Peníze, zůstatky dovolené a čerpané dny a hodiny aktuálního roku (sestava)

Peníze, zůstatky dovolené a čerpané dny a hodiny aktuálního roku (sestava) - sestava pro Generátor sestav II. Určeno pro Oracle!

select A.os_cislo, A.prijmeni, A.jmeno,
B.cislo_pom,
B.druh_pom_j,
B.prac_kateg,
B.datum_nastupu,
B.datum_ukonc,
B.prac_str,
B.tarif as tarif_akt, 

(select sum(P.hodiny) from pichacky P where P.os_cislo = B.os_cislo and P.cislo_pom = B.cislo_pom 
and datum >= %DATUMOD% and datum <= %DATUMDO% and kod in (210,211,212,213,214,215)) as hod_210_215
,
(select avg(P.sazba) from pichacky P where P.os_cislo = B.os_cislo and P.cislo_pom = B.cislo_pom 
and datum >= %DATUMOD% and datum <= %DATUMDO% and kod = 240) as saz_240
,

(select avg(P.sazba) from pichacky P where P.os_cislo = B.os_cislo and P.cislo_pom = B.cislo_pom 
and datum >= %DATUMOD% and datum <= %DATUMDO% and kod = 241) as saz_241
,
(select avg(P.sazba) from pichacky P where P.os_cislo = B.os_cislo and P.cislo_pom = B.cislo_pom 
and datum >= %DATUMOD% and datum <= %DATUMDO% and kod = 242) as saz_242
,

(select sum(P.castka) from pichacky P where P.os_cislo = B.os_cislo and P.cislo_pom = B.cislo_pom 
and datum >= %DATUMOD% and datum <= %DATUMDO% and kod = 301) as Kc_301
,
(select sum(P.castka) from pichacky P where P.os_cislo = B.os_cislo and P.cislo_pom = B.cislo_pom 
and datum >= %DATUMOD% and datum <= %DATUMDO% and kod = 302) as Kc_302
,
(select sum(P.hodiny) from pichacky P where P.os_cislo = B.os_cislo and P.cislo_pom = B.cislo_pom 
and datum >= %DATUMOD% and datum <= %DATUMDO% and kod = 234) as hod_234
,
(select sum(P.castka) from pichacky P where P.os_cislo = B.os_cislo and P.cislo_pom = B.cislo_pom 
and datum >= %DATUMOD% and datum <= %DATUMDO% and kod = 234) as Kc_234
,
(select sum(P.castka) from pichacky P where P.os_cislo = B.os_cislo and P.cislo_pom = B.cislo_pom 
and datum >= %DATUMOD% and datum <= %DATUMDO% and kod>100 and kod<700) as Kc_hr_mz
,
(select sum(P.dny) from pichacky P where P.os_cislo = B.os_cislo and P.cislo_pom = B.cislo_pom 
and datum >= %DATUMOD% and datum <= %DATUMDO% and kod = 589) as dny_589
,
(select sum(P.dny) from pichacky P where P.os_cislo = B.os_cislo and P.cislo_pom = B.cislo_pom 
and substr(datum, 1, 4) = substr(%DATUMDO%,1,4) and kod = 589) as dny_589_rok
,
(select sum(P.hodiny) from pichacky P where P.os_cislo = B.os_cislo and P.cislo_pom = B.cislo_pom 
and substr(datum, 1, 4) = substr(%DATUMDO%,1,4) and kod in (211,212,213,214,215)) as hod_211_5_rok
,
(select zustatek from dovolena D where D.os_cislo = B.os_cislo and D.cislo_pom = B.cislo_pom 
and substr (D.datum,1,7) = substr (%DATUMDO%,1,7)) as zust_dov

from pracovnici A, prac_pom B
where A.os_cislo = B.os_cislo

Odkazy

• Generátor sestav II
• Hd 36994