Ordering a monthly and quarterly query

Question

I want to start off by giving a tremendous thanks to everyone who participates in this forum. The plethora of knowledge and examples has been an amazing resource at 3 am the day before [INSERT PROJECT NAME HERE] was due and I arrived at a wall...lol.

I've created a simple query to run as a regular report of data broken out by month and quarter, and it pulls the correct values. However i'm having trouble with ordering. This is what I would like the query results to return:

Month   DM          TM          FI          GR          NM          Total
JAN     0           0           50000       0           0           50000
FEB     0           0           535000      0           620043.77   1255043.77
MAR     0           0           614146      5611        3016237.91  3637788.91
Q1      0           0           1199146     5611        3636281.68  4942832.68
APR     0           0           1230059     6395        1278525.79  2578589.79
MAY     0           45796       101160      15473.18    1357877.7   1932405.88
JUN     213716.34   141050      746302.28   25007.68    2552277.35  3963233.35
Q2      213716.34   186846      2077521.28  46875.86    5188680.84  8474229.02
JUL     258362.92   49541       359178.5    19674.33    803987.99   1928917.74
AUG     62750.35    9159        151561.28   15020.88    1248746.28  1533091.93
SEP     262246.37   136064      198365.99   37397.73    3271355.71  4017822.94
Q3      583359.64   194764      709105.77   72092.94    5324089.98  7479832.61
OCT     198565.1    189847      626951.11   29517       978947.85   2048681.2
NOV     22391       53652       155140.84   11444.75    470482.95   714747.68
Q4      220956.1    243499      782091.95   40961.75    1449430.8   2763428.88

However, this is where I get stuck:

Month   DM          TM          FI          GR          NM          Total
APR     0.00        0.00        1230059.00  6395.00     1275636.61  2575700.61
AUG     62750.35    9159.00     151561.28   15020.88    1248286.28  1532631.93
FEB     0.00        0.00        535000.00   0.00        587337.00   1222337.00
JAN     0.00        0.00        50000.00    0.00        0.00        50000.00
JUL     258362.92   49541.00    359178.50   19674.33    803417.96   1933347.71
JUN     213716.34   141050.00   746302.28   25007.68    2551383.23  3962339.23
MAR     0.00        0.00        614146.00   5611.00     2920193.41  3541744.41
MAY     0.00        45796.00    101160.00   15473.18    1356653.55  1931181.73
NOV     26213.00    61683.00    130270.90   13099.75    496460.05   729362.84
OCT     198565.10   189847.00   626951.11   29517.00    978797.85   2043531.20
Q1      0.00        0.00        1199146.00  5611.00     3507530.41  4814081.41
Q2      213716.34   186846.00   2077521.28  46875.86    5183673.39  8469221.57
Q3      583359.64   194764.00   709105.77   72092.94    5322894.95  7483637.58
Q4      224778.10   251530.00   757222.01   42616.75    1475257.90  2772894.04
SEP     262246.37   136064.00   198365.99   37397.73    3271190.71  4017657.94

Here is the Query that gets me the alphabetized results:

SELECT 
CASE DATEPART(mm, dtDate) 
WHEN 01 THEN 'JAN' WHEN 02 THEN 'FEB' WHEN 03 THEN 'MAR'
WHEN 04 THEN 'APR' WHEN 05 THEN 'MAY' WHEN 06 THEN 'JUN'
WHEN 07 THEN 'JUL' WHEN 08 THEN 'AUG' WHEN 09 THEN 'SEP'
WHEN 10 THEN 'OCT' WHEN 11 THEN 'NOV' WHEN 12 THEN 'DEC'
END 
AS 'Month', 

SUM(CASE WHEN sAccount = 'DM' AND sChannel = 'DM' THEN mAmount 
    ELSE 0 END) AS 'DM',    
SUM(CASE WHEN sAccount = 'DM' AND sChannel = 'TM' THEN mAmount 
    ELSE 0 END) AS 'TM',        
SUM(CASE WHEN sAccount = 'FI' AND sChannel = 'FI' THEN mAmount 
    ELSE 0 END) AS 'FI',    
SUM(CASE WHEN sAccount = 'FI' AND sChannel = 'GR' THEN mAmount 
    ELSE 0 END) AS 'GR',    
SUM(CASE WHEN sAccount = 'NM' THEN mAmount 
    ELSE 0 END) AS 'NM',    
SUM(mAmount) AS 'Total' 

FROM Contrib
WHERE iDeleted = '0'

GROUP BY DATEPART(mm, dtDate)

UNION

SELECT 
CASE DATEPART(qq, dtDate) 
WHEN 01 THEN 'Q1'
WHEN 02 THEN 'Q2'
WHEN 03 THEN 'Q3'
WHEN 04 THEN 'Q4'
END 
AS 'Month', 

SUM(CASE WHEN sAccount = 'DM' AND sChannel = 'DM' THEN mAmount 
    ELSE 0 END) AS 'DM',    
SUM(CASE WHEN sAccount = 'DM' AND sChannel = 'TM' THEN mAmount 
    ELSE 0 END) AS 'M', 
SUM(CASE WHEN sAccount = 'FI' AND sChannel = 'FI' THEN mAmount 
    ELSE 0 END) AS 'FI',
SUM(CASE WHEN sAccount = 'FI' AND sChannel = 'GR' THEN mAmount 
    ELSE 0 END) AS 'GR',
SUM(CASE WHEN sAccount = 'NM' THEN mAmount 
    ELSE 0 END) AS 'NM',    
SUM(mAmount) AS 'Total' 

FROM Contrib
WHERE iDeleted = '0'

GROUP BY DATEPART(qq, dtDate)

I've tried a number of ways to add an order by clause into the query, but have had no success (usually getting Msg 104, Level 16... "ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator"). I suspect that my issue is due to the union and 2 different datepart functions (month vs quarter).

I'm still pretty green, so please let me know if there is a more efficient way of organizing this query or if you think you know where I might be running into my snag. .

Many thanks!

Pat

@abhi well, for one, [never use ORDER BY ](https://sqlblog.org/blogs/aaron_bertrand/archive/2009/10/06/bad-habits-to-kick-order-by-ordinal.aspx). Second, how will this help? The first column is the month name. I don't think the goal is to order by APR, AUG, FEB... — Aaron Bertrand, Nov 20 '13 at 20:00

score 1 · Answer 1 · answered Nov 20 '13 at 20:29

I think you'll need to add another column to the select, and use it for ordering ... something like this, maybe ?

SELECT 
CASE DATEPART(mm, dtDate) 
WHEN 01 THEN '01' WHEN 02 THEN '02' WHEN 03 THEN '04'
WHEN 04 THEN '05' WHEN 05 THEN '06' WHEN 06 THEN '07'
WHEN 07 THEN '09' WHEN 08 THEN '10' WHEN 09 THEN '11'
WHEN 10 THEN '13' WHEN 11 THEN '14' WHEN 12 THEN '15'
END 
AS 'Order', 
CASE DATEPART(mm, dtDate) 
WHEN 01 THEN 'JAN' WHEN 02 THEN 'FEB' WHEN 03 THEN 'MAR'
WHEN 04 THEN 'APR' WHEN 05 THEN 'MAY' WHEN 06 THEN 'JUN'
WHEN 07 THEN 'JUL' WHEN 08 THEN 'AUG' WHEN 09 THEN 'SEP'
WHEN 10 THEN 'OCT' WHEN 11 THEN 'NOV' WHEN 12 THEN 'DEC'
END 
AS 'Month', 

SUM(CASE WHEN sAccount = 'DM' AND sChannel = 'DM' THEN mAmount 
    ELSE 0 END) AS 'DM',    
SUM(CASE WHEN sAccount = 'DM' AND sChannel = 'TM' THEN mAmount 
    ELSE 0 END) AS 'TM',        
SUM(CASE WHEN sAccount = 'FI' AND sChannel = 'FI' THEN mAmount 
    ELSE 0 END) AS 'FI',    
SUM(CASE WHEN sAccount = 'FI' AND sChannel = 'GR' THEN mAmount 
    ELSE 0 END) AS 'GR',    
SUM(CASE WHEN sAccount = 'NM' THEN mAmount 
    ELSE 0 END) AS 'NM',    
SUM(mAmount) AS 'Total' 

FROM Contrib
WHERE iDeleted = '0'

GROUP BY DATEPART(mm, dtDate)

UNION

SELECT 
CASE DATEPART(qq, dtDate) 
WHEN 01 THEN '05'
WHEN 02 THEN '08'
WHEN 03 THEN '12'
WHEN 04 THEN '16'
END 
AS 'Order', 

CASE DATEPART(qq, dtDate) 
WHEN 01 THEN 'Q1'
WHEN 02 THEN 'Q2'
WHEN 03 THEN 'Q3'
WHEN 04 THEN 'Q4'
END 
AS 'Month', 

SUM(CASE WHEN sAccount = 'DM' AND sChannel = 'DM' THEN mAmount 
    ELSE 0 END) AS 'DM',    
SUM(CASE WHEN sAccount = 'DM' AND sChannel = 'TM' THEN mAmount 
    ELSE 0 END) AS 'M', 
SUM(CASE WHEN sAccount = 'FI' AND sChannel = 'FI' THEN mAmount 
    ELSE 0 END) AS 'FI',
SUM(CASE WHEN sAccount = 'FI' AND sChannel = 'GR' THEN mAmount 
    ELSE 0 END) AS 'GR',
SUM(CASE WHEN sAccount = 'NM' THEN mAmount 
    ELSE 0 END) AS 'NM',    
SUM(mAmount) AS 'Total' 

FROM Contrib
WHERE iDeleted = '0'

GROUP BY DATEPART(qq, dtDate)

ORDER BY 'Order'

score 0 · Answer 2 · answered Nov 20 '13 at 21:07

Using AdventureWorks 2012 I came up with this solution using ROLLUP

SELECT 
    [Month] = CASE WHEN GROUPING(DATEPART(MM, OrderDate)) = 1 THEN 'Q' + CAST(DATEPART(QQ, OrderDate) AS VARCHAR(2))
                   ELSE CAST(DATEPART(MM, OrderDate) AS VARCHAR(4)) END,
    SUM(TotalDue) [TotalDue]
FROM AdventureWorks.Sales.SalesOrderHeader
GROUP BY DATEPART(YY, OrderDate), DATEPART(QQ, OrderDate), DATEPART(MM, OrderDate) WITH ROLLUP
HAVING GROUPING(DATEPART(QQ, OrderDate))=0
ORDER BY MAX(OrderDate)

Adapting it for your query

SELECT 
  CASE WHEN GROUPING(DATEPART(MM, dtDate)) = 1 THEN 'Q' + CAST(DATEPART(QQ, dtDate) AS VARCHAR(2))
       ELSE STUFF(CAST(DATEPART(MM, dtDate) AS VARCHAR(4)), 1, 0, REPLICATE('0', 2 - LEN(CAST(DATEPART(MM, dtDate) AS VARCHAR(4))))) END,
  SUM(CASE WHEN sAccount = 'DM' AND sChannel = 'DM' THEN mAmount 
           ELSE 0 END) AS 'DM',    
  SUM(CASE WHEN sAccount = 'DM' AND sChannel = 'TM' THEN mAmount 
    ELSE 0 END) AS 'TM',        
  SUM(CASE WHEN sAccount = 'FI' AND sChannel = 'FI' THEN mAmount 
           ELSE 0 END) AS 'FI',    
  SUM(CASE WHEN sAccount = 'FI' AND sChannel = 'GR' THEN mAmount 
           ELSE 0 END) AS 'GR',    
  SUM(CASE WHEN sAccount = 'NM' THEN mAmount 
           ELSE 0 END) AS 'NM',    
  SUM(mAmount) AS 'Total' 
FROM Contrib
WHERE iDeleted = '0'
GROUP BY DATEPART(YY, dtDate), DATEPART(QQ, dtDate), DATEPART(MM, dtDate) WITH ROLLUP
HAVING GROUPING(DATEPART(QQ, dtDate))=0
ORDER BY MAX(dtDate)

Ordering a monthly and quarterly query

2 Answers2