Zobrazení zdroje stránky 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'''.

[[Image: Prehled-dovolene-a-benefitniho-volna-sestava.png]]

== SQL script ==
<small>
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
</small>

== Odkazy ==
* [[Dovolená]]
* [[Benefitní volna]]