YTD in SQL Query using Self-Join

YTD - Abbreviation for YTD is Year-To-Date. YTD function will sum the measurable value from the period beginning January 1st of any year we specified up until the date we specified in the same year.

Database - “dbo.YTD“

Table  Structure

Sample Records

SQL Query for YTD using Self-Join

/* Passing Date as a parameter*/
Declare @Date Datetime
Set @Date = ‘Mar 01, 08′
/* Calculating the Year beginning based on the Date parameter */
Declare @SDate Datetime
Set @SDate = Convert(Varchar, Convert(Datetime, @Date) – Datepart(y, Convert(Datetime, @Date)-1),7)
/* Converting the Date parameter to a generalized format */
Declare @EDate Datetime
Set @EDate = Convert(Varchar,(@Date),7)
/* Passing Product a parameter */
Declare @Product Nvarchar(25)
Set @Product = ‘AA’
/* Self-Join Query to display both YTD amount and the SaleAmount for a Particular Date in a single Query  */
Select A.ProductName, B.SalesAmount, Sum(A_sales) YTDSalesAmount
From

/* Query for calculating “YTD” */
(   Select ProductName, Sum(SalesAmount)as A_sales
From dbo.YTD
Where Date Between @SDate And @EDate AND ProductName = @Product
Group By ProductName    )A,
/* Query to show the “SalesAmount” for the “Date” Parameter*/
(   Select ProductName, SalesAmount
From dbo.YTD
Where Date = @EDate AND ProductName = @Product    )B


Group By A.ProductName, B.SalesAmount

Result

1. Date = ‘Mar 01, 08′ and Product =’AA’.

2. Date = ‘Mar 01, 08′ and Product =’AB.

Same like this by changing the “Date” Parameter, we can get YTD for different Dates.