SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Order By - Why so complicated?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Vergy39
Starting Member

USA
15 Posts

Posted - 12/24/2013 :  10:10:21  Show Profile  Reply with Quote
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

Edited by - Vergy39 on 12/24/2013 10:13:36

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/24/2013 :  12:18:57  Show Profile  Reply with Quote
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
Go to Top of Page

Vergy39
Starting Member

USA
15 Posts

Posted - 12/24/2013 :  12:50:54  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 12/24/2013 :  13:34:33  Show Profile  Reply with Quote
No problem
you're welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000