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...FiscalYear10.........2011......Jun........FY1121.........2011......Jun........FY1113.........2011......Jul........FY1243.........2011......Jul........FY1235.........2011......Jul........FY1261.........2011......Aug........FY1237.........2011......Sep........FY1289.........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...CountOrdersFYTD2011......Jun........FY11.........2................22011......Jul........FY12.........3................32011......Aug........FY12.........1................42011......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.MnthNumFROM #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.CalMonthORDER BY b.MnthNum; DROP TABLE #tmp;----------- RESULTS-----------------CalYear CalMonth FiscalYear CountOrdersYTD CountOrdersMTD MnthNum2011 Jun FY11 2 2 62011 Jul FY12 3 3 72011 Aug FY12 4 1 82011 Sep FY12 5 1 92011 Oct FY12 6 1 10 |
|
|
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 11Incorrect 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
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? |
|
|
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.MnthNumFROM #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 ) cORDER BY b.MnthNum; |
|
|
|
|
|