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 31 32 33 34 35 36 37 38 39 40
|
;
WITH DebutMois AS(--calcul du premier jour du mois de la date de debut
SELECT
DATEADD(
MONTH
,DATEDIFF(
MONTH
,0
,@DateDebut
)
,0
) AS Mois
)
,
ListeMois AS(
SELECT
Mois AS Debut
,DATEADD(MONTH, 1, Mois) AS Fin
FROM DebutMois
UNION ALL
SELECT
DATEADD(MONTH, 1, Debut)
,DATEADD(MONTH, 1, Fin)
FROM ListeMois
WHERE Fin < @DateFin
)
SELECT
Debut
,COALESCE(SUM(Ventes.Quantitélivree),0) AS [Dn]
FROM ListeMois M
LEFT OUTER JOIN Ventes V
ON V.DateLivraison >= M.debut
AND V.DateLivraison < M.Fin
AND Ventes.DateLivraison >= @Date_debut
AND Ventes.DateLivraison <= @Date_fin
AND Ventes.Magasin = 'ALPHA'
AND Ventes.Article = '123456'
GROUP BY Debut |
Partager