Creating a YTD record count column

[PRUSA]

Hi Everyone,

I am working on a request here and I cant seem to get handle on it.

I am create a query that shows both MTD figures but also a YTD column.

Here is my query.

select
         case when Month(a.event_date)=1  then 'January'
	      when Month(a.event_date)=2  then 'February'
	      when Month(a.event_date)=3  then 'March'
	      when Month(a.event_date)=4  then 'April'
	      when Month(a.event_date)=5  then 'May'
	      when Month(a.event_date)=6  then 'June'
	      when Month(a.event_date)=7  then 'July'
	      when Month(a.event_date)=8  then 'August'
	      when Month(a.event_date)=9  then 'September'
              when Month(a.event_date)=10 then 'October'
	      when Month(a.event_date)=11 then 'November'
	      when Month(a.event_date)=12 then 'December'
	      else ''	end	as calendar_month_name
	,sum(case 
	     when Month(a.event_date) = 1 then 1
	     when Month(a.event_date) = 2 then 1
	     when Month(a.event_date) = 3 then 1
	     when Month(a.event_date) = 4 then 1
	     when Month(a.event_date) = 5 then 1
	     when Month(a.event_date) = 6 then 1
	     when Month(a.event_date) = 7 then 1
	     when Month(a.event_date) = 8 then 1
	     when Month(a.event_date) = 9 then 1
	     when Month(a.event_date) = 10 then 1
	     when Month(a.event_date) = 11 then 1
	     when Month(a.event_date) = 12 then 1
	     else ''end ) as MTD

As I said The months come out fine the MTD column is what i want, but now I need a YTD column that will keep a running YTD number by month for example

Month MTD YTD
Jan 200 200
Feb 100 300
Mar 300 600

And then i have to figure out how to order the result by month.

I dont know if this is the best way to run this query but its the only way i could figure it out.

and help woudl be much appreciated.

-Sergio

 

[RiverGuy]

More information is needed.

Firstly, what does MTD mean to you? To me, it would mean that if I wanted to run a report with a date of June 25, I would want the totals for June 1 - June 25, May 1 - May 25, April 1 - April 25, etc. Is this what you are looking to do, or are you simply wanting to group by each month to include all existing days in the month?

Secondly, are you sure you want a running total for YTD? If I wanted to run a report from June through December, I would want the YTD for June to include January through June.

 

[PRUSA]

Sorry I wasnt detailed

MTD = Month to date
I am calculating the number of records that come back for each month.

For example from the query above this is what my output looks like:

Month    MTD
January  671
February 664
March    604
April    599
May      559

and yes I want to keep a running YTD total, thats whats being asked for whatever reason

so output should look like this.

Month    MTD  YTD
January  671  671
February 664  1335
March    604  1939
April    599  2538
May      559  3097

Let me know if that makes more sense.

 

[RiverGuy]

OK, normally, you would want to do running totals in the front-end reporting tool, however that is not always an option if you're not using a reporting tool. That being said, there are various ways in which you could do a running total in T-SQL. Here is one method which utilizes a temporary table. You can put your result set with Month and MTD into the temporary table, and then use a subquery against it to return your running total. For example:

SELECT 
MONTH(TheDate) AS TheMonth, 
YEAR(TheDate) AS TheYear,
SUM(TheAmount) AS MTD
INTO #Temp1
FROM @Table1
GROUP BY MONTH(TheDate), YEAR(TheDate)

SELECT a.*,
(SELECT SUM(MTD) FROM #Temp1 b WHERE TheMonth <= a.TheMonth AND TheYear <= b.TheYear) AS YTD
FROM #Temp1 a

DROP TABLE #Temp1