As Viorel pointed out, it gets less messy with numeric values for months. And presumably you need the year somewhere as well. Furthermore, I assume that you have tables that define the Regions and Stores. Those tables should replace the CTE AllStores.
An issue with Viorel's query is that performance will be proportional to the square of number of months. This solution avoids this issue:
DROP TABLE IF EXISTS #Peter
CREATE TABLE #Peter (Month char(3) NOT NULL,
Region char(2) NOT NULL,
Store char(2) NOT NULL,
Sales decimal(10,2) NOT NULL
)
INSERT #Peter(Month, Region, Store, Sales)
VALUES
('Jan', 'R1', 'S1', 10.00),
('Jan', 'R1', 'S1', 15.00),
('Jan', 'R1', 'S1', 30.00),
('Jan', 'R1', 'S2', 5.00),
('Jan', 'R1', 'S2', 1.00),
('Jan', 'R2', 'S3', 17.00),
('Jan', 'R2', 'S3', 5.00),
('Jan', 'R2', 'S4', 60.00),
('Jan', 'R2', 'S4', 45.00),
('Jan', 'R2', 'S4', 11.00),
('Feb', 'R1', 'S1', 20.00),
('Feb', 'R1', 'S1', 40.00),
('Feb', 'R1', 'S1', 60.00),
('Feb', 'R2', 'S3', 10.00),
('Feb', 'R2', 'S3', 10.00),
('Feb', 'R2', 'S3', 10.00),
('Feb', 'R2', 'S3', 10.00)
go
; WITH AllStores AS (
SELECT DISTINCT Region, Store FROM #Peter
), Months AS (
SELECT value AS monthno,
choose(value, 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',
'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec') AS Month
FROM generate_series(1, 23)
)
SELECT m.Month, a.Region, a.Store,
SUM(SUM(isnull(p.Sales, 0))) OVER(PARTITION BY a.Region, a.Store
ORDER BY m.monthno
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Months m
CROSS JOIN AllStores a
LEFT JOIN #Peter p ON p.Month = m.Month
AND p.Region = a.Region
AND p.Store = a.Store
GROUP BY m.Month, m.monthno, a.Region, a.Store
ORDER BY m.monthno, a.Region, a.Store
go
;WITH MonthlyStoreSales AS
(
SELECT
[Month],
[Region],
[Store],
SUM([Sales]) AS MonthlySales
FROM #Peter
GROUP BY [Month], [Region], [Store]
)
SELECT
[Month],
[Region],
[Store],
SUM(MonthlySales) OVER
(
PARTITION BY [Region], [Store]
ORDER BY [Month]
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS Sales
FROM MonthlyStoreSales
ORDER BY [Month], [Region], [Store];
I deleted the AI answer, because it was not producing the desired result.