按BillingId分组Payments表并生成临时表的SQL实现求助

需要对Payments表按BillingId分组，提取以下字段（加粗字段为当前未实现的难点）：

• Billing Id
• Total Number Of ANY RESPONSES Made by Insurance
• Earliest ANY RESPONSE (Made by Insurance) Received On
• Earliest ANY RESPONSE (Made by Insurance) Insurance
• Earliest ANY RESPONSE (Made by Insurance) Type
• Earliest ANY RESPONSE (Made by Insurance) Amount
• Total Number Of PAYMENTS Made by Insurance
• Latest PAYMENT (Made by Insurance) Received On
• Latest PAYMENT (Made by Insurance) Insurance
• Latest PAYMENT (Made by Insurance) Type
• Latest PAYMENT (Made by Insurance) Amount
• Total Amount Adjustment
• Total Amount Paid

最终结果需保存至临时表#GroupedPaymentsData。

原始数据定义与示例 #

CREATE TABLE Payments
(PaymentId INT, PaymentRecordDate DATE, BillingId INT,  InsuranceName VARCHAR(25), PaymentType VARCHAR(25), PaymentAmount FLOAT, PaymentIsCopay VARCHAR(25));

INSERT INTO Payments
VALUES
(1, '2022-02-11', 11111, 'Kaiser', 'Cash', 25.0, 'No'),
(2, '2022-02-05', 11111, 'Kaiser', 'Check', 100.0, 'No'),
(3, '2022-07-01', 11111, 'Cigna', 'Electronic', 50.0, 'No'),
(4, '2022-06-25', 33333, 'Patient', 'Electronic', 100.0, 'Yes'),
(5, '2022-07-15', 33333, 'Cigna', 'Adjustment', 50.0, 'No'),
(6, '2022-01-10', 77777, 'Tricare', 'Electronic', 25.0, 'No'),
(7, '2022-02-11', 77777, 'Tricare', 'Adjustment', 35.0, 'No'),
(8, '2022-01-15', 77777, 'Patient', 'Cash', 50.0, 'Yes'),
(9, '2022-01-05', 77777, 'Tricare', 'Credit Card', 100.0, 'No')

SELECT * FROM Payments

现有代码 #

IF OBJECT_ID ('tempdb..#GroupedPaymentsData') IS NOT NULL  
DROP TABLE tempdb..#GroupedPaymentsData

IF OBJECT_ID('tempdb..#GroupedPaymentsData') IS NULL (

  SELECT

       MainTable.BillingId AS [Billing Id],

       COUNT(CASE WHEN (MainTable.PaymentIsCopay = 'No') THEN 1 END) AS [Total Number Of ANY RESPONSES Made by Insurance],
       -- AS [Earliest ANY RESPONSE (Made by Insurance) Received On],
       -- AS [Earliest ANY RESPONSE (Made by Insurance) Insurance],
       -- AS [Earliest ANY RESPONSE (Made by Insurance) Type],
       -- AS [Earliest ANY RESPONSE (Made by Insurance) Amount],

       COUNT(CASE WHEN MainTable.PaymentType IN ('Electronic', 'Cash', 'Check', 'Credit Card') AND (MainTable.PaymentIsCopay = 'No') AND (MainTable.PaymentAmount > 0) THEN 1 END) AS [Total Number Of PAYMENTS Made by Insurance],
       -- AS [Latest PAYMENT (Made by Insurance) Received On],
       -- AS [Latest PAYMENT (Made by Insurance) Insurance],
       -- AS [Latest PAYMENT (Made by Insurance) Type],
       -- AS [Latest PAYMENT (Made by Insurance) Amount],

       SUM(CASE WHEN MainTable.PaymentType IN ('Adjustment') THEN MainTable.PaymentAmount ELSE 0 END) AS [Total Amount Adjustment],                         
       SUM(CASE WHEN MainTable.PaymentType NOT IN ('Adjustment') THEN MainTable.PaymentAmount ELSE 0 END) AS [Total Amount Paid]

  INTO  #GroupedPaymentsData

  FROM (

    SELECT p.PaymentId,
            p.PaymentRecordDate,
            p.BillingId,
            p.InsuranceName,
            p.PaymentType,
            p.PaymentAmount,
            p.PaymentIsCopay

    FROM Payments as p

    ) AS MainTable

  GROUP BY MainTable.BillingId

  );

SELECT * FROM #GroupedPaymentsData

期望输出 #

定义说明 #

• ANY RESPONSE (Made by Insurance)：排除PaymentIsCopay = 'Yes'的所有交易
• PAYMENT (Made by Insurance)：排除PaymentIsCopay = 'Yes'或PaymentType = 'Adjustment'的所有交易

解决方案代码 #

IF OBJECT_ID ('tempdb..#GroupedPaymentsData') IS NOT NULL  
DROP TABLE tempdb..#GroupedPaymentsData

-- 用CTE标记每个BillingId下的最早响应和最晚支付记录
WITH PaymentCTE AS (
    SELECT 
        BillingId,
        PaymentRecordDate,
        InsuranceName,
        PaymentType,
        PaymentAmount,
        PaymentIsCopay,
        -- 按日期升序标记最早的保险响应
        ROW_NUMBER() OVER (PARTITION BY BillingId ORDER BY CASE WHEN PaymentIsCopay = 'No' THEN PaymentRecordDate END ASC) AS EarliestResponseRow,
        -- 按日期降序标记最晚的保险支付
        ROW_NUMBER() OVER (PARTITION BY BillingId ORDER BY CASE WHEN PaymentIsCopay = 'No' AND PaymentType NOT IN ('Adjustment') AND PaymentAmount > 0 THEN PaymentRecordDate END DESC) AS LatestPaymentRow
    FROM Payments
)
SELECT 
    cte.BillingId AS [Billing Id],
    -- 统计保险响应总数
    COUNT(CASE WHEN cte.PaymentIsCopay = 'No' THEN 1 END) AS [Total Number Of ANY RESPONSES Made by Insurance],
    -- 提取最早响应的相关字段
    MAX(CASE WHEN cte.EarliestResponseRow = 1 AND cte.PaymentIsCopay = 'No' THEN cte.PaymentRecordDate END) AS [Earliest ANY RESPONSE (Made by Insurance) Received On],
    MAX(CASE WHEN cte.EarliestResponseRow = 1 AND cte.PaymentIsCopay = 'No' THEN cte.InsuranceName END) AS [Earliest ANY RESPONSE (Made by Insurance) Payor],
    MAX(CASE WHEN cte.EarliestResponseRow = 1 AND cte.PaymentIsCopay = 'No' THEN cte.PaymentType END) AS [Earliest ANY RESPONSE (Made by Insurance) Type],
    MAX(CASE WHEN cte.EarliestResponseRow = 1 AND cte.PaymentIsCopay = 'No' THEN cte.PaymentAmount END) AS [Earliest ANY RESPONSE (Made by Insurance) Amount],
    -- 统计保险支付总数
    COUNT(CASE WHEN cte.PaymentType IN ('Electronic', 'Cash', 'Check', 'Credit Card') AND cte.PaymentIsCopay = 'No' AND cte.PaymentAmount > 0 THEN 1 END) AS [Total Number Of PAYMENTS Made by Insurance],
    -- 提取最晚支付的相关字段
    MAX(CASE WHEN cte.LatestPaymentRow = 1 AND cte.PaymentIsCopay = 'No' AND cte.PaymentType NOT IN ('Adjustment') AND cte.PaymentAmount > 0 THEN cte.PaymentRecordDate END) AS [Latest PAYMENT (Made by Insurance) Received On],
    MAX(CASE WHEN cte.LatestPaymentRow = 1 AND cte.PaymentIsCopay = 'No' AND cte.PaymentType NOT IN ('Adjustment') AND cte.PaymentAmount > 0 THEN cte.InsuranceName END) AS [Latest PAYMENT (Made by Insurance) Payor],
    MAX(CASE WHEN cte.LatestPaymentRow = 1 AND cte.PaymentIsCopay = 'No' AND cte.PaymentType NOT IN ('Adjustment') AND cte.PaymentAmount > 0 THEN cte.PaymentType END) AS [Latest PAYMENT (Made by Insurance) Type],
    MAX(CASE WHEN cte.LatestPaymentRow = 1 AND cte.PaymentIsCopay = 'No' AND cte.PaymentType NOT IN ('Adjustment') AND cte.PaymentAmount > 0 THEN cte.PaymentAmount END) AS [Latest PAYMENT (Made by Insurance) Amount],
    -- 计算调整总额和支付总额
    SUM(CASE WHEN cte.PaymentType IN ('Adjustment') THEN cte.PaymentAmount ELSE 0 END) AS [Total Amount Adjustment],                         
    SUM(CASE WHEN cte.PaymentType NOT IN ('Adjustment') THEN cte.PaymentAmount ELSE 0 END) AS [Total Amount Paid]
INTO #GroupedPaymentsData
FROM PaymentCTE cte
GROUP BY cte.BillingId;

SELECT * FROM #GroupedPaymentsData

内容的提问来源于stack exchange，提问作者Yara1994