16 lines
1.3 KiB
Plaintext
16 lines
1.3 KiB
Plaintext
|
-- 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;
|