I have to cumulative sum by month a quantity but in some months there's no quantity and SQL does not show these rows.

I have tried multiple other solutions I found here but none of them worked or at least I couldn't get them working. Currently, my code is as follows:

SELECT DISTINCT
         A.FromDate
        ,A.ToDate
        ,A.OperationType
        ,A.[ItemCode]
        ,SUM(A.[Quantity]) OVER (PARTITION BY [ItemCode],OperationType,YEAR ORDER BY MONTH) [Quantity]

 FROM (
        SELECT 
         CONVERT(DATE,DATEADD(yy, DATEDIFF(yy, 0, T.OrderDate), 0)) AS FromDate
        ,EOMONTH(T.OrderDate) ToDate
        ,DATEPART(MONTH, t.OrderDate) AS [Month]
                ,DATEPART(YEAR, t.OrderDate) AS [Year]
                ,SUM(T.[Quantity]) [Quantity]
        ,OperationType
        ,[ItemCode]
        FROM TEST T

        WHERE [ItemCode] != ''

        GROUP BY T.OrderDate,[ItemCode],OperationType
        
      ) A

With these results:

And I was expecting these results:

SQL Fiddle link: http://www.sqlfiddle.com/#!18/04a997/1

I would really appreciate some help. Thank you

CodePudding user response：

Here is one way:

WITH m(Earliest,Latest) AS
(
  SELECT DATEADD(DAY,1,MIN(EOMONTH(OrderDate,-1))),
    MAX(EOMONTH(OrderDate)) FROM dbo.TEST
), TypeCodes AS 
(
  SELECT DISTINCT ItemCode, OperationType
  FROM dbo.TEST
), Months AS
(
  SELECT Month = DATEADD(MONTH, ROW_NUMBER() 
    OVER (ORDER BY @@SPID)-1, Earliest)
  FROM m CROSS APPLY STRING_SPLIT(REPLICATE(',',
    DATEDIFF(MONTH,Earliest,Latest)),',')
), raw AS
(
  SELECT m.Month, i.OperationType, i.ItemCode, 
    Q = COALESCE(SUM(Quantity),0)
  FROM Months AS m
  CROSS JOIN TypeCodes AS i
  LEFT OUTER JOIN dbo.TEST AS t
  ON t.OrderDate >= m.Month
  AND t.OrderDate < DATEADD(MONTH, 1, m.Month)
  AND i.ItemCode = t.ItemCode
  AND i.OperationType = t.OperationType
  GROUP BY m.Month, i.OperationType, i.ItemCode
)
SELECT FromDate = Month, 
       ToDate = EOMONTH(Month),
       OperationType, 
       ItemCode, 
       Quantity = SUM(Q) OVER (ORDER BY Month)
FROM raw;

Working example in this fiddle.