1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
|
SELECT T5.NS_SERVICE_ID,
CASE WHEN T5.NS_FRATRIE_ID IS NULL THEN 0
ELSE T5.NS_FRATRIE_ID
END AS FRATRIE_ID,
COUNT (*) AS NB_PEC
FROM (
SELECT T1.NS_SERVICE_ID, (SELECT T2.NS_FRATRIE_ID
FROM CIOC.FRATRIE T2
WHERE T2.NS_JEUNE_ID = T1.NS_JEUNE_ID
AND T2.DT_DEBUT <= CURRENT_DATE
AND (T2.DT_FIN >= CURRENT_DATE OR T2.DT_FIN IS NULL))
FROM CIOC.PEC T1, MSIS.SERVICE, MSIS.VW_MSIS0012 T4
WHERE T1.NS_SERVICE_ID = T4.NS_SERVICE_ID
AND MSIS.SERVICE.NS_SERVICE_ID_LNK = T4.NS_SERVICE_ID_LNK
AND MSIS.SERVICE.NS_SERVICE_ID = :AI_NS_SERVICE_ID AND T1.SW_COMPTE = 'O'
AND T1.DT_DEB_MANDAT <= :AI_DT_DEBUT
AND T1.DT_FIN_MANDAT > :AI_DT_FIN
AND T4.DT_DEBUT <= :AI_DT_DEBUT
AND (T4.DT_FIN >= :AI_DT_FIN OR T4.DT_FIN IS NULL)
AND MSIS.SERVICE.DT_DEBUT <= :AI_DT_DEBUT
AND (MSIS.SERVICE.DT_FIN >= :AI_DT_FIN OR MSIS.SERVICE.DT_FIN IS NULL)
) T5
GROUP BY T5.NS_SERVICE_ID, T5.NS_FRATRIE_ID |
Partager