How to query cumulative sum period by month-CodePudding

I have an issue with table source like this:

Available month in table is only as is from column month, no April, May, June etc.

I need to create a view to create "Year to Date" table as shown here:

I need to view all months from Year 2020 January - December with value based on table source.

VALUE COLUMN filled by Source with same month, but because April not available from source, so it automatically gets set to 0 and others are same

For CUMULATIVE VALUE COLUMN filled by value Sum from last month value

I've tried using case when query but it not simple if I have to many Name and not dynamic

CodePudding user response：

You can use next SQL construction:

 WITH Calendar AS (
    -- generate Year, Month table for each Name
    SELECT Year, Month, Name
    FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12)) AS Months(Month)
    CROSS JOIN (VALUES (2020)) AS years(Year)
    CROSS JOIN (SELECT DISTINCT Name FROM Source) AS Names
) SELECT 
    Calendar.Year,
    Calendar.Month,
    Calendar.Name, 
    COALESCE(Source.Value, 0) AS Value,
    SUM(COALESCE(Source.Value, 0)) OVER(PARTITION BY Calendar.Name ORDER BY Calendar.Year, Calendar.Month) AS cumulativeSum
FROM Calendar
LEFT JOIN Source ON 
    Source.Year = Calendar.Year AND 
    Source.Month = Calendar.Month AND
    Source.Name = Calendar.Name ;

MSSQL cumulative sum

The query use CTE expression for generate calendar table and window function SUM over partition by name to calculate cumulative values