SQL Server Group By/Sum By Company Name，Balance

Question

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

我现在需要做的是按保险公司名称和年龄对此数据进行分组/求和。例如，Aetna的每条记录都减少到一条记录。这一条记录有一个用于当前余额的字段，一个用于30天过期余额的字段，一个用于60天过期余额的字段，等等。安泰的每个当前余额都应该在一个安泰记录的第一个字段中求和。每隔30天，Aetna的逾期余额应在一个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 each循环。任何帮助都将不胜感激！

当前：

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不是保险公司的Name，而只是一些代码，那么我可以将INNER JOIN添加到具有全名的表中。