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 2021

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 = '2021-01-01') > 0
and A.datum_nastupu <= '2021-01-31' and (datum_vystupu >= '2021-01-01' or datum_vystupu is null)
then 1 else null 
end as K_2021_01,
case when
(select sum(kod) from pichacky P where P.os_cislo = A.os_cislo and P.kod <>501 and P.datum = '2021-02-01') > 0
and A.datum_nastupu <= '2021-02-28' and (datum_vystupu >= '2021-02-01' or datum_vystupu is null)
then 1 else null 
end as K_2021_02,
case when
(select sum(kod) from pichacky P where P.os_cislo = A.os_cislo and P.kod <>501 and P.datum = '2021-03-01') > 0
and A.datum_nastupu <= '2021-03-31' and (datum_vystupu >= '2021-03-01' or datum_vystupu is null)
then 1 else null 
end as K_2021_03,
case when
(select sum(kod) from pichacky P where P.os_cislo = A.os_cislo and P.kod <>501 and P.datum = '2021-04-01') > 0
and A.datum_nastupu <= '2021-04-30' and (datum_vystupu >= '2021-04-01' or datum_vystupu is null)
then 1 else null 
end as K_2021_04,
case when
(select sum(kod) from pichacky P where P.os_cislo = A.os_cislo and P.kod <>501 and P.datum = '2021-05-01') > 0
and A.datum_nastupu <= '2021-05-31' and (datum_vystupu >= '2021-05-01' or datum_vystupu is null)
then 1 else null 
end as K_2021_05,
case when
(select sum(kod) from pichacky P where P.os_cislo = A.os_cislo and P.kod <>501 and P.datum = '2021-06-01') > 0
and A.datum_nastupu <= '2021-06-30' and (datum_vystupu >= '2021-06-01' or datum_vystupu is null)
then 1 else null 
end as K_2021_06,
case when
(select sum(kod) from pichacky P where P.os_cislo = A.os_cislo and P.kod <>501 and P.datum = '2021-07-01') > 0
and A.datum_nastupu <= '2021-07-31' and (datum_vystupu >= '2021-07-01' or datum_vystupu is null)
then 1 else null 
end as K_2021_07,
case when
(select sum(kod) from pichacky P where P.os_cislo = A.os_cislo and P.kod <>501 and P.datum = '2021-08-01') > 0
and A.datum_nastupu <= '2021-08-31' and (datum_vystupu >= '2021-08-01' or datum_vystupu is null)
then 1 else null 
end as K_2021_08,
case when
(select sum(kod) from pichacky P where P.os_cislo = A.os_cislo and P.kod <>501 and P.datum = '2021-09-01') > 0
and A.datum_nastupu <= '2021-09-30' and (datum_vystupu >= '2021-09-01' or datum_vystupu is null)
then 1 else null 
end as K_2021_09,
case when
(select sum(kod) from pichacky P where P.os_cislo = A.os_cislo and P.kod <>501 and P.datum = '2021-10-01') > 0
and A.datum_nastupu <= '2021-10-31' and (datum_vystupu >= '2021-10-01' or datum_vystupu is null)
then 1 else null 
end as K_2021_10,
case when
(select sum(kod) from pichacky P where P.os_cislo = A.os_cislo and P.kod <>501 and P.datum = '2021-11-01') > 0
and A.datum_nastupu <= '2021-11-30' and (datum_vystupu >= '2021-11-01' or datum_vystupu is null)
then 1 else null 
end as K_2021_11,
case when
(select sum(kod) from pichacky P where P.os_cislo = A.os_cislo and P.kod <>501 and P.datum = '2021-12-01') > 0
and A.datum_nastupu <= '2021-12-31' and (datum_vystupu >= '2021-12-01' or datum_vystupu is null)
then 1 else null 
end as K_2021_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)