Zobrazení zdroje stránky Zkrácená mzdová statistika s účty MD - DAL

Zkrácená mzdová statistika s účty MD - DAL je sestava pro [[Generátor sestav II]].

== Omezení ==
* není ošetřeno pro [[víceIČ]]
* počítá se jen s číselníkem kódy účtování, nikoliv rozšířené kódy účtování ani kódy účtování pro zdravotní pojišťovny.
== Sestava ==
[[Image: Zkracena mzdova statistika s ucty MD DAL.png|450px]]

== SQL definice ==
<small>
 select distinct P.kod, C.vyznam,
 (select count(*) from pichacky P2 where P2.kod = P.kod and P2.datum >= %DATUMOD% and P2.datum <= %DATUMDO%) as pocet,
 (select sum(dny) from pichacky P2 where P2.kod = P.kod and P2.datum >= %DATUMOD% and P2.datum <= %DATUMDO%) as dny,
 (select sum(hodiny) from pichacky P2 where P2.kod = P.kod and P2.datum >= %DATUMOD% and P2.datum <= %DATUMDO%) as hodiny,
 (select ceiling(sum(castka)) from pichacky P2 where P2.kod = P.kod and P2.datum >= %DATUMOD% and P2.datum <= %DATUMDO%) as castka,
 C.kod_uc,
 (select ucet_md from kody_uc KUC where C.kod_uc = KUC.kod_uc) as ucet_MD,
 (select ucet_dal from kody_uc KUC where C.kod_uc = KUC.kod_uc) as ucet_DAL,
 (select nazev from kody_uc KUC where C.kod_uc = KUC.kod_uc) as nazev
 
 from pichacky P, ciselnik C
 where P.kod = C.kod and P.datum >= %DATUMOD% and P.datum <= %DATUMDO%
 order by P.kod
</small>

=== Po střediscích ===
<small>
 select P.kod, P.prac_str, C.vyznam,
 (select count(*) from pichacky P2 where P2.kod = P.kod and P2.prac_str = P.prac_str and P2.datum >= %DATUMOD% and P2.datum <= %DATUMDO%) as pocet,
 (select sum(dny) from pichacky P2 where P2.kod = P.kod and P2.prac_str = P.prac_str and P2.datum >= %DATUMOD% and P2.datum <= %DATUMDO%) as dny,
 (select sum(hodiny) from pichacky P2 where P2.kod = P.kod and P2.prac_str = P.prac_str and P2.datum >= %DATUMOD% and P2.datum <= %DATUMDO%) as hodiny,
 (select ceiling(sum(castka)) from pichacky P2 where P2.kod = P.kod and P2.prac_str = P.prac_str and P2.datum >= %DATUMOD% and P2.datum <= %DATUMDO%) as castka,
 C.kod_uc,
 (select ucet_md from kody_uc KUC where C.kod_uc = KUC.kod_uc) as ucet_MD,
 (select ucet_dal from kody_uc KUC where C.kod_uc = KUC.kod_uc) as ucet_DAL,
 (select nazev from kody_uc KUC where C.kod_uc = KUC.kod_uc) as nazev
 
 from pichacky P, ciselnik C
 where P.kod = C.kod and P.datum >= %DATUMOD% and P.datum <= %DATUMDO%
 group by P.kod, P.prac_str, C.vyznam, C.kod_uc
 order by P.kod, P.prac_str
</small>

=== Pro MD a DAL uváděné v názvu ===
<small>
 select distinct P.kod, C.vyznam,
 (select count(*) from pichacky P2 where P2.kod = P.kod and P2.datum >= %DATUMOD% and P2.datum <= %DATUMDO%) as pocet,
 (select sum(dny) from pichacky P2 where P2.kod = P.kod and P2.datum >= %DATUMOD% and P2.datum <= %DATUMDO%) as dny,
 (select sum(hodiny) from pichacky P2 where P2.kod = P.kod and P2.datum >= %DATUMOD% and P2.datum <= %DATUMDO%) as hodiny,
 (select ceiling(sum(castka)) from pichacky P2 where P2.kod = P.kod and P2.datum >= %DATUMOD% and P2.datum <= %DATUMDO%) as castka,
 C.kod_uc,
 
 (select substring(nazev, 1, 8) from kody_uc KUC where C.kod_uc = KUC.kod_uc) as ucet_MD2,
 (select substring(nazev, 10, 8) from kody_uc KUC where C.kod_uc = KUC.kod_uc) as ucet_DAL2,
 (select substring(nazev, 19, len(rtrim(ltrim(nazev)))) from kody_uc KUC where C.kod_uc = KUC.kod_uc) as nazev2
 
 from pichacky P, ciselnik C
 where P.kod = C.kod and P.datum >= %DATUMOD% and P.datum <= %DATUMDO%
 order by P.kod
</small>
: <small>hd 61840</small>



== Odkazy ==
* hd 61840
* [[Generátor sestav II]]
* [[Generátor sestav II (příklady sestav)]]