-- Ursprünglich für Club 74

select p.LastName as Nachname, p.FirstName as Vorname,  
 cb2sc.`ApprovedStartDate` as 'Bewilligt von', cb2sc.`ApprovedEndDate` as 'Bewilligt bis', 
 o.`Name` as 'Kostentraeger',
 cust.TerminationDate as 'Betreuungsende'
 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 cust.IsActive <> 0 and sc.IsActive <> 0 and cb2sc.`ApprovedStartDate` is not null and cb2sc.`ApprovedEndDate` is not null 
 and ((cb2sc.`ApprovedStartDate` < ':Monat_End' and cb2sc.`ApprovedEndDate` >= DATE_ADD(':Monat_End', INTERVAL -1 DAY) and cust.TerminationDate is null) 
 or (cust.TerminationDate is not null and cb2sc.`ApprovedStartDate` < ':Monat_End' and cust.TerminationDate >= DATE_ADD(':Monat_End', INTERVAL -1 DAY) and cust.TerminationDate < cb2sc.`ApprovedEndDate`)
 or (cust.TerminationDate is not null and cb2sc.`ApprovedStartDate` < ':Monat_End' and cb2sc.ApprovedEndDate >= DATE_ADD(':Monat_End', INTERVAL -1 DAY) and cust.TerminationDate >= cb2sc.`ApprovedEndDate`))
 order by p.LastName, p.FirstName;