YTD Summation

Question

I have a below scenario wherein the left side represents the input dataset and right side represents the required output

Create table #Test
(
FiscalYear int,
Month datetime,
Region varchar(255),
C1 varchar(255),
MTD int
)

insert into #Test
Select 2021,'2021-05-01','Brazil','x',10
union
Select 2021,'2021-05-01','Brazil','y',10
union
Select 2021,'2021-05-01','Brazil','Z',10
union
Select 2021,'2021-07-01','Brazil','x',5
union--
Select 2022,'2021-09-01','Brazil','x',10
union
Select 2022,'2021-09-01','Brazil','y',10
union
Select 2022,'2021-09-01','Brazil','Z',10
union
Select 2022,'2021-10-01','Brazil','x',5

Select * from #test order by month

below was my sample code:

select
a.FiscalYear,a.Month,a.region,a.c1,sum(case when a.FiscalYear=b.fiscalyear then b.MTD else 0 end) as YTD
from #test a
left join #Test b on a.Region=b.Region and a.c1=b.c1
where a.month>=b.month
group by a.FiscalYear,a.Month,a.region,a.c1
order by month

But I am unable to think of a way to pull down the missing values from prev month into next month other than cross Apply but in real scenario my dataset contains millions of records.
So what is the best way to achieve this use case

Answer 1

Since I am not sure how you calculate the YTD column, My script does not include it:

DECLARE @StartDate date;  
DECLARE @EndDate date;  
SELECT @StartDate = MIN([Month]), @EndDate = MAX([Month]) FROM #Test  
  
;WITH CTE_Date AS (  
	SELECT @StartDate AS [Month]  
	UNION ALL  
	SELECT DATEADD(MONTH, 1, [Month]) AS [Month]  
	FROM CTE_Date  
	WHERE [Month] < @EndDate  
),  
CTE_Distinct_C1_And_Region AS (  
	SELECT DISTINCT C1, Region  
	FROM #Test  
),  
CTE_Date_C1_Region AS (  
	SELECT [Month], C1, Region  
	FROM CTE_Date, CTE_Distinct_C1_And_Region  
)  
  
SELECT  
CASE   
	WHEN t.FiscalYear IS NULL THEN   
		CASE WHEN MONTH(c.[Month]) >= 9 THEN YEAR(c.[Month]) + 1 ELSE YEAR(c.[Month]) END  
	ELSE t.FiscalYear   
END AS FiscalYear,  
c.[Month],  
c.Region,  
c.C1,  
ISNULL(t.MTD, 0) AS MTD  
FROM CTE_Date_C1_Region AS c  
LEFT JOIN #Test AS t ON c.[Month] = t.[Month] AND c.Region = t.Region AND c.C1 = t.C1  
ORDER BY c.[Month], c.[C1], c.[Region];  

Answer 2

Create table #Test  
(  
FiscalYear int,  
Month datetime,  
Region varchar(255),  
C1 varchar(255),  
MTD int  
)  
  
  
  
insert into #Test  
Select 2021,'2021-05-01','Brazil','x',10  
union  
Select 2021,'2021-05-01','Brazil','y',10  
union  
Select 2021,'2021-05-01','Brazil','Z',10  
union  
Select 2021,'2021-07-01','Brazil','x',5  
union--  
Select 2022,'2021-09-01','Brazil','x',10  
union  
Select 2022,'2021-09-01','Brazil','y',10  
union  
Select 2022,'2021-09-01','Brazil','Z',10  
union  
Select 2022,'2021-10-01','Brazil','x',5  
;with mycte as  
(Select distinct C1,Region  from #Test)  
  
,mycte2 as (  
Select [Month],  max([Month]) Over() maxMonth  ,n  
from #Test   
Cross apply(values(1),(2),(3),(4),(5)) d(n)    
  
)  
,mycte3 as (  
select distinct dateadd(month,n-1,[Month]) dt , c1 , Region from mycte2,mycte  
where dateadd(month,n-1,[Month])<=maxMonth  
)  
  
select Case when Month(dt)>=9 then Year(dt)+1 else year(dt) end FiscalYear  
,dt,m3.Region,m3.C1,ISNULL(t.MTD,0) MTD,  
Sum(MTD)Over(Partition by m3.Region, Case when Month(dt)>=9 then Year(dt)+1 else year(dt) end, m3.C1 Order by dt) YTD  
from mycte3 m3   
left join #Test t on t.c1=m3.c1 and t.[Month]=m3.dt  
Order by dt,m3.c1  
  
  
drop   table #Test  
   

Answer 3

Please see:

https://codingsight.com/calculating-running-total-with-over-clause-and-partition-by-clause-in-sql-server/