Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Cumulative Sum for Fiscal Year to Date values

Author  Topic 

edyl
Starting Member

35 Posts

Posted - 2013-02-27 : 15:31:54
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-27 : 16:32:48
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

35 Posts

Posted - 2013-02-27 : 18:49:05
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
Master Smack Fu Yak Hacker

3873 Posts

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

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-27 : 19:17:41
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
   

- Advertisement -