Pom14
Z Prirucky
Účelová sestava - přepočtení zaměstnanci
SQL
select PR.os_cislo, PR.prijmeni, PP.cislo_pom, PP.druh_pom_j, PP.prac_str, PP.datum_nastupu, PP.datum_ukonc, case when (select sum(kod) from absence where PP.os_cislo = absence.os_cislo and PP.cislo_pom = absence.cislo_pom and kod in (select kod from ciselnik where ind_abs = 3) and dat_od <=%DATUMOD% and (dat_do is null or dat_do >= %DATUMDO%) ) is null then null else 'MIMOEV' end as mimoev, V.id_uc_dim1, (select nazev from uc_dim DIM where DIM.typ_dimenze = 1 and DIM.id = V.id_uc_dim1) as nazev1, V.id_uc_dim2, (select nazev from uc_dim DIM where DIM.typ_dimenze = 2 and DIM.id = V.id_uc_dim2) as nazev2, V.id_uc_dim3, (select nazev from uc_dim DIM where DIM.typ_dimenze = 3 and DIM.id = V.id_uc_dim3) as nazev3, V.misto, V.datum, V.davka, V.poznamka, null as hod_dohody, null as hod_fondstand_doh, isnull(V.procento/100, 0) as podil_proj, isnull(PP.smluv_pom_uv,0) as podil_smluv, isnull(V.procento/100*PP.smluv_pom_uv, 0) as proj_krat_smluv from pracovnici PR, prac_pom PP left outer join vyrobak V on PP.os_cislo = V.os_cislo and PP.cislo_pom = V.cislo_pom where PR.os_cislo = PP.os_cislo and PP.druh_pom in (0,1,2,3) UNION ALL select PR.os_cislo, PR.prijmeni, PP.cislo_pom, PP.druh_pom_j, PP.prac_str, PP.datum_nastupu, PP.datum_ukonc, case when (select sum(kod) from absence where PP.os_cislo = absence.os_cislo and PP.cislo_pom = absence.cislo_pom and kod in (select kod from ciselnik where ind_abs = 3) and dat_od <=%DATUMOD% and (dat_do is null or dat_do >= %DATUMDO%) ) is null then null else 'MIMOEV' end as mimoev, V.id_uc_dim1, (select nazev from uc_dim DIM where DIM.typ_dimenze = 1 and DIM.id = V.id_uc_dim1) as nazev1, V.id_uc_dim2, (select nazev from uc_dim DIM where DIM.typ_dimenze = 2 and DIM.id = V.id_uc_dim2) as nazev2, V.id_uc_dim3, (select nazev from uc_dim DIM where DIM.typ_dimenze = 3 and DIM.id = V.id_uc_dim3) as nazev3, V.misto, V.datum, V.davka, V.poznamka, (select sum(hodiny) from pichacky PICH where PICH.os_cislo = PP.os_cislo and PICH.cislo_pom = PP.cislo_pom and PICH.kod in (630,634) and PICH.datum >= %DATUMOD% and PICH.datum <= %DATUMDO%) as hod_dohody, (select sum(dny_prac)*8 from mesice where rok>=year(%DATUMOD%) and mesic >=month(%DATUMOD%) and rok <=year(%DATUMDO%) and mesic<=month(%DATUMDO%)) as hod_fondstand_doh, isnull(V.procento/100, 0) as podil_proj, (select sum(hodiny) from pichacky PICH where PICH.os_cislo = PP.os_cislo and PICH.cislo_pom = PP.cislo_pom and PICH.kod in (630,634) and PICH.datum >= %DATUMOD% and PICH.datum <= %DATUMDO%) / (select sum(dny_prac)*8 from mesice where rok>=year(%DATUMOD%) and mesic >=month(%DATUMOD%) and rok <=year(%DATUMDO%) and mesic<=month(%DATUMDO%)) as podil_smluv, isnull ( V.procento/100 * (select sum(hodiny) from pichacky PICH where PICH.os_cislo = PP.os_cislo and PICH.cislo_pom = PP.cislo_pom and PICH.kod in (630,634) and PICH.datum >= %DATUMOD% and PICH.datum <= %DATUMDO%) / (select sum(dny_prac)*8 from mesice where rok>=year(%DATUMOD%) and mesic >=month(%DATUMOD%) and rok <=year(%DATUMDO%) and mesic<=month(%DATUMDO%)) , 0) as proj_krat_smluv from pracovnici PR, prac_pom PP left outer join vyrobak V on PP.os_cislo = V.os_cislo and PP.cislo_pom = V.cislo_pom where PR.os_cislo = PP.os_cislo and PP.druh_pom in (4,5,6,7)