21 lines
1.4 KiB
Plaintext
21 lines
1.4 KiB
Plaintext
-- Bei Monvita 1992495802 (APP1)
|
|
-- Hilfepläne ohne Bewertung in den letzten 5 Monaten (6 Monate sind LVR Vorgabe)
|
|
|
|
SELECT
|
|
p.LastName AS Nachname, p.FirstName AS Vorname, sc.oid,
|
|
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',
|
|
rating.letzteBewertung AS 'Datum der letzten Bewertung'
|
|
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`
|
|
LEFT JOIN (SELECT rat.SupportConceptOid AS ratscoid, MAX(rat.startdate) AS letzteBewertung FROM rating rat
|
|
GROUP BY rat.SupportConceptOid) AS rating ON rating.ratscoid = sc.oid
|
|
WHERE (letzteBewertung < DATE_ADD(CURDATE(), INTERVAL -5 MONTH) or letzteBewertung is null)
|
|
AND cust.IsActive = 1 AND sc.IsActive = 1
|
|
ORDER BY p.lastname |