Přehled dovolené a benefitního volna (sestava)
Přehled dovolené a benefitního volna je sestava určená pro Generátor sestav II pro db platformu MS SQL.
SQL script
select A.os_cislo, A.prijmeni, A.jmeno, B.cislo_pom, B.druh_pom, B.prac_str, D.narok_letos as D_narok, D.cerpana as D_cerpana, D.cerpana_akt as D_cerp_akt, D.zustatek as D_zustatek, BN.narok as BN_narok, BN.cerpano as BN_cerpano, BN.cerp_akt as BN_cerp_akt, BN.zustatek as BN_zustatek, B.datum as datum
from pracovnici A, pom_zal B left outer join dovolena D on (B.os_cislo = D.os_cislo and B.cislo_pom = D.cislo_pom and D.datum = B.datum), benefit_volno BN right outer join pom_zal B2 on (BN.os_cislo = B2.os_cislo and BN.cislo_pom = B2.cislo_pom and BN.datum = B2.datum)
where A.os_cislo = B.os_cislo and B.os_cislo = B2.os_cislo and B.cislo_pom = B2.cislo_pom and B.datum = B2.datum and B.datum >=%DATUMOD% and B.datum<=%DATUMDO%
union
select A.os_cislo, A.prijmeni, A.jmeno, B.cislo_pom, B.druh_pom, B.prac_str, D.narok_letos as D_narok, D.cerpana as D_cerpana, D.cerpana_akt as D_cerp_akt, D.zustatek as D_zustatek, BN.narok as BN_narok, BN.cerpano as BN_cerpano, BN.cerp_akt as BN_cerp_akt, BN.zustatek as BN_zustatek, (select datum_prvni from par_map where id_podniku = A.id_podniku) as datum
from pracovnici A, prac_pom B left outer join dovolena D on (B.os_cislo = D.os_cislo and B.cislo_pom = D.cislo_pom and year(D.datum) =year(%DATUMDO%) and month(D.datum) = month(%DATUMDO%)), benefit_volno BN right outer join prac_pom B2 on (BN.os_cislo = B2.os_cislo and BN.cislo_pom = B2.cislo_pom and year(BN.datum) =year(%DATUMDO%) and month(BN.datum) = month(%DATUMDO%))
where A.os_cislo = B.os_cislo and B.os_cislo = B2.os_cislo and B.cislo_pom = B2.cislo_pom and %DATUMDO% >= (select datum_prvni from par_map where id_podniku = A.id_podniku)
order by A.prijmeni, datum
