CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Ice
Ice

Reputation: 459

sql server 2008 order by fiscal year

I want to order by fiscal year using the below sql on SQl Server, but I got an error message 'Invalid column name 'MONTH''. I wonder if you can help me modify the below sql. Thanks!

SELECT 'Closed Home' AS STATUS,
    right(CONVERT(VARCHAR(8), Closed_Dt, 3),5) as "MONTH",
    COUNT(Home_ID) COUNT
FROM Home
GROUP BY HOME_STATUS,right(CONVERT(VARCHAR(8), Closed_Dt, 3),5)
HAVING Facility_STATUS IN ('CLOSED')

UNION

SELECT 'New Placement' AS STATUS,
right(CONVERT(VARCHAR(8), EFFECT_DT, 3),5) AS "MONTH",
COUNT(CLT_NBR) AS COUNT 
FROM STATUS S
GROUP BY right(CONVERT(VARCHAR(8), EFFECT_DT, 3),5)     
ORDER BY status,
  case "MONTH" when '07/15' then 1 
               when '08/15' then 2
               when '09/15' then 3
               when '10/15' then 4
               when '11/15' then 5
               when '12/15' then 6
               when '01/16' then 7
               when '02/16' then 8
               when '03/16' then 9
               when '04/16' then 10
               when '05/16' then 11
               when '06/16' then 12
    end

Upvotes: 1

Views: 157

Answers (3)

JamieD77
JamieD77

Reputation: 13949

you can just move your main query into a derived table and use the MONTH column then.. 

SELECT * FROM (
    SELECT  'Closed Home' AS STATUS,
            RIGHT(CONVERT(VARCHAR(8),Closed_Dt,3),5) AS "MONTH",
            COUNT(Home_ID) COUNT
    FROM    Home
    GROUP BY HOME_STATUS,
            RIGHT(CONVERT(VARCHAR(8),Closed_Dt,3),5)
    HAVING  Facility_STATUS IN ('CLOSED')
    UNION
    SELECT  'New Placement' AS STATUS,
            RIGHT(CONVERT(VARCHAR(8),EFFECT_DT,3),5) AS "MONTH",
            COUNT(CLT_NBR) AS COUNT
    FROM    STATUS S
    GROUP BY RIGHT(CONVERT(VARCHAR(8),EFFECT_DT,3),5)
) t
ORDER BY status,
        CASE "MONTH"
          WHEN '07/15' THEN 1
          WHEN '08/15' THEN 2
          WHEN '09/15' THEN 3
          WHEN '10/15' THEN 4
          WHEN '11/15' THEN 5
          WHEN '12/15' THEN 6
          WHEN '01/16' THEN 7
          WHEN '02/16' THEN 8
          WHEN '03/16' THEN 9
          WHEN '04/16' THEN 10
          WHEN '05/16' THEN 11
          WHEN '06/16' THEN 12
        END

Upvotes: 2

Tab Alleman
Tab Alleman

Reputation: 31785

You are getting the error because you cannot reference an alias in a CASE expression, even though it's in the ORDER BY clause.

This should work:

...
case right(CONVERT(VARCHAR(8), Closed_Dt, 3),5)
...

Upvotes: 0

Cato
Cato

Reputation: 3701

    SELECT 'Closed Home' AS STATUS,
        right(CONVERT(VARCHAR(8), Closed_Dt, 3),5) as "MONTH",
        COUNT(Home_ID) COUNT
    FROM Home
    GROUP BY HOME_STATUS,right(CONVERT(VARCHAR(8), Closed_Dt, 3),5)
    HAVING Facility_STATUS IN ('CLOSED')

    UNION

    SELECT 'New Placement' AS STATUS,
    right(CONVERT(VARCHAR(8), EFFECT_DT, 3),5) AS "MONTH",
    COUNT(CLT_NBR) AS COUNT 
    FROM STATUS S
    GROUP BY right(CONVERT(VARCHAR(8), EFFECT_DT, 3),5)     
    ORDER BY status,
      case right(CONVERT(VARCHAR(8), EFFECT_DT, 3),5)  when '07/15' then 1 
                   when '08/15' then 2
                   when '09/15' then 3
                   when '10/15' then 4
                   when '11/15' then 5
                   when '12/15' then 6
                   when '01/16' then 7
                   when '02/16' then 8
                   when '03/16' then 9
                   when '04/16' then 10
                   when '05/16' then 11
                   when '06/16' then 12
        end

Upvotes: 0

Related Questions