-- Urspünglich für Club 74

select p.LastName as Nachname, p.FirstName as Vorname,  Round(scap.ApprovedBETotal, 2) as 'Anzahl Sonderstunden', cb2sc.ApprovedStartDate as 'HP Bewilligt von', cb2sc.ApprovedEndDate as 'HP Bewilligt bis', 
o.`Name` as 'Kostentraeger', scap.Start AS BewilligungStart, scap.EndDate AS BewilligungEnde
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 `supportconceptapprovalperiod` scap on cb2sc.`Oid` = scap.`CostBearer2SupportConceptOid` 
join `organisation` o on cb.`Oid` = o.`CostBearerOid` 
WHERE cust.IsActive = 1 and sc.IsActive = 1 AND scap.ApprovedBETotal > 0  AND lower(scap.Notice) LIKE '%sonderstunde%'
order by p.LastName;