| Author |
 Topic  |
|
|
edyl
Starting Member
32 Posts |
 Posted - 02/27/2013 : 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
Flowing Fount of Yak Knowledge
1530 Posts |
Posted - 02/27/2013 : 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
|
 |
|
|
edyl
Starting Member
32 Posts |
Posted - 02/27/2013 : 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. |
|
|
|
James K
Flowing Fount of Yak Knowledge
1530 Posts |
Posted - 02/27/2013 : 19:05:04
|
| Ah... What I posted works only on SQL 2012. What version of SQL Server are you on? |
|
|
|
James K
Flowing Fount of Yak Knowledge
1530 Posts |
Posted - 02/27/2013 : 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; |
|
|
| |
Topic |
|
|
|
Cumulative Sum for Fiscal Year to Date values
