Pom14: Porovnání verzí
Z Prirucky
(Stránka vyprázdněna) |
|||
Řádek 1: | Řádek 1: | ||
''Účelová sestava - přepočtení zaměstnanci'' | |||
[[Image: Ucelova sestava-Prepocteni zamestnanci.png|1500px]] | |||
== SQL == | |||
<small> | |||
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) | |||
</small> | |||
== Odkazy == | |||
* [[Účetnictví cc]] |
Verze z 12. 12. 2019, 15:18
Úč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)