Sestavy (Rozpad na projekty dle odpracované doby)
Z Prirucky
Verze z 26. 8. 2016, 10:13, kterou vytvořil Petrm (diskuse | příspěvky) (→Činnosti na projektech (vč. neschválených))
Sestavy (Rozpad na projekty dle odpracované doby)
Sestavy jsou určeny jako doplněk funkcionality
převážně pro Generátor sestav II, kam je také potřeba zadat příslušnou definici (uváděnou níže u každé sestavy) - Generátor sestav II (nová definice SQL).
Odpracovaná doba na projektech (vč. neschválené)
select projekty_hod.id, projekty_hod.id_proj, datum, pracovnici.os_cislo, prijmeni, jmeno, titul, prac_pom.cislo_pom, prac_pom.prac_str, cis_projekty.cislo, cis_projekty.nazev, id_poz, schvaleno, hod, poz from pracovnici,prac_pom, projekty_hod, cis_projekty where pracovnici.os_cislo = prac_pom.os_cislo and prac_pom.os_cislo = projekty_hod.os_cislo and prac_pom.cislo_pom = projekty_hod.cislo_pom and projekty_hod.id_proj = cis_projekty.id and datum >= %DATUMOD% and datum <= %DATUMDO%
Činnosti na projektech (vč. neschválených)
select projekty_cinn.id, projekty_hod.id_proj,projekty_hod.datum, pracovnici.os_cislo, prijmeni, jmeno, titul, prac_pom.cislo_pom, prac_pom.prac_str, cis_projekty.cislo as cis_proj, cis_projekty.nazev as naz_proj, id_poz, schvaleno, projekty_cinn.nazev as nazev_cinn, projekty_cinn.hod as hod_cinn, projekty_cinn.popis as popis_cinn from pracovnici, prac_pom, projekty_cinn, projekty_hod, cis_projekty where pracovnici.os_cislo = prac_pom.os_cislo and prac_pom.os_cislo = projekty_hod.os_cislo and prac_pom.cislo_pom = projekty_hod.cislo_pom and projekty_hod.id_proj = cis_projekty.id and projekty_hod.id = projekty_cinn.id_proj_hod and datum >= %DATUMOD% and datum <= %DATUMDO%
Vypočítané peníze po projektech
select pracovnici.os_cislo, prac_pom.cislo_pom, prijmeni, jmeno, pichacky.kod, (select vyznam from ciselnik where kod = pichacky.kod) as vyznam, pichacky.prac_str, dat_od, dat_do, dny, hodiny, sazba, castka, prizn_aut, pichacky.prac_kateg, datum, id_vazby, typ_vazby, (select cislo from cis_projekty where id = pichacky.id_vazby and typ_vazby = 'ph') as cis_projektu, (select nazev from cis_projekty where id = pichacky.id_vazby and typ_vazby = 'ph') as naz_projektu from pracovnici, prac_pom, pichacky where pracovnici.os_cislo = prac_pom.os_cislo and pracovnici.os_cislo = pichacky.os_cislo and prac_pom.cislo_pom = pichacky.cislo_pom and datum >= %DATUMOD% and datum <= %DATUMDO% order by datum, os_cislo, cislo_pom, kod
Částky po měsících a po kódech a projektech
- Pro MS SQL
select kod, id_vazby, cislo, nazev, plati_od, plati_do, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-01-01') as Kc_01_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-02-01') as Kc_02_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-03-01') as Kc_03_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-04-01') as Kc_04_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-05-01') as Kc_05_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-06-01') as Kc_06_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-07-01') as Kc_07_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-08-01') as Kc_08_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-09-01') as Kc_09_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-10-01') as Kc_10_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-11-01') as Kc_11_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-12-01') as Kc_12_2015 from pichacky A left outer join cis_projekty on A.id_vazby = cis_projekty.id where id_vazby is not null group by kod, id_vazby, cislo, nazev, plati_od, plati_do union select kod, id_vazby, cislo, nazev, plati_od, plati_do, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-01-01') as Kc_01_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-02-01') as Kc_02_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-03-01') as Kc_03_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-04-01') as Kc_04_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-05-01') as Kc_05_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-06-01') as Kc_06_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-07-01') as Kc_07_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-08-01') as Kc_08_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-09-01') as Kc_09_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-10-01') as Kc_10_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-11-01') as Kc_11_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-12-01') as Kc_12_2015 from pichacky A left outer join cis_projekty on A.id_vazby = cis_projekty.id where id_vazby is null group by kod, id_vazby, cislo, nazev, plati_od, plati_do
Včetně názvu mzdové položky
select A.kod, vyznam, id_vazby, cislo, nazev, plati_od, plati_do, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-01-01') as Kc_01_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-02-01') as Kc_02_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-03-01') as Kc_03_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-04-01') as Kc_04_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-05-01') as Kc_05_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-06-01') as Kc_06_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-07-01') as Kc_07_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-08-01') as Kc_08_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-09-01') as Kc_09_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-10-01') as Kc_10_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-11-01') as Kc_11_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '2015-12-01') as Kc_12_2015 from pichacky A left outer join cis_projekty on A.id_vazby = cis_projekty.id, ciselnik where id_vazby is not null and ciselnik.kod = A.kod group by A.kod, vyznam, id_vazby, cislo, nazev, plati_od, plati_do union select A.kod, vyznam, id_vazby, cislo, nazev, plati_od, plati_do, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-01-01') as Kc_01_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-02-01') as Kc_02_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-03-01') as Kc_03_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-04-01') as Kc_04_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-05-01') as Kc_05_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-06-01') as Kc_06_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-07-01') as Kc_07_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-08-01') as Kc_08_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-09-01') as Kc_09_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-10-01') as Kc_10_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-11-01') as Kc_11_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '2015-12-01') as Kc_12_2015 from pichacky A left outer join cis_projekty on A.id_vazby = cis_projekty.id, ciselnik where id_vazby is null and ciselnik.kod = A.kod group by A.kod, vyznam, id_vazby, cislo, nazev, plati_od, plati_do
- Pro Informix
select kod, id_vazby, cislo, nazev, plati_od, plati_do, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '01.01.2015') as Kc_01_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '01.02.2015') as Kc_02_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '01.03.2015') as Kc_03_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '01.04.2015') as Kc_04_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '01.05.2015') as Kc_05_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '01.06.2015') as Kc_06_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '01.07.2015') as Kc_07_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '01.08.2015') as Kc_08_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '01.09.2015') as Kc_09_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '01.10.2015') as Kc_10_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '01.11.2015') as Kc_11_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby = A.id_vazby and B.datum = '01.12.2015') as Kc_12_2015 from pichacky A left outer join cis_projekty on A.id_vazby = cis_projekty.id where id_vazby is not null group by kod, id_vazby, cislo, nazev, plati_od, plati_do union select kod, id_vazby, cislo, nazev, plati_od, plati_do, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '01.01.2015') as Kc_01_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '01.02.2015') as Kc_02_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '01.03.2015') as Kc_03_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '01.04.2015') as Kc_04_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '01.05.2015') as Kc_05_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '01.06.2015') as Kc_06_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '01.07.2015') as Kc_07_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '01.08.2015') as Kc_08_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '01.09.2015') as Kc_09_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '01.10.2015') as Kc_10_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '01.11.2015') as Kc_11_2015, (select sum(castka) from pichacky B where B.kod = A.kod and B.id_vazby is null and B.datum = '01.12.2015') as Kc_12_2015 from pichacky A left outer join cis_projekty on A.id_vazby = cis_projekty.id where id_vazby is null group by kod, id_vazby, cislo, nazev, plati_od, plati_do
