1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
| WITH T_AFF_MENS
( MoisId, AffaireID, NbJoursConsoRA, NbJoursValorisesRA) AS
(
SELECT
COALESCE(M_MAX.MoisID, 2993) as MoisId,A.AffaireID,
SUM(ISNULL(LRA.NbJoursConso,0)) AS NbJoursConsoRA,SUM(ISNULL(LRA.NbJoursValorises,0)) AS NbJoursValorisesRA
from
ra.Mois M_MAX
LEFT JOIN ra.Mois M ON M.DateFin < M_MAX.DateFin
LEFT JOIN ra.ReleveActivite RA ON RA.MoisID = M.MoisID
LEFT JOIN ra.LigneReleveActivite LRA ON LRA.ReleveActiviteID=RA.ReleveActiviteID
LEFT JOIN gda.Affaire A ON A.AffaireID=LRA.AffaireID AND COALESCE(LRA.IsValide,0) = 1
GROUP BY M_MAX.MoisID ,A.AffaireID
)
SELECT M.DateFin, M.MoisID, F.AffaireID, F.MoisId, ISnull(F.NbJoursConsoRA,0) AS NbJoursConsoRA, isnull(F.NbJoursValorisesRA,0) AS NbJoursValorisesRA --, *
FROM
ra.Mois M left join T_AFF_MENS F on F.MoisId = M.MoisID and F.AffaireID=2993
order by M.DateFin asc |
Partager