-- 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;