20 lines
1.2 KiB
Plaintext
20 lines
1.2 KiB
Plaintext
|
-- Bei Suchthilfe Ostfriesland 2235342307 (APP2)
|
||
|
|
-- Abwesenheiten pro Monat mit Uhrzeit
|
||
|
|
|
||
|
|
SELECT qry.Nachname, qry.Vorname, qry.Kategorie, DATE(qry.von) AS Start, date_format(qry.von, "%H:%i") AS `Start Uhrzeit`,
|
||
|
|
DATE(qry.bis) AS Ende, date_format(qry.bis, "%H:%i") AS `Ende Uhrzeit`
|
||
|
|
-- ,qry.DauerJahr AS TageProMonat
|
||
|
|
FROM
|
||
|
|
(select p.`FirstName` as Vorname, p.`LastName` as Nachname, ar.`Description` as Kategorie,
|
||
|
|
CASE WHEN at.`Start`>= ':Monat_Start' THEN at.`Start` ELSE ':Monat_Start' END as von,
|
||
|
|
CASE WHEN at.`EndTime`<= ':Monat_End' THEN at.`EndTime` ELSE ':Monat_End' END as bis,
|
||
|
|
CASE WHEN at.`Start`>= ':Monat_Start' THEN datediff(CASE WHEN ifnull(at.`EndTime`, CURDate()) <= ':Monat_End' THEN at.`EndTime` ELSE ':Monat_End' END, at.`Start`)-1
|
||
|
|
ELSE datediff(ifnull(at.`EndTime`, CURDate()), ':Monat_Start') END AS DauerJahr,
|
||
|
|
datediff(ifnull(at.`EndTime`, CURDate()), at.`Start`)-1 AS Dauer,
|
||
|
|
c.Oid AS cOid
|
||
|
|
from `person` p
|
||
|
|
inner join `customer` c on p.`Oid` = c.`PersonOid`
|
||
|
|
inner join `absencetime` at on c.`Oid` = at.`CustomerOid`
|
||
|
|
inner join `absencereason` ar on at.`AbsenceReasonOid` = ar.`Oid`
|
||
|
|
where c.`IsActive` = 1 and at.EndTime >= ':Monat_Start' and at.Start <= ':Monat_End')qry
|
||
|
|
order by qry.Nachname, qry.von;
|