29 lines
1.9 KiB
Plaintext
29 lines
1.9 KiB
Plaintext
-- Anzahl Termine nach Leistung einer bestimmten Leistungskategorie im Jahr
|
|
-- Bei Kette e.V. 9383760246 (APP2)
|
|
-- Leider lassen sich die Spaltenüberschriften nicht flexibel setzen, wenn neue Leistungen dazu kommen. Aber "Restspalte" fängt es auf
|
|
SELECT
|
|
org.Name AS Jugendamt,
|
|
YEAR(':Jahr_Start') AS Jahr,
|
|
COUNT(CASE WHEN sd.ServiceCategoryOid = 47 THEN 1 END) AS 'Termine insgesamt',
|
|
COUNT(CASE WHEN sr.ServiceDescriptionOid = 136 THEN 1 END) AS Ketteräume,
|
|
COUNT(CASE WHEN sr.ServiceDescriptionOid = 135 THEN 1 END) AS aufsuchend,
|
|
COUNT(CASE WHEN sr.ServiceDescriptionOid = 125 THEN 1 END) AS Video,
|
|
COUNT(CASE WHEN sr.ServiceDescriptionOid = 109 THEN 1 END) AS Telefonkontakt,
|
|
COUNT(CASE WHEN sr.ServiceDescriptionOid = 112 THEN 1 END) AS Hilfeplangespräch,
|
|
COUNT(CASE WHEN sr.ServiceDescriptionOid = 137 THEN 1 END) AS 'E-Mail',
|
|
COUNT(CASE WHEN sr.ServiceDescriptionOid = 138 THEN 1 END) AS Fahrtzeit,
|
|
COUNT(CASE WHEN sr.ServiceDescriptionOid = 140 THEN 1 END) AS 'Terminabsage (<24h)',
|
|
COUNT(CASE WHEN sd.ServiceCategoryOid = 47 AND sr.ServiceDescriptionOid NOT IN (109, 112, 125, 135, 136, 137, 138, 140) THEN 1 END) AS 'Weitere Leistungen',
|
|
(COUNT(CASE WHEN sr.ServiceDescriptionOid = 135 THEN 1 END) /
|
|
(COUNT(CASE WHEN sr.ServiceDescriptionOid = 136 THEN 1 END) + COUNT(CASE WHEN sr.ServiceDescriptionOid = 125 THEN 1 END))) AS 'aufsuchend / Ketteräume + Video'
|
|
FROM servicerecord sr
|
|
INNER JOIN costbearer2supportconcept cb2sc ON sr.CostBearer2SupportconceptOid = cb2sc.Oid
|
|
Inner Join costbearer cb ON cb.Oid = cb2sc.CostBearerOid
|
|
Right JOIN organisation org ON org.CostBearerOid = cb.Oid
|
|
JOIN servicedescription sd ON sr.ServiceDescriptionOid = sd.Oid
|
|
WHERE StartDate >= ':Jahr_Start' and EndDate <= ':Jahr_End'
|
|
GROUP BY org.Name
|
|
HAVING COUNT(CASE WHEN sd.ServiceCategoryOid = 47 THEN 1 END) > 0
|
|
ORDER BY org.Name;
|
|
|
|
-- SELECT * FROM `9383760246`.servicedescription where ServiceCategoryOid = 47; |