Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

How to get monthly YTD data

By Kautuk,

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.

Total article views: 11222 | Views in the last 30 days: 4
 
Related Articles
FORUM

Select all months bewtween two dates

Select all months bewtween two dates

FORUM

Get Monthly total in sp

Get Monthly total in sp

BLOG

July 2013 – Monthly SQL Server Checklist

First Monday of the month and time to update the monthly SQL Server checklist.  Monthly SQL Server...

FORUM

select the last month in SQL server 2005

select the last month in SQL server 2005

BLOG

July 2014 – SQL Server Monthly Maintenance Checklist

image source It’s been a few months since I’ve updated my SQL Server monthly maintenance checklist....

Tags
aggregates    
date manipulation    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones