Vyúčtování daně - počet zaměstnanců (sestava)

Vyúčtování daně - počet zaměstnanců (sestava)

Sestava

SQL definice - určena pro rok 2025

select A.os_cislo, A.prijmeni, A.jmeno, A.datum_nastupu, A.datum_vystupu,
case when
(select sum(kod) from pichacky P where P.os_cislo = A.os_cislo and P.kod <>501 and P.datum = '2025-01-01') > 0
and A.datum_nastupu <= '2025-01-31' and (datum_vystupu >= '2021-01-31' or datum_vystupu is null)
then 1 else null 
end as K_2025_01,
case when
(select sum(kod) from pichacky P where P.os_cislo = A.os_cislo and P.kod <>501 and P.datum = '2025-02-01') > 0
and A.datum_nastupu <= '2025-02-28' and (datum_vystupu >= '2021-02-28' or datum_vystupu is null)
then 1 else null 
end as K_2025_02,
case when
(select sum(kod) from pichacky P where P.os_cislo = A.os_cislo and P.kod <>501 and P.datum = '2025-03-01') > 0
and A.datum_nastupu <= '2025-03-31' and (datum_vystupu >= '2021-03-31' or datum_vystupu is null)
then 1 else null 
end as K_2025_03,
case when
(select sum(kod) from pichacky P where P.os_cislo = A.os_cislo and P.kod <>501 and P.datum = '2025-04-01') > 0
and A.datum_nastupu <= '2025-04-30' and (datum_vystupu >= '2021-04-30' or datum_vystupu is null)
then 1 else null 
end as K_2025_04,
case when
(select sum(kod) from pichacky P where P.os_cislo = A.os_cislo and P.kod <>501 and P.datum = '2025-05-01') > 0
and A.datum_nastupu <= '2025-05-31' and (datum_vystupu >= '2021-05-31' or datum_vystupu is null)
then 1 else null 
end as K_2025_05,
case when
(select sum(kod) from pichacky P where P.os_cislo = A.os_cislo and P.kod <>501 and P.datum = '2025-06-01') > 0
and A.datum_nastupu <= '2025-06-30' and (datum_vystupu >= '2021-06-30' or datum_vystupu is null)
then 1 else null 
end as K_2025_06,
case when
(select sum(kod) from pichacky P where P.os_cislo = A.os_cislo and P.kod <>501 and P.datum = '2025-07-01') > 0
and A.datum_nastupu <= '2025-07-31' and (datum_vystupu >= '2021-07-31' or datum_vystupu is null)
then 1 else null 
end as K_2025_07,
case when
(select sum(kod) from pichacky P where P.os_cislo = A.os_cislo and P.kod <>501 and P.datum = '2025-08-01') > 0
and A.datum_nastupu <= '2025-08-31' and (datum_vystupu >= '2021-08-31' or datum_vystupu is null)
then 1 else null 
end as K_2025_08,
case when
(select sum(kod) from pichacky P where P.os_cislo = A.os_cislo and P.kod <>501 and P.datum = '2025-09-01') > 0
and A.datum_nastupu <= '2025-09-30' and (datum_vystupu >= '2021-09-30' or datum_vystupu is null)
then 1 else null 
end as K_2025_09,
case when
(select sum(kod) from pichacky P where P.os_cislo = A.os_cislo and P.kod <>501 and P.datum = '2025-10-01') > 0
and A.datum_nastupu <= '2025-10-31' and (datum_vystupu >= '2021-10-31' or datum_vystupu is null)
then 1 else null 
end as K_2025_10,
case when
(select sum(kod) from pichacky P where P.os_cislo = A.os_cislo and P.kod <>501 and P.datum = '2025-11-01') > 0
and A.datum_nastupu <= '2025-11-30' and (datum_vystupu >= '2021-11-30' or datum_vystupu is null)
then 1 else null 
end as K_2025_11,
case when
(select sum(kod) from pichacky P where P.os_cislo = A.os_cislo and P.kod <>501 and P.datum = '2025-12-01') > 0
and A.datum_nastupu <= '2025-12-31' and (datum_vystupu >= '2021-12-31' or datum_vystupu is null)
then 1 else null 
end as K_2025_12

from pracovnici A order by A.prijmeni;

Po zkopírování do MS Excel je možno provést součty pod jednotlivé sloupce - tím bude stanoven počet za daný měsíc.

Odkazy

• Generátor sestav II (příklady sestav)
• Vyúčtování daně (za rok 2018)