Subtract columns from different tables

0

Companions, I have this because my logic is not giving me.

This my query:

(select count(distinct (case when  sno_personalnomina.codnom::int in (1,2,3,5,6,7,8) then sno_personalnomina.codper end)) as sin_renumeracion from sno_personalnomina  inner join sno_nomina on sno_nomina.codnom=sno_personalnomina.codnom WHERE sno_personalnomina.codnom::int in (1,2,3,5,6,7,8) and sno_personalnomina.staper::int=1   GROUP BY sno_personalnomina.codnom, sno_nomina.desnom)

I want to subtract it with this query

select count(distinct (case when sno_hpersonalnomina.codban = '001' then sno_hsalida.codper end)) as trabajadores_tes FROM sno_hpersonalnomina 
left join scb_banco on sno_hpersonalnomina.codban = scb_banco.codban 
inner JOIN sno_hsalida ON sno_hpersonalnomina.codnom = sno_hsalida.codnom AND sno_hpersonalnomina.codper = sno_hsalida.codper AND 
sno_hpersonalnomina.codperi= sno_hsalida.codperi 
inner join sno_nomina on sno_nomina.codnom=sno_hsalida.codnom
WHERE sno_hpersonalnomina.codperi::int=1 AND sno_hpersonalnomina.codnom::integer in (1,2,3,5,6,7,8) and sno_hpersonalnomina.staper::int=1
group by sno_hpersonalnomina.codnom

How do I do it? I have tried to use a conditional sum with case but it does not give me the results. Thanks in advance

    
asked by Rafael Aguilar 22.03.2018 в 16:38
source

1 answer

0

The results of the queries have a single column. A second column or field is needed to compare two results. I mean, each query can work as a table, the first query would be table1 and the second query table2. Let's say that the result you are currently using is field2 of each table and you still have to define field1. You can do the query like this:

select tabla1.campo1, tabla1.campo2,tabla2.campo2,tabla1.campo2-tabla2.campo2
from tabla1,tabla2
where tabla1.campo1=tabla2.campo1

More specifically using your queries

select tabla1.campo1, tabla1.campo2,tabla2.campo2,tabla1.campo2-tabla2.campo2
    from (select count(distinct (case when  sno_personalnomina.codnom::int in (1,2,3,5,6,7,8) then sno_personalnomina.codper end)) as sin_renumeracion from sno_personalnomina  inner join sno_nomina on sno_nomina.codnom=sno_personalnomina.codnom WHERE sno_personalnomina.codnom::int in (1,2,3,5,6,7,8) and sno_personalnomina.staper::int=1   GROUP BY sno_personalnomina.codnom, sno_nomina.desnom) as tabla1,
    (select count(distinct (case when sno_hpersonalnomina.codban = '001' then sno_hsalida.codper end)) as trabajadores_tes FROM sno_hpersonalnomina 
left join scb_banco on sno_hpersonalnomina.codban = scb_banco.codban 
inner JOIN sno_hsalida ON sno_hpersonalnomina.codnom = sno_hsalida.codnom AND sno_hpersonalnomina.codper = sno_hsalida.codper AND 
sno_hpersonalnomina.codperi= sno_hsalida.codperi 
inner join sno_nomina on sno_nomina.codnom=sno_hsalida.codnom
WHERE sno_hpersonalnomina.codperi::int=1 AND sno_hpersonalnomina.codnom::integer in (1,2,3,5,6,7,8) and sno_hpersonalnomina.staper::int=1
group by sno_hpersonalnomina.codnom)as tabla2
    where tabla1.campo1=tabla2.campo1

By the way, your queries remind me of SIGESP, the administration system used in several public entities in my country.

    
answered by 27.03.2018 в 23:34