Question: SQL Server Group By/Sum By Company Name, Balance

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.).

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:

Payor Class Cur 30 60 90 120 150 Aetna 1234.00 987.00 0.00

456.00

123.00

0.00

2100.00

Blue Cross 3216.00

0.00

321.00

212.00 401.00

56123.00

Cigna

30887.00

0.00

0.00 0.00

0.00 Kaiser 21003.00 1806.00 904.00 305.00 0.00 0.00

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.).

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!

Current:

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

If u have any answer ...pls do