|
|
| Řádek 1: |
Řádek 1: |
| ''Účelová sestava - přepočtení zaměstnanci (Definice - verze1)''
| |
| První používaná verze sestavy [[Účelová sestava - přepočtení zaměstnanci]].
| |
|
| |
|
| <small>
| |
| select PR.os_cislo, PR.prijmeni, PR.jmeno, 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, PR.jmeno, 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)
| |
|
| |
| == Odkazy ==
| |
| * [[Účelová sestava - přepočtení zaměstnanci]]
| |
