Počet směn individuálního kalendáře (sestava SQL): Porovnání verzí

Z Prirucky
(Založena nová stránka: '''Počet směn individuálního kalendáře (sestava SQL)''' - sestavu je potřeba upravit na příslušné období. 550px == Inform…)
 
 
Řádek 1: Řádek 1:
'''Počet směn individuálního kalendáře (sestava SQL)''' - sestavu je potřeba upravit na příslušné období.
'''Počet směn individuálního kalendáře (sestava SQL)'''.


[[Image:C-rozvrh-indiv kal-sql.png|550px]]
[[Image:C-rozvrh-indiv kal-sql.png|550px]]


== Informix ==
Zadává se:
 
* '''datum od''' - podle db serveru ''(tj. buď například 2016-08-01, nebo 01.08.2016)''.
select indiv_kal.cis_rozvrhu, indiv_kal.os_cislo, cislo_pom,
* '''datum do'''
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


== SQL skript ==
<small>
select A.cis_rozvrhu, A.os_cislo, A.cislo_pom, P.prijmeni, P.jmeno, P.titul,
(select count(*) from indiv_kal_dny B where A.cis_rozvrhu = B.cis_rozvrhu and
(B.pocet_hodin is not null and B.pocet_hodin <> 0) and
B.kal_den >= '%DAT_OD%' and B.kal_den <= '%DAT_DO%') as poc,
(select sum (pocet_hodin) from indiv_kal_dny C where A.cis_rozvrhu = C.cis_rozvrhu and C.pocet_hodin is not null and
C.kal_den >= '%DAT_OD%' and C.kal_den <= '%DAT_DO%') as sum_hodin,
(select sum (pocet_hodin) from indiv_kal_dny C where A.cis_rozvrhu = C.cis_rozvrhu and C.pocet_hodin is not null and
C.kal_den >=  '%DAT_OD%' and C.kal_den <= '%DAT_DO%')/(select count(*) from indiv_kal_dny B
where A.cis_rozvrhu = B.cis_rozvrhu and (B.pocet_hodin is not null and B.pocet_hodin <> 0) and
B.kal_den >= '%DAT_OD%' and B.kal_den <= '%DAT_DO%') as prumer
from indiv_kal A, pracovnici P where A.os_cislo = P.os_cislo
</small>
== Odkazy ==
== Odkazy ==
* [[Individuální kalendář]]
* [[Individuální kalendář]]
* [[Generátor sestav II (příklady sestav)]]

Aktuální verze z 5. 10. 2016, 11:16

Počet směn individuálního kalendáře (sestava SQL).

C-rozvrh-indiv kal-sql.png

Zadává se:

  • datum od - podle db serveru (tj. buď například 2016-08-01, nebo 01.08.2016).
  • datum do

SQL skript

select A.cis_rozvrhu, A.os_cislo, A.cislo_pom, P.prijmeni, P.jmeno, P.titul, 

(select count(*) from indiv_kal_dny B where A.cis_rozvrhu = B.cis_rozvrhu and 
(B.pocet_hodin is not null and B.pocet_hodin <> 0) and 
B.kal_den >= '%DAT_OD%' and B.kal_den <= '%DAT_DO%') as poc,

(select sum (pocet_hodin) from indiv_kal_dny C where A.cis_rozvrhu = C.cis_rozvrhu and C.pocet_hodin is not null and 
C.kal_den >= '%DAT_OD%' and C.kal_den <= '%DAT_DO%') as sum_hodin,

(select sum (pocet_hodin) from indiv_kal_dny C where A.cis_rozvrhu = C.cis_rozvrhu and C.pocet_hodin is not null and
C.kal_den >=  '%DAT_OD%' and C.kal_den <= '%DAT_DO%')/(select count(*) from indiv_kal_dny B
where A.cis_rozvrhu = B.cis_rozvrhu and (B.pocet_hodin is not null and B.pocet_hodin <> 0) and 
B.kal_den >= '%DAT_OD%' and B.kal_den <= '%DAT_DO%') as prumer 

from indiv_kal A, pracovnici P where A.os_cislo = P.os_cislo

Odkazy

Citováno z „http://prirucky.ksprogram.cz/index.php?title=Počet_směn_individuálního_kalendáře_(sestava_SQL)&oldid=29006“