|
|
| Řádek 1: |
Řádek 1: |
| == 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]]
| |
