Pom15

Create

create table aa_mzdy2 (

os_cislo decimal (10,0),
cislo_pom smallint,
prac_str char(10),
kod_puv smallint,
kod smallint,
dat_od	date,
dat_do	date,
dny decimal (3,1),
hodiny decimal (5,2),
sazba decimal (16,2),
castka	decimal (16,2),
prizn_aut char(1),
prac_kateg smallint,
datum date,
id_podniku integer,
poznamka char(30));

Pro vybraný kód

select distinct X.os_cislo as os_cislo_puv,
A.os_cislo as os_cislo_ks,
A.prijmeni,
A.jmeno,
A.kmen_str,

(select sum(dny) from aa_mzdy2 X1 where X1.os_cislo = X.os_cislo and X1.kod = %KOD% and X1.datum = %DATUMOD%) as dny_puv,
(select sum(dny) from pichacky P where P.os_cislo = A.os_cislo and P.kod = %KOD% and P.datum = %DATUMOD%) as dny_ks,
(
(select sum(dny) from aa_mzdy2 X1 where X1.os_cislo = X.os_cislo and X1.kod = %KOD% and X1.datum = %DATUMOD%) -
(select sum(dny) from pichacky P where P.os_cislo = A.os_cislo and P.kod = %KOD% and P.datum = %DATUMOD%)
) as rozdil,


(select sum(hodiny) from aa_mzdy2 X1 where X1.os_cislo = X.os_cislo and X1.kod = %KOD% and X1.datum = %DATUMOD%) as hod_puv,
(select sum(hodiny) from pichacky P where P.os_cislo = A.os_cislo and P.kod = %KOD% and P.datum = %DATUMOD%) as hod_ks,
(
(select sum(hodiny) from aa_mzdy2 X1 where X1.os_cislo = X.os_cislo and X1.kod = %KOD% and X1.datum = %DATUMOD%) -
(select sum(hodiny) from pichacky P where P.os_cislo = A.os_cislo and P.kod = %KOD% and P.datum = %DATUMOD%)
) as rozdil,


(select sum(castka) from aa_mzdy2 X1 where X1.os_cislo = X.os_cislo and X1.kod = %KOD% and X1.datum = %DATUMOD%) as castka_puv,
(select sum(castka) from pichacky P where P.os_cislo = A.os_cislo and P.kod = %KOD% and P.datum = %DATUMOD%) as castka_ks,
(
(select sum(castka) from aa_mzdy2 X1 where X1.os_cislo = X.os_cislo and X1.kod = %KOD% and X1.datum = %DATUMOD%) -
(select sum(castka) from pichacky P where P.os_cislo = A.os_cislo and P.kod = %KOD% and P.datum = %DATUMOD%)
) as rozdil

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

Pro všechny kódy

Výchozí zdroj pro kód a hodnoty je původní tabulka

select distinct X.os_cislo as os_cislo_puv,
A.os_cislo as os_cislo_ks,
A.prijmeni,
A.jmeno,
A.kmen_str,
X.kod,

(select sum(dny) from aa_mzdy2 X1 where X1.os_cislo = X.os_cislo and X1.kod = X.kod and X1.datum = %DATUMOD%) as dny_puv,
(select sum(dny) from pichacky P where P.os_cislo = A.os_cislo and P.kod = X.kod and P.datum = %DATUMOD%) as dny_ks,
(
(select sum(dny) from aa_mzdy2 X1 where X1.os_cislo = X.os_cislo and X1.kod = X.kod and X1.datum = %DATUMOD%) -
(select sum(dny) from pichacky P where P.os_cislo = A.os_cislo and P.kod = X.kod and P.datum = %DATUMOD%)
) as rozdil,


(select sum(hodiny) from aa_mzdy2 X1 where X1.os_cislo = X.os_cislo and X1.kod = X.kod and X1.datum = %DATUMOD%) as hod_puv,
(select sum(hodiny) from pichacky P where P.os_cislo = A.os_cislo and P.kod = X.kod and P.datum = %DATUMOD%) as hod_ks,
(
(select sum(hodiny) from aa_mzdy2 X1 where X1.os_cislo = X.os_cislo and X1.kod = X.kod and X1.datum = %DATUMOD%) -
(select sum(hodiny) from pichacky P where P.os_cislo = A.os_cislo and P.kod = X.kod and P.datum = %DATUMOD%)
) as rozdil,


(select sum(castka) from aa_mzdy2 X1 where X1.os_cislo = X.os_cislo and X1.kod = X.kod and X1.datum = %DATUMOD%) as castka_puv,
(select sum(castka) from pichacky P where P.os_cislo = A.os_cislo and P.kod = X.kod and P.datum = %DATUMOD%) as castka_ks,
(
(select sum(castka) from aa_mzdy2 X1 where X1.os_cislo = X.os_cislo and X1.kod = X.kod and X1.datum = %DATUMOD%) -
(select sum(castka) from pichacky P where P.os_cislo = A.os_cislo and P.kod = X.kod and P.datum = %DATUMOD%)
) as rozdil

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

Varianta 3

select A.os_cislo, A.cislo_pom, PR.prijmeni, PR.jmeno, A.kod, sum(A.castka) as Kc_ks, sum(B.castka) as Kc_puv, 
(sum(B.castka) - sum(A.castka)) as rozdil
from pichacky A, aa_mzdy2 B, pracovnici PR
where A.os_cislo = B.os_cislo and A.cislo_pom = B.cislo_pom and A.kod = B.kod and
A.os_cislo = PR.os_cislo and A.datum = %DATUMOD% and B.datum = %DATUMOD%
group by A.os_cislo, A.cislo_pom, PR.prijmeni, PR.jmeno, A.kod

Odkazy

• Komplexní zkoušky mezd