SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Cumulative Sum for Fiscal Year to Date values
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

edyl
Starting Member

33 Posts

Posted - 02/27/2013 :  15:31:54  Show Profile  Reply with Quote
Hello Everyone,

I am trying to come up with a SQL query to create aggregate counts for Month to Date and Fiscal Year to Date values. For example, lets say I have the following table. Our Fiscal Year Ends in June and starts in July.


OrderID...CalYear...CalMonth...FiscalYear
10.........2011......Jun........FY11
21.........2011......Jun........FY11
13.........2011......Jul........FY12
43.........2011......Jul........FY12
35.........2011......Jul........FY12
61.........2011......Aug........FY12
37.........2011......Sep........FY12
89.........2011......Oct........FY12


Following is the result set I want to get. Please note that going from June to July the CountOrdersFYTD are not cumulative becasue they are for differen fiscal years. But if they are for the same Fiscal Year, the values are a cumulative sum of the previous month. I want to create a SQL query that I can use to insert values in a new table. The query will run every day to make the insertions daily as the new values comes into the original table.


CalYear...CalMonth...FiscalYear...CountOrdersMTD...CountOrdersFYTD
2011......Jun........FY11.........2................2
2011......Jul........FY12.........3................3
2011......Aug........FY12.........1................4
2011......Sep........FY12.........2................6


Any insights or recomendations greatly appreciated.

Thanks in Advance.

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 02/27/2013 :  16:32:48  Show Profile  Reply with Quote
The hardest part about this is that the months are using English month names, so it is hard to order them. So I created a mapping between month number and month name in the following. Also, the results do not seem to match up with what you showed. In your sample input, I see one row for each of Aug, Sep, and Oct, but in the results, Sep has count = 2 and Oct has none.
CREATE TABLE #tmp(OrderId INT, CalYear int, CalMonth VARCHAR(32), FiscalYear VARCHAR(32));
INSERT INTO #tmp VALUES 
('10','2011','Jun','FY11'),
('21','2011','Jun','FY11'),
('13','2011','Jul','FY12'),
('43','2011','Jul','FY12'),
('35','2011','Jul','FY12'),
('61','2011','Aug','FY12'),
('37','2011','Sep','FY12'),
('89','2011','Oct','FY12');

SELECT DISTINCT 
	a.CalYear,
	a.CalMonth,
	a.FiscalYear,
	COUNT(*) OVER(PARTITION BY a.FiscalYear ORDER BY b.MnthNum) AS CountOrdersYTD,
	COUNT(*) OVER(PARTITION BY a.FiscalYear, b.MnthNum ORDER BY b.MnthNum) AS CountOrdersMTD,
	b.MnthNum
FROM
	#tmp a
	INNER JOIN 
	(
	VALUES 
		(1 , 'Jan' ) ,
		(2 , 'Feb' ) ,
		(3 , 'Mar' ) ,
		(4 , 'Apr' ) ,
		(5 , 'May' ) ,
		(6 , 'Jun' ) ,
		(7 , 'Jul' ) ,
		(8 , 'Aug' ) ,
		(9 , 'Sep' ) ,
		(10, 'Oct' ) ,
		(11, 'Nov' ) ,
		(12, 'Dec' ) 
	) b(MnthNum, MnthName) ON b.MnthName = a.CalMonth
ORDER BY 
	b.MnthNum;
	
DROP TABLE #tmp;

----------- RESULTS-----------------
CalYear	CalMonth	FiscalYear	CountOrdersYTD	CountOrdersMTD	MnthNum
2011	Jun	FY11	2	2	6
2011	Jul	FY12	3	3	7
2011	Aug	FY12	4	1	8
2011	Sep	FY12	5	1	9
2011	Oct	FY12	6	1	10
Go to Top of Page

edyl
Starting Member

33 Posts

Posted - 02/27/2013 :  18:49:05  Show Profile  Reply with Quote
Hi James,

I tried a similar query in my actual tables but I get this error:

Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'ORDER'.

I typed in exact same syntax, but of course different column names. I had changed the names of the columns for simplicity.

Thanks.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 02/27/2013 :  19:05:04  Show Profile  Reply with Quote
Ah... What I posted works only on SQL 2012. What version of SQL Server are you on?
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3661 Posts

Posted - 02/27/2013 :  19:17:41  Show Profile  Reply with Quote
Can you give this a try? It should work in SQL 2008 or later:
;WITH cte AS 
(
	SELECT * FROM 
	(
		VALUES 
		(1 , 'Jan' ) ,
		(2 , 'Feb' ) ,
		(3 , 'Mar' ) ,
		(4 , 'Apr' ) ,
		(5 , 'May' ) ,
		(6 , 'Jun' ) ,
		(7 , 'Jul' ) ,
		(8 , 'Aug' ) ,
		(9 , 'Sep' ) ,
		(10, 'Oct' ) ,
		(11, 'Nov' ) ,
		(12, 'Dec' ) 
	) b(MnthNum, MnthName) 
)
SELECT DISTINCT 
	a.CalYear,
	a.CalMonth,
	a.FiscalYear,
	c.CountOrdersYTD,
	COUNT(*) OVER (PARTITION BY a.FiscalYear,a.CalMonth) AS CountOrdersMTD,
	b.MnthNum
FROM
	#tmp a
	INNER JOIN cte b ON b.MnthName = a.CalMonth
	CROSS APPLY
	(
		SELECT
			COUNT(*) AS CountOrdersYTD
		FROM
			#tmp c
			INNER JOIN cte d ON d.MnthName = c.CalMonth
		WHERE
			d.MnthNum <= b.MnthNum AND c.FiscalYear=a.FiscalYear
	) c
ORDER BY 
	b.MnthNum;	
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000