Počet směn individuálního kalendáře (sestava SQL)
Počet směn individuálního kalendáře (sestava SQL) - sestavu je potřeba upravit na příslušné období.
Informix
select indiv_kal.cis_rozvrhu, indiv_kal.os_cislo, cislo_pom, prijmeni, jmeno, titul, (select count(*) from indiv_kal_dny b where indiv_kal.cis_rozvrhu = b.cis_rozvrhu and (pocet_hodin is not null and pocet_hodin <> 0) and b.kal_den >= '01.01.2012' and b.kal_den <= '31.12.2012') as poc,
(select sum (pocet_hodin) from indiv_kal_dny c where indiv_kal.cis_rozvrhu = c.cis_rozvrhu and c.kal_den >= '01.01.2012' and c.kal_den <= '31.12.2012') as sum_hodin,
(select sum (pocet_hodin) from indiv_kal_dny c where indiv_kal.cis_rozvrhu = c.cis_rozvrhu and c.kal_den >= '01.01.2012' and c.kal_den <= '31.12.2012')/(select count(*) from indiv_kal_dny b where indiv_kal.cis_rozvrhu = b.cis_rozvrhu and (pocet_hodin is not null and pocet_hodin <> 0) and b.kal_den >= '01.01.2012' and b.kal_den <= '31.12.2012') as prumer
from indiv_kal, pracovnici where indiv_kal.os_cislo = pracovnici.os_cislo
MS SQL
select indiv_kal.cis_rozvrhu, indiv_kal.os_cislo, cislo_pom, prijmeni, jmeno, titul, (select count(*) from indiv_kal_dny b where indiv_kal.cis_rozvrhu = b.cis_rozvrhu and (pocet_hodin is not null and pocet_hodin <> 0) and b.kal_den >= '2012-01-01' and b.kal_den <= '2012-12-31') as poc,
(select sum (pocet_hodin) from indiv_kal_dny c where indiv_kal.cis_rozvrhu = c.cis_rozvrhu and c.kal_den >= '2012-01-01' and c.kal_den <= '2012-12-31') as sum_hodin,
(select sum (pocet_hodin) from indiv_kal_dny c where indiv_kal.cis_rozvrhu = c.cis_rozvrhu and c.kal_den >= '2012-01-01' and c.kal_den <= '2012-12-31')/(select count(*) from indiv_kal_dny b where indiv_kal.cis_rozvrhu = b.cis_rozvrhu and (pocet_hodin is not null and pocet_hodin <> 0) and b.kal_den >= '2012-01-01' and b.kal_den <= '2012-12-31') as prumer from indiv_kal, pracovnici where indiv_kal.os_cislo = pracovnici.os_cislo
