YTD Summation

Nandan Hegde 29,886 Reputation points MVP
2022-08-12T13:31:11.917+00:00

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

230744-image.png

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

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,655 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
{count} votes

Accepted answer
  1. Guoxiong 8,126 Reputation points
    2022-08-12T17:32:27.517+00:00

    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];  
    

2 additional answers

Sort by: Most helpful
  1. Jingyang Li 5,891 Reputation points
    2022-08-12T20:17:01.28+00:00
    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  
       
    
    0 comments No comments