28 lines
2.0 KiB
Plaintext
28 lines
2.0 KiB
Plaintext
|
-- FÜR SEHT Münster
|
||
|
|
|
||
|
|
SELECT
|
||
|
|
CONCAT(p.`LastName`, ', ', p.`FirstName`) as 'KlientIn',
|
||
|
|
(select GROUP_CONCAT(CASE WHEN ValueListEntryOid = 12 THEN 'Psychisch' WHEN ValueListEntryOid = 11 THEN 'Geistig' ELSE 'BEIDES' END ORDER BY ValueListEntryOid) AS OID FROM valuelistentry2object vl2o where vl2o.`ValueListEntryOid` in (11,12) and vl2o.`ObjectOid` = c.`Oid` and vl2o.`ObjectTid` = 8 GROUP BY vl2o.`ObjectOid`) as Behinderung,
|
||
|
|
(select GROUP_CONCAT(CASE WHEN ValueListEntryOid = 47 THEN 'Psychisch' WHEN ValueListEntryOid = 48 THEN 'Geistig' ELSE 'BEIDES' END ORDER BY ValueListEntryOid) AS OID FROM valuelistentry2object vl2o where vl2o.`ValueListEntryOid` in (47,48) and vl2o.`ObjectOid` = c.`Oid` and vl2o.`ObjectTid` = 8 GROUP BY vl2o.`ObjectOid`) as 'Betreuung',
|
||
|
|
|
||
|
|
DATE_FORMAT(cb2sc.ApprovedStartDate, '%d.%m.%Y') as 'Bewilligt von',
|
||
|
|
DATE_FORMAT(cb2sc.ApprovedEndDate, '%d.%m.%Y') as 'Bewilligt bis',
|
||
|
|
org.Name as 'Kostentraeger',
|
||
|
|
ROUND(sr.`GeleisteteDir`, 2) as 'Minuten direkt',
|
||
|
|
ROUND(sr.`GeleisteteDir` / 60, 2) as 'Stunden direkt'
|
||
|
|
FROM `supportconcept` sc
|
||
|
|
INNER JOIN `costbearer2supportconcept` cb2sc ON sc.`Oid` = cb2sc.`SupportConceptOid`
|
||
|
|
INNER JOIN `costbearer` cb ON cb2sc.`CostBearerOid` = cb.`Oid`
|
||
|
|
INNER JOIN `organisation` org ON org.`CostBearerOid` = cb.`Oid`
|
||
|
|
INNER JOIN `customer` c ON sc.`CustomerOid` = c.`Oid`
|
||
|
|
INNER JOIN `person` p on c.`PersonOid` = p.`Oid`
|
||
|
|
LEFT JOIN
|
||
|
|
(SELECT sr2.`CostBearer2SupportConceptOid`, SUM(sr2.`roundedduration` / IF(sr2.`GroupEmployeeCount` is null, 1, sr2.`GroupEmployeeCount`)) AS GeleisteteDir FROM `servicerecord` sr2
|
||
|
|
INNER JOIN `servicedescription` sd ON sr2.`ServiceDescriptionOid` = sd.`Oid`
|
||
|
|
INNER JOIN `servicecategory` sc ON sd.`ServiceCategoryOid` = sc.`Oid`
|
||
|
|
WHERE sr2.`StartDate` >= ':Jahr_Start' AND sr2.`StartDate` < ':Jahr_End' AND sc.Oid In (1,5) GROUP BY sr2.`CostBearer2SupportConceptOid`)
|
||
|
|
AS sr ON sr.`CostBearer2SupportConceptOid` = cb2sc.`Oid`
|
||
|
|
|
||
|
|
|
||
|
|
WHERE c.`IsActive` = 1 AND sc.`IsActive` = 1
|
||
|
|
ORDER BY p.`LastName`, p.`FirstName`, cb2sc.`ApprovedStartDate`
|