-- zunächst für Verein für Beratung und Therapie (LWV)
-- 7113513872 (APP3)

SELECT p.LastName as Nachname, p.FirstName as Vorname,  
ROUND(DATEDIFF(CURDATE(), cb2sc.`RequestedStartDate`) / 30, 2) as 'Monate seit Beginn der Betreuung', cb2sc.ApprovedStartDate as 'Bewilligt von', cb2sc.ApprovedEndDate as 'Bewilligt bis', 
o.`Name` as 'Kostentraeger', 
(select CONCAT(emp1p.`FirstName`, ' ', emp1p.`LastName`) from employee emp1 
join `person` emp1p on emp1p.`Oid` = emp1.`PersonOid` 
where emp1.`Oid` in (select employeeoid from `employee2customer` e2c1 
join `valuelistentry2object` vl2o on e2c1.`Oid` = vl2o.`ObjectOid` 
join `valuelistentry` vle on vl2o.`ValueListEntryOid` = vle.`Oid` 
where e2c1.`CustomerOid` = cust.`Oid` and vle.`SystemEntryID` = 3) LIMIT 1) as 'Bezugsbetreuung' from `person` p 
join `customer` cust on p.Oid = cust.PersonOid join `supportconcept` sc on cust.Oid = sc.CustomerOid 
join `costbearer2supportconcept` cb2sc on sc.Oid = cb2sc.SupportConceptOid 
join `costbearer` cb on cb2sc.`CostBearerOid` = cb.`Oid` 
join `organisation` o on cb.`Oid` = o.`CostBearerOid` 
WHERE cb2sc.ApprovedEndDate is not null 
AND cb2sc.ApprovedEndDate < CONCAT(YEAR(CURDATE()),-12-31) and cb2sc.ApprovedEndDate >= CURDATE() and cust.IsActive <> 0 and sc.IsActive = 1 
ORDER BY cb2sc.ApprovedEndDate;