Order By - Why so complicated? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

SQL Server 2000 Forums

Transact-SQL (2000)

Order By - Why so complicated?

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources