Often we come across requirements (one of the popular ones is comparing YTD sales of different regions) where we need monthly data to be presented in year to date (YTD as more popularly known) format. This might sound pretty simple but it might dizzy developers who are not masters in SQL programming. I am presenting a simple way to get YTD data.

So, let’s get started. Generally we have data with some date field. I will be using table MyUser which will store information about users and (most importantly their date of creation). The table structure will be something like,

create table MyUser
(
 UserId int,
 UserName varchar(100),
 UserCreationDate datetime
)

First I will group the users based on the month in which they were created (of course we are talking about the same year when we say YTD). For this grouping, I am simply counting the number of users created every month. To store the grouped data you might use a temporary table. I am using a common table expression (CTE) to do the same. The code for the CTE should look something like,

;With monthly(usercount,Period,monthval)
AS
(
 select count(userid) As usercount,
 CONVERT(CHAR(4), UserCreationDate , 100) + CONVERT(CHAR(4), UserCreationDate , 120) As Period,
 month(UserCreationDate ) as monthval
 from MyUser
 where year(UserCreationDate )=year(getdate())
 group by 
 CONVERT(CHAR(4), UserCreationDate , 100) + CONVERT(CHAR(4), UserCreationDate , 120),
 month(UserCreationDate ),
 year(UserCreationDate )
)

We can visualise a CTE as a table having data that is selected by the definition of the CTE, which we can query like any normal table. We can have a look of the data in the CTE,

 select usercount,period,monthval
 from monthly
 order by monthval 

The monthval field is only added for sorting purposes, and we will use it later. One thing to keep in mind while using CTEs is that the statement using the CTE must be the next statement after the CTE (like the select query above), so we need to remove the select query and place the following code to use our CTE,

select 
  sum(mo.usercount) As UsersYTD
  , m.period
  , m.monthval
 from monthly m
  cross join monthly mo
 where mo.monthval <= m.monthval
 and m.monthval <> month(getdate())
 group by m.period,m.monthval
 order by m.monthval  

As you can see, we need the month value to sort the values based on the correct order of months.

Another variant of the same problem can be the YTD data for, say last 6 months. We just need to change the second query a bit and include a month comparison factor in the where clause,

select 
   sum(mo.usercount) As UsersYTD
 , m.period
 , m.monthval
 , m.yearval
 from monthly m
  cross join monthly mo
 where mo.monthval <= m.monthval
 and m.monthval >= month(getdate())-6     
 group by 
   m.period
 , m.monthval
 , m.yearval
 order by 
   m.monthval

So we will get the data only for the set of months that we want,

One point that I have deliberately missed in the previous code is to include the case when we are in the earlier half of the year (current month <= 6). Observing carefully, in that case we need to tweak the base query (the CTE) to include the data for previous year (as last six months will also involve few months from the previous year and we still need YTD data, so that will need summing data for the previous year). Additionally, we will be checking the current month before executing the CTE. I am leaving this exercise to the reader.

I hope this simple article serves its purpose and helps some developers. I would love to hear your feedback.