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

Z Prirucky
Řádek 4: Řádek 4:
[[Image: Vyuctovani dane-Pocet zamestnancu.png|550px]]
[[Image: Vyuctovani dane-Pocet zamestnancu.png|550px]]


== SQL definice - určena pro rok 2021 ==
== SQL definice - určena pro rok 2025 ==
<small>
<small>
  select A.os_cislo, A.prijmeni, A.jmeno, A.datum_nastupu, A.datum_vystupu,
  select A.os_cislo, A.prijmeni, A.jmeno, A.datum_nastupu, A.datum_vystupu,
  case when
  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
  (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 <= '2021-01-31' and (datum_vystupu >= '2021-01-31' or datum_vystupu is null)
  and A.datum_nastupu <= '2025-01-31' and (datum_vystupu >= '2021-01-31' or datum_vystupu is null)
  then 1 else null  
  then 1 else null  
  end as K_2021_01,
  end as K_2025_01,
  case when
  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
  (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 <= '2021-02-28' and (datum_vystupu >= '2021-02-28' or datum_vystupu is null)
  and A.datum_nastupu <= '2025-02-28' and (datum_vystupu >= '2021-02-28' or datum_vystupu is null)
  then 1 else null  
  then 1 else null  
  end as K_2021_02,
  end as K_2025_02,
  case when
  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
  (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 <= '2021-03-31' and (datum_vystupu >= '2021-03-31' or datum_vystupu is null)
  and A.datum_nastupu <= '2025-03-31' and (datum_vystupu >= '2021-03-31' or datum_vystupu is null)
  then 1 else null  
  then 1 else null  
  end as K_2021_03,
  end as K_2025_03,
  case when
  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
  (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 <= '2021-04-30' and (datum_vystupu >= '2021-04-30' or datum_vystupu is null)
  and A.datum_nastupu <= '2025-04-30' and (datum_vystupu >= '2021-04-30' or datum_vystupu is null)
  then 1 else null  
  then 1 else null  
  end as K_2021_04,
  end as K_2025_04,
  case when
  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
  (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 <= '2021-05-31' and (datum_vystupu >= '2021-05-31' or datum_vystupu is null)
  and A.datum_nastupu <= '2025-05-31' and (datum_vystupu >= '2021-05-31' or datum_vystupu is null)
  then 1 else null  
  then 1 else null  
  end as K_2021_05,
  end as K_2025_05,
  case when
  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
  (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 <= '2021-06-30' and (datum_vystupu >= '2021-06-30' or datum_vystupu is null)
  and A.datum_nastupu <= '2025-06-30' and (datum_vystupu >= '2021-06-30' or datum_vystupu is null)
  then 1 else null  
  then 1 else null  
  end as K_2021_06,
  end as K_2025_06,
  case when
  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
  (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 <= '2021-07-31' and (datum_vystupu >= '2021-07-31' or datum_vystupu is null)
  and A.datum_nastupu <= '2025-07-31' and (datum_vystupu >= '2021-07-31' or datum_vystupu is null)
  then 1 else null  
  then 1 else null  
  end as K_2021_07,
  end as K_2025_07,
  case when
  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
  (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 <= '2021-08-31' and (datum_vystupu >= '2021-08-31' or datum_vystupu is null)
  and A.datum_nastupu <= '2025-08-31' and (datum_vystupu >= '2021-08-31' or datum_vystupu is null)
  then 1 else null  
  then 1 else null  
  end as K_2021_08,
  end as K_2025_08,
  case when
  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
  (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 <= '2021-09-30' and (datum_vystupu >= '2021-09-30' or datum_vystupu is null)
  and A.datum_nastupu <= '2025-09-30' and (datum_vystupu >= '2021-09-30' or datum_vystupu is null)
  then 1 else null  
  then 1 else null  
  end as K_2021_09,
  end as K_2025_09,
  case when
  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
  (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 <= '2021-10-31' and (datum_vystupu >= '2021-10-31' or datum_vystupu is null)
  and A.datum_nastupu <= '2025-10-31' and (datum_vystupu >= '2021-10-31' or datum_vystupu is null)
  then 1 else null  
  then 1 else null  
  end as K_2021_10,
  end as K_2025_10,
  case when
  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
  (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 <= '2021-11-30' and (datum_vystupu >= '2021-11-30' or datum_vystupu is null)
  and A.datum_nastupu <= '2025-11-30' and (datum_vystupu >= '2021-11-30' or datum_vystupu is null)
  then 1 else null  
  then 1 else null  
  end as K_2021_11,
  end as K_2025_11,
  case when
  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
  (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 <= '2021-12-31' and (datum_vystupu >= '2021-12-31' or datum_vystupu is null)
  and A.datum_nastupu <= '2025-12-31' and (datum_vystupu >= '2021-12-31' or datum_vystupu is null)
  then 1 else null  
  then 1 else null  
  end as K_2021_12
  end as K_2025_12
   
   
  from pracovnici A order by A.prijmeni;
  from pracovnici A order by A.prijmeni;

Verze z 16. 3. 2026, 11:35

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

Sestava

Vyuctovani dane-Pocet zamestnancu.png

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

Citováno z „http://prirucky.ksprogram.cz/index.php?title=Vyúčtování_daně_-_počet_zaměstnanců_(sestava)&oldid=50992“