sql – Passing values ​​from a select to a subselect

Question:

I have the following query:

 SELECT rp.colaborador as codigo_, t.nome, count(rp.*) as presencas, 
    ((100 * (select count(rp.*) from empresa.cad_reuniao_presencas rp 
    join empresa.cad_terceiros t on t.codigo = rp.colaborador
    join empresa.cad_reunioes r on r.codigo = rp.reuniao
    where r.data_reuniao > t.data_cadastro and t.codigo = ?
    group by rp.colaborador, t.codigo )) / (select max(codigo) from empresa.cad_reunioes rp) || '%') as porcentagem 
    FROM empresa.cad_reuniao_presencas rp 
    join empresa.cad_terceiros t on t.codigo = rp.colaborador
    join empresa.cad_reunioes r on r.codigo = rp.reuniao
    where r.data_reuniao > t.data_cadastro
    group by t.nome, rp.colaborador order by presencas desc;

Where is the ? (question mark) I would like to pass the Third Party code to correctly get the percentage of attendance at meetings, how could I do that? If I put a fixed value it works, but then in all lines it only appears from a Third Party.

Answer:

Giancarlo, you can use temporary tables or join with select, follow examples below.

Using temporary table.

CREATE TABLE #TABLE_PORCENTAGEM (
QUANTIDADE BIGINT,
CODIGOT NVARCHAR(MAX)
)



INSERT INTO #TABLE_PORCENTAGEM (QUANTIDADE, CODIGOT)
select 
    count(rp.*), 
    t.codigo 
from 
    empresa.cad_reuniao_presencas rp 
    join empresa.cad_terceiros t on t.codigo = rp.colaborador
    join empresa.cad_reunioes r on r.codigo = rp.reuniao
where
     r.data_reuniao > t.data_cadastro
group by 
    rp.colaborador, 
    t.codigo 


 SELECT
     rp.colaborador as codigo_, 
     t.nome, 
     count(rp.*) as presencas, 
     ((100 * ISNULL(tp.QUANTIDADE,0)) / (select max(codigo) from empresa.cad_reunioes rp) || '%') as porcentagem 
FROM 
    empresa.cad_reuniao_presencas rp 
    join empresa.cad_terceiros t on t.codigo = rp.colaborador
    join empresa.cad_reunioes r on r.codigo = rp.reuniao
    left join #TABLE_PORCENTAGEM tp on tp.CODIGOT = t.codigo
where 
    r.data_reuniao > t.data_cadastro
group by 
    t.nome, 
    rp.colaborador 
order by 
    presencas desc;

DROP TABLE #TABLE_PORCENTAGEM

Using join with select

 SELECT
     rp.colaborador as codigo_, 
     t.nome, 
     count(rp.*) as presencas, 
     ((100 * ISNULL(tp.QUANTIDADE,0)) / (select max(codigo) from empresa.cad_reunioes rp) || '%') as porcentagem 
FROM 
    empresa.cad_reuniao_presencas rp 
    join empresa.cad_terceiros t on t.codigo = rp.colaborador
    join empresa.cad_reunioes r on r.codigo = rp.reuniao
    left join (
                select 
                count(rp.*) as QUANTIDADE, 
                t.codigo as CODIGOT 
                from 
                    empresa.cad_reuniao_presencas rp 
                    join empresa.cad_terceiros t on t.codigo = rp.colaborador
                    join empresa.cad_reunioes r on r.codigo = rp.reuniao
                where
                     r.data_reuniao > t.data_cadastro
                group by 
                    rp.colaborador, 
             ) tp on tp.CODIGOT = t.codigo
where 
    r.data_reuniao > t.data_cadastro
group by 
    t.nome, 
    rp.colaborador 
order by 
    presencas desc;
Scroll to Top