sql-server - 对 SQL Server 结果进行排序

Question

我正在尝试排序以显示最早的month_year第一个，但在每个时间段结束时仍然有空的总显示。当我没有总数时，这有效，但现在无效，因为 null 位于顶部

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)) 

标签： sql-serversorting

Answer 1

不是您正在寻找的答案，但您可以将整个 case 语句替换为：UPPER(FORMAT(SERVICE_DATE, 'MMM_yyyy'))