Q:

Sorting SQL Server results

I am trying to sort to show the earliest month_year first but still have the null total show at the end of each time period. When I didn't have the totals this worked but doesn't now since the null goes to the top
WITH TEST AS
(
    SELECT 
        PARENT,
        NEW_SWPA_NUM,
        SWPA_DESCRIPTION,
        CASE 
           WHEN SERVICE_DATE BETWEEN '2018-07-01' AND '2018-07-31' 
              THEN 'JUL_2018'
           WHEN SERVICE_DATE BETWEEN '2018-08-01' AND '2018-08-31' 
              THEN 'AUG_2018'
           WHEN SERVICE_DATE BETWEEN '2018-09-01' AND '2018-09-30' 
              THEN 'SEP_2018'
           WHEN SERVICE_DATE BETWEEN '2018-10-01' AND '2018-10-31' 
              THEN 'OCT_2018'
           WHEN SERVICE_DATE BETWEEN '2018-11-01' AND '2018-11-30' 
              THEN 'NOV_2018'
           WHEN SERVICE_DATE BETWEEN '2018-12-01' AND '2018-12-31' 
              THEN 'DEC_2018'
           WHEN SERVICE_DATE BETWEEN '2019-01-01' AND '2019-01-31' 
              THEN 'JAN_2019'
           WHEN SERVICE_DATE BETWEEN '2019-02-01' AND '2019-02-28' 
              THEN 'FEB_2019'
           WHEN SERVICE_DATE BETWEEN '2019-03-01' AND '2019-03-31' 
              THEN 'MAR_2019'
           WHEN SERVICE_DATE BETWEEN '2019-04-01' AND '2019-04-30' 
              THEN 'APR_2019'
           WHEN SERVICE_DATE BETWEEN '2019-05-01' AND '2019-05-31' 
              THEN 'MAY_2019'
           WHEN SERVICE_DATE BETWEEN '2019-06-01' AND '2019-06-30' 
              THEN 'JUN_2019' 
        END AS MONTH_YEAR,
        SUM(TOPAY) AS PAID
    FROM 
        DE_IDENTIFIED.DBO.DE_IDENTIFIED_CLAIMS
    WHERE 
        TOPAY <> 0
        AND VOID_DATE IS NULL
        AND SERVICE_DATE BETWEEN '2018-07-01' AND '2019-06-30'
        AND COUNTY IN ('20','43','61')
    GROUP BY 
        ROLLUP(PARENT, NEW_SWPA_NUM, SWPA_DESCRIPTION,
               CASE WHEN SERVICE_DATE BETWEEN '2018-07-01' AND '2018-07-31' THEN 'JUL_2018'
      WHEN SERVICE_DATE BETWEEN '2018-08-01' AND '2018-08-31' THEN 'AUG_2018'
      WHEN SERVICE_DATE BETWEEN '2018-09-01' AND '2018-09-30' THEN 'SEP_2018'
      WHEN SERVICE_DATE BETWEEN '2018-10-01' AND '2018-10-31' THEN 'OCT_2018'
      WHEN SERVICE_DATE BETWEEN '2018-11-01' AND '2018-11-30' THEN 'NOV_2018'
      WHEN SERVICE_DATE BETWEEN '2018-12-01' AND '2018-12-31' THEN 'DEC_2018'
      WHEN SERVICE_DATE BETWEEN '2019-01-01' AND '2019-01-31' THEN 'JAN_2019'
      WHEN SERVICE_DATE BETWEEN '2019-02-01' AND '2019-02-28' THEN 'FEB_2019'
      WHEN SERVICE_DATE BETWEEN '2019-03-01' AND '2019-03-31' THEN 'MAR_2019'
      WHEN SERVICE_DATE BETWEEN '2019-04-01' AND '2019-04-30' THEN 'APR_2019'
      WHEN SERVICE_DATE BETWEEN '2019-05-01' AND '2019-05-31' THEN 'MAY_2019'
      WHEN SERVICE_DATE BETWEEN '2019-06-01' AND '2019-06-30' THEN 'JUN_2019' END)  
)
SELECT *
FROM TEST
WHERE SWPA_DESCRIPTION IS NOT NULL
ORDER BY 
    NEW_SWPA_NUM, SUBSTRING(MONTH_YEAR, 5, 4),
    DATEPART(MM, CAST(SUBSTRING(MONTH_YEAR, 1, 3) + '1900' AS DATETIME)) 

A:

You could add an additional predicate to your sort logic. Something like this. 
ORDER BY NEW_SWPA_NUM
    , CASE when MONTH_YEAR is null then 2 else 1 end
    , SUBSTRING(MONTH_YEAR,5,4)
    , DATEPART(MM,CAST(SUBSTRING(MONTH_YEAR, 1,3) + '1900' AS DATETIME)) 

But to be honest I would make this a bit simpler like this. There is no need to sort by your strings.
ORDER BY NEW_SWPA_NUM
    , CASE when MONTH_YEAR is null then 2 else 1 end
    , SERVICE_DATE 

