How to caluclate YTD values from Month values in SQL

Question

Hi Team,
Have one input table like bellow

and I need results as shown in bellow

Answer 1

Try:

select year, month,   
    sum(ExtendedAmount) over (partition by SalesTerritoryCountry, year order by month) as ExtendedAmount,  
    sum(TotalProductCost) over (partition by SalesTerritoryCountry, year order by month) as TotalProductCost,  
    sum(SalesAmount) over (partition by SalesTerritoryCountry, year order by month) as SalesAmount,  
    SalesTerritoryCountry  
from OneInputTable  
order by year, SalesTerritoryCountry, month  

Answer 2

Hi @Bala Narasimha Challa
You can use subquery which works in all versions:

SELECT year,month,  
      (SELECT SUM(ExtendedAmount) FROM #TEST t2 WHERE t1.COUNTRY=t2.COUNTRY and t1.YEAR=t2.YEAR and t2.MONTH<=t1.MONTH) AS ExtendedAmount,  
	  (SELECT SUM(TotalProductCost) FROM #TEST t2 WHERE t1.COUNTRY=t2.COUNTRY and t1.YEAR=t2.YEAR and t2.MONTH<=t1.MONTH) AS TotalProductCost,  
	  (SELECT SUM(SalesAmount) FROM #TEST t2 WHERE t1.COUNTRY=t2.COUNTRY and t1.YEAR=t2.YEAR and t2.MONTH<=t1.MONTH) AS SalesAmount,  
       SalesTerritoryCountry  
FROM YourTable t1  
ORDER BY year,SalesTerritoryCountry,month  

You can use 'ROWS or RANGE' as well for SQL Server 2012 (11.x) and later:

SELECT year, month,   
       SUM(ExtendedAmount) OVER (PARTITION BY SalesTerritoryCountry, year ORDER BY month RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ExtendedAmount,  
       SUM(TotalProductCost) OVER (PARTITION BY SalesTerritoryCountry, year ORDER BY month RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS TotalProductCost,  
       SUM(SalesAmount) OVER (PARTITION BY SalesTerritoryCountry, year ORDER BY month RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS SalesAmount,  
       SalesTerritoryCountry  
FROM YourTable  
ORDER BY year,SalesTerritoryCountry,month  

Note:
1)ROWS or RANGE requires that the ORDER BY clause be specified. If ORDER BY contains multiple order expressions, CURRENT ROW FOR RANGE considers all columns in the ORDER BY list when determining the current row.
2)If 'ORDER BY' is specified, and a ROWS/RANGE is not specified, then default RANGE UNBOUNDED PRECEDING AND CURRENT ROW is used as default for window frame by the functions that can accept optional ROWS/RANGE specification.

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.