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
|
SELECT DATEADD(MONTH, n + 11, Mois)AS Mois,
COALESCE(SUM(Montant), 0) AS Total
FROM
(
SELECT
DATEADD(
MONTH
,DATEDIFF(
MONTH
,0
,GETDATE()
) - 11
,0
) AS Mois
) AS M(Mois)
CROSS JOIN (
SELECT -12 AS n
UNION ALL SELECT -11
UNION ALL SELECT -10
UNION ALL SELECT -9
UNION ALL SELECT -8
UNION ALL SELECT -7
UNION ALL SELECT -6
UNION ALL SELECT -5
UNION ALL SELECT -4
UNION ALL SELECT -3
UNION ALL SELECT -2
UNION ALL SELECT -1
) Nb(n)
LEFT JOIN LaTable
ON LaDate >= DATEADD(MONTH, n , Mois)
AND LaDate < DATEADD(MONTH, n + 11, Mois)
GROUP BY DATEADD(MONTH, n + 11, Mois) |
Partager