sql - 将 CASE 语句与 ROLL UP 函数一起使用

Question

我将几个 DischargeType 值归为一个名为“上诉”的类别。这工作正常，但聚合字段 MIN(DateReceived) 导致结果为每个 Appeals 类型生成 1 DateReceived 值，而我只想要一个用于整个 Appeals 类别的值。我以为我可以在 GROUP BY 子句中添加相同的 CASE 语句，但我不知道如何使用 ROLL UP 函数执行此操作。最后一行中的 NULL 值是总计行。谢谢

<PRE>
CREATE PROCEDURE [dbo].[p_ReportMonthlySAEProcessing] 
@StartDate smalldatetime = NULL,
@EndDate smalldatetime = NULL
AS
BEGIN
    SELECT
    CASE  
        WHEN DischargeType = 'dqs' THEN 'Disqualifying Status'
        WHEN DischargeType = 'fraud' THEN 'Fraud'
        WHEN DischargeType = 'id theft' THEN 'ID Theft'
        WHEN DischargeType = 'unenforceable' THEN 'Unenforceable'
        WHEN DischargeType = 'unp' THEN 'Unpaid Refund'
        WHEN DischargeType = 'uns' THEN 'Unauthorized Signature/Payment'
        WHEN DischargeType IN ('atb appeal', 'cls appeal','dqs appeal','id 
        theft appeal','unp appeal','uns appeal') THEN 'Appeals'            
    END AS DischargeType 
   ,SUM(CASE WHEN (DateReceived > @StartDate AND DateReceived < DATEADD(dd, 
1, @EndDate)) THEN 1 ELSE 0 END) AS Claims_Received
   ,SUM(CASE WHEN (DateCompleted > @StartDate AND DateCompleted < 
DATEADD(dd, 1, @EndDate)) THEN 1 ELSE 0 END) AS Claims_Completed
   ,SUM(CASE WHEN DateCompleted IS NULL THEN 1 ELSE 0 END) AS Claims_Pending
   ,MIN(DateReceived) AS [Oldest_Claim]     
FROM 
Claims
GROUP BY 
    DischargeType WITH ROLLUP      
ORDER BY 
    CASE WHEN DischargeType IS NULL THEN 1 ELSE 0 END, DischargeType</pre>

当前结果：

期望的结果：

标签： sqlsql-serversql-server-2012

Answer 1

我会使用 CTE https://docs.microsoft.com/en-us/sql/t-sql/queries/with-common-table-expression-transact-sql?view=sql-server-2017

WITH claimsGrp AS (
   SELECT
    CASE  
        WHEN DischargeType = 'dqs' THEN 'Disqualifying Status'
        WHEN DischargeType = 'fraud' THEN 'Fraud'
        WHEN DischargeType = 'id theft' THEN 'ID Theft'
        WHEN DischargeType = 'unenforceable' THEN 'Unenforceable'
        WHEN DischargeType = 'unp' THEN 'Unpaid Refund'
        WHEN DischargeType = 'uns' THEN 'Unauthorized Signature/Payment'
        WHEN DischargeType IN ('atb appeal', 'cls appeal','dqs appeal','id 
        theft appeal','unp appeal','uns appeal') THEN 'Appeals'            
    END AS DischargeType 
   ,
   CASE WHEN (DateReceived > @StartDate AND DateReceived < DATEADD(dd, 
1, @EndDate)) THEN 1 ELSE 0 END AS Claims_Received
   ,CASE WHEN (DateCompleted > @StartDate AND DateCompleted < 
DATEADD(dd, 1, @EndDate)) THEN 1 ELSE 0 END AS Claims_Completed
   ,CASE WHEN DateCompleted IS NULL THEN 1 ELSE 0 END AS Claims_Pending
   ,DateReceived AS [Oldest_Claim]     
FROM 
Claims)

SELECT claimsGrp.DischargeType,
       SUM(claimsGrp.Claims_Received),
       SUM(claimsGrp.Claims_Completed),
       SUM(claimsGrp.Claims_Pending)
       min([Oldest_Claim])
        FROM claimsGrp
GROUP BY 
    DischargeType WITH ROLLUP      
ORDER BY 
    CASE WHEN DischargeType IS NULL THEN 1 ELSE 0 END, DischargeType