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.
| Author |
Topic |
|
Vergy39
Starting Member
15 Posts |
 Posted - 2013-12-24 : 10:10:21
|
I am trying to create a report that shows a number of items by fiscal year and month. The data is correct, but I cannot get the order by correct. The grand total should be at the bottom of the report and the total for each year should be at the bottome of each year, but my query has them at the top. Here is the query:
"Select --Grouping(siFiscalYear), Grouping(m.vcMonthname),
Case When Grouping(siFiscalyear) = 1 Then '' Else siFiscalYear End AS FiscalYear,
Case When Grouping(m.vcMonthname) = 1 Then 'Total' Else m.vcMonthName End AS FiscalMonth,
Sum(Case When Division <> 72 Then 1 Else 0 End) As AZ,
Sum(Case When Division = 72 Then 1 Else 0 End) As CAL
From dbo.DailyQueues q
Inner join fwUtility..tblFiscalCalendar c ON c.sdtDate = q.RptDate
Inner join fwUtility..tblMonths m ON m.tiMonthNum = tiFiscalPeriodFK
Where Que = 279
AND RptDate Between '2012-10-02 00:00' AND '2014-01-04 23:59'
Group By siFiscalYear, m.vcMonthName, tiFiscalPeriodFK With Rollup
Having (Grouping(c.siFiscalYear) = 0 AND Grouping(c.tiFiscalPeriodFK) = 0 AND Grouping (m.vcMonthName) = 0)
OR (Grouping(c.siFiscalYear) = 0 AND Grouping(m.vcMonthName) = 1)
OR (Grouping(m.vcMonthName) = 1)
Order By si.FiscalYear, tiFiscalPeriodFK"
Here is the results:
Year Month AZ CAL
0 Total 100332 1517
2012 Total 23612 353
2012 September 5289 69
2012 October 4995 78
2012 November 5116 117
2012 December 8212 89
2013 Total 76720 1164
2013 January 6101 99
2013 February 7178 104
2013 March 6220 68
2013 April 6469 65
2013 May 8209 93
2013 June 6867 72
2013 July 7726 74
2013 August 8554 123
2013 September 6804 118
2013 October 7327 180
2013 November 5265 168
It should look like:
Year Month AZ CAL
2012 September 5289 69
2012 October 4995 78
2012 November 5116 117
2012 December 8212 89
2012 Total 23612 353
2013 January 6101 99
2013 February 7178 104
2013 March 6220 68
2013 April 6469 65
2013 May 8209 93
2013 June 6867 72
2013 July 7726 74
2013 August 8554 123
2013 September 6804 118
2013 October 7327 180
2013 November 5265 168
0 Total 100332 1517
What is wrong with the Order by Statement? Any assistance is greatly appreciated.
Also, Merry Christmas!!
David V |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-24 : 12:18:57
|
make order by like
..
Order By case when Grouping(siFiscalyear) = 1 then 1 else 0 end, si.FiscalYear,case when Grouping(m.vcMonthName) = 1 then 1 else 0 end, tiFiscalPeriodFK
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
 |
|
|
Vergy39
Starting Member
15 Posts |
Posted - 2013-12-24 : 12:50:54
|
Thanks visakh16. That worked perfectly. I did try the grouping(siFiscalYear) but used 0 instead of 1 since 0 is before 1 that would come first. Anyway, thanks for taking the time to assist me.
David V |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-24 : 13:34:33
|
No problem
you're welcome 
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
|
|
|
|
Order By - Why so complicated?