title: SQL Server Group By/Sum By Company Name, Balance
body: <p>I am working in SQL Server 2005.  I have a stored procedure that creates a temp table and pulls data in from a database table.  This temp table contains the name of the payor class, the balance/credit, the time period (month), and the "age" of the balance (current, 30 days past due, 60 days past due, etc.).</p>
  
  <p>What I now need to do is group/sum this data by insurance company name and age.  For example, every record for Aetna is reduced to one record.  That one record has a field for current balance, a field for 30 days past due balance, a field for 60 days past due balance, etc.  Every current balance for Aetna should be summed in the first field of the one Aetna record.  Every 30 days past due balance for Aetna should be summed in the second field of the one Aetna record, etc., etc.  The resulting table should look something like:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>My current stored procedure gets the start date passed in from the application (VB.NET), calculates the periods (i.e. what is 30 days, what is 60 days, etc.), then pulls all balance records into a temp table.  There are some predetermined groupings (Medicare, Medicaid, Private) but anything that doesn't fall into one of those groups is assigned to a payor class that is just the payor's name (e.g. Aetna, Blue Cross, etc.).</p>
  
  <p>My problem is that I have no idea ahead of time how many different insurance company records there might be, so I'm not sure how to group/sum based on that and on the age.  I'd rather not do a for each loop if I can avoid it.  Any help would be much appreciated!</p>
  
  <p>Current:</p>
  
  <pre><code>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 &lt;= @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 &lt;= @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 &gt; @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 &lt; @V240Start THEN '270'
                  END)
              FROM balance b WITH(NOLOCK)         
  
  SELECT * FROM #t_temp
  
  END TRY
      BEGIN CATCH
  
      END CATCH
  END
  </code></pre>