Čistá mzda z původního a nového systému (kontrolní sestava pro duplicitu) (SK)

Čistá mzda z původního a nového systému (kontrolní sestava pro duplicitu) (SK) je obdobou sestavy:

• Čistá mzda z původního a nového systému (kontrolní sestava pro duplicitu),

která je používána pro CZ legislativu. Navíc je zde uvedena položka zdrav_zakl.

CREATE

create table aa_mzdy (		
os_cislo decimal (10,0),
k_vyplate decimal (10,2),
soc_zakl decimal (10,2),
zdrav_zakl decimal (10,2)
mesic smallint,
rok smallint
);	

Sestava

select 
distinct X.os_cislo as os_cislo_puv,
A.os_cislo as os_cislo_ks,
A.prijmeni,
A.jmeno,
A.kmen_str,
(select skup_str from stred where str = A.kmen_str) as skup_str,

(select sum(k_vyplate) from aa_mzdy X1 where X1.os_cislo = X.os_cislo and
year(%DATUMOD%) = X1.rok and
month (%DATUMOD%) = X1.mesic
) as k_vypl_puv,

(select sum(castka) from pichacky P1 where P1.os_cislo = A.os_cislo and kod in (940, 941, 942) and P1.datum = %DATUMOD%) as k_940_1_2_ks,

((select sum(k_vyplate) from aa_mzdy X1 where X1.os_cislo = X.os_cislo and
year(%DATUMOD%) = X1.rok and
month (%DATUMOD%) = X1.mesic
)
-
(select sum(castka) from pichacky P1 where P1.os_cislo = A.os_cislo and kod in (941, 942) and P1.datum = %DATUMOD%)) as rozdil_1,

(select sum(soc_zakl) from aa_mzdy X2 where X2.os_cislo = X.os_cislo and
year(%DATUMOD%) = X2.rok and
month (%DATUMOD%) = X2.mesic
) as soc_zakl_puv,

(select sum(soc_zakl) from telegram T1 where T1.os_cislo = A.os_cislo and T1.datum = %DATUMOD%) as soc_zakl_ks,

((select sum(soc_zakl) from aa_mzdy X2 where X2.os_cislo = X.os_cislo and
year(%DATUMOD%) = X2.rok and
month (%DATUMOD%) = X2.mesic
)
-
(select sum(soc_zakl) from telegram T1 where T1.os_cislo = A.os_cislo and T1.datum = %DATUMOD%)) as rozdil_2,

(select sum(zdrav_zakl) from aa_mzdy X2 where X2.os_cislo = X.os_cislo and
year(%DATUMOD%) = X2.rok and
month (%DATUMOD%) = X2.mesic
) as zdrav_zakl_puv,

(select sum(zdrav_zakl) from telegram T1 where T1.os_cislo = A.os_cislo and T1.datum = %DATUMOD%) as zdrav_zakl_ks, 

((select sum(zdrav_zakl) from aa_mzdy X2 where X2.os_cislo = X.os_cislo and
year(%DATUMOD%) = X2.rok and
month (%DATUMOD%) = X2.mesic
)
-
(select sum(zdrav_zakl) from telegram T1 where T1.os_cislo = A.os_cislo and T1.datum = %DATUMOD%)) as rozdil_3

from pracovnici A full join aa_mzdy X on A.os_cislo = X.os_cislo

Odkazy

• Čistá mzda z původního a nového systému (kontrolní sestava pro duplicitu)