SQL Server Group By/Sum By Company Name, Balance

Question

我在SQL Server 2005工作。我有一个存储过程，它创建一个临时表并从数据库表中提取数据。这个临时表包含付款人类别的名称、余额/贷方、时间段(月)和余额的"年龄"(当前、逾期30天、逾期60天等)。

我现在需要做的是按保险公司名称和年龄对这些数据进行分组/求和。例如，Aetna的每条记录被简化为一条记录。该记录有一个字段用于当前余额，一个字段用于逾期30天余额，一个字段用于逾期60天余额，等等。每个Aetna的当前余额应该在Aetna记录的第一个字段中进行汇总。Aetna的逾期余额每30天应在一条Aetna记录的第二个字段中进行汇总，等等。结果表应该类似于:

Payor Class Cur         30          60          90          120         150
Aetna       1234.00     987.00      0.00        123.00      456.00      0.00
Blue Cross  3216.00     2100.00     321.00      0.00        212.00      401.00
Cigna       56123.00    30887.00    0.00        0.00        0.00        0.00
Kaiser      21003.00    1806.00     904.00      305.00      0.00        0.00

我当前的存储过程从应用程序(VB.NET)获取传入的开始日期，计算周期(即30天，60天，等等)，然后将所有余额记录拉到临时表中。有一些预先确定的类别(医疗保险，医疗补助，私人)，但任何不属于这些类别之一的东西都被分配到付款人类别，该类别只是付款人的名字(例如Aetna, Blue Cross等)。

我的问题是我事先不知道可能有多少不同的保险公司记录，所以我不确定如何根据这些记录和年龄进行分组/汇总。如果可以避免的话，我宁愿不做for每个循环。任何帮助将非常感激!

当前:

CREATE PROCEDURE [AgedReport]    
    @VCurrStart     DATETIME
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRY
        CREATE TABLE #t_temp
            (
                payor_class             VARCHAR(30),
                bal                     DECIMAL(11, 2), 
                period                  DATETIME,               
                age                     VARCHAR(10)
            )
        CREATE TABLE #t_class
            (
                payor_class             VARCHAR(30),
                balType                 VARCHAR(10),
                balTotal                DECIMAL(11,2),
                balAdv                  DECIMAL(11,2),
                balCurr                 DECIMAL(11,2),
                bal30                   DECIMAL(11,2),
                bal60                   DECIMAL(11,2),
                bal90                   DECIMAL(11,2),
                bal120                  DECIMAL(11,2),
                bal150                  DECIMAL(11,2),
                bal180                  DECIMAL(11,2),
                bal210                  DECIMAL(11,2),
                bal240                  DECIMAL(11,2),
                bal270                  DECIMAL(11,2)
            )
        DECLARE @VCurrEnd           DATETIME
        DECLARE @V30Start           DATETIME
        DECLARE @V60Start           DATETIME
        DECLARE @V90Start           DATETIME
        DECLARE @V120Start          DATETIME
        DECLARE @V150Start          DATETIME
        DECLARE @V180Start          DATETIME
        DECLARE @V210Start          DATETIME
        DECLARE @V240Start          DATETIME
SET @VCurrEnd = DATEADD(DAY,-1,(DATEADD(MONTH,1,@VCurrStart)))
SET @V30Start = DATEADD(month,-1,@VCurrStart)
SET @V60Start = DATEADD(month,-2,@VCurrStart)
SET @V90Start = DATEADD(month,-3,@VCurrStart)
SET @V120Start = DATEADD(month,-4,@VCurrStart)
SET @V150Start = DATEADD(month,-5,@VCurrStart)
SET @V180Start = DATEADD(month,-6,@VCurrStart)
SET @V210Start = DATEADD(month,-7,@VCurrStart)
SET @V240Start = DATEADD(month,-8,@VCurrStart)
INSERT INTO #t_temp
            (
                payor_class,
                bal,
                period,             
                age
            )            
SELECT 
DISTINCT
                (CASE 
                    (CASE WHEN p.payor_specific = 1 THEN 
                        (SELECT TOP 1 ar_account
                            FROM payor_seg ps
                            WHERE ps.payor_code = b.payor_code 
                            AND ps.start_date <= @VCurrEnd 
                            ORDER BY ps.start_date DESC) 
                    ELSE (SELECT TOP 1 ar_account
                            FROM plan_seg pls 
                            WHERE pls.plan_code = b.plan_code 
                            AND pls.start_date <= @VCurrEnd 
                            ORDER BY pls.start_date DESC) END) 
                WHEN '123100' THEN 'Private'
                WHEN '123110' THEN 'Medicaid'
                WHEN '123120' THEN 'Medicaid'
                WHEN '123130' THEN 'Medicaid'
                WHEN '123140' THEN 'Medicare A'             
                WHEN '123150' THEN 'Medicare B'
                ELSE (CASE WHEN p.payor_specific = 1 THEN 
                        (CASE WHEN (SELECT TOP 1 payor_name
                                    FROM payor p
                                    WHERE p.payor_code = b.payor_code) IS NULL THEN
                            'UNKNOWN'
                              WHEN (SELECT TOP 1 payor_name
                                    FROM payor p
                                    WHERE p.payor_code = b.payor_code) = '' THEN
                            'UNKNOWN'
                              ELSE '*' + SUBSTRING(p.payor_name,1,19) END)                            
                    WHEN p.payor_specific = 0 THEN 
                        (CASE WHEN (SELECT TOP 1 plan_description
                                    FROM plans pl
                                    WHERE pl.plan_code = b.plan_code) IS NULL THEN
                            'UNKNOWN'
                              WHEN (SELECT TOP 1 plan_description
                                    FROM plans pl
                                    WHERE pl.plan_code = b.plan_code) = '' THEN
                            'UNKNOWN'                             
                              ELSE '*' + SUBSTRING(pl.plan_description,1,19) END)
                    ELSE 'UNKNOWN'
                    END) END),
                b.bal,
                b.period,               
                (SELECT CASE 
                WHEN period > @VCurrStart THEN 'ADV'
                WHEN period = @VCurrStart THEN 'CUR'
                WHEN period = @V30Start THEN '30'
                WHEN period = @V60Start THEN '60'
                WHEN period = @V90Start THEN '90'
                WHEN period = @V120Start THEN '120'
                WHEN period = @V150Start THEN '150'
                WHEN period = @V180Start THEN '180'
                WHEN period = @V210Start THEN '210'
                WHEN period = @V240Start THEN '240'
                WHEN period < @V240Start THEN '270'
                END)
            FROM balance b WITH(NOLOCK)         
SELECT * FROM #t_temp
END TRY
    BEGIN CATCH
    END CATCH
END

Answer 1

除非我遗漏了您的一些要求，否则以下内容应该可以满足您的要求:

SELECT payor_class, SUM(balCurr), SUM(bal30), SUM(bal60), SUM(bal90), SUM(bal120), 
       SUM(bal150), SUM(bal180), SUM(bal210), SUM(bal240), SUM(bal270)
FROM Temp
GROUP BY payor_class
ORDER BY payor_class

如果payor_class不是保险公司名称而只是一些代码，那么我可以将INNER JOIN添加到具有全名的表中。