Aggregate value by group

Transact-SQL
1

Hi,

I have the following table:

create TABLE test (
[Claim ID] int NOT NULL,
[Damage ID] int NOT NULL,
[Location ID] int NOT NULL,
[Seq Num] int NOT NULL,
[Reserve Date] datetime NOT NULL,
[Reserve amt] Decimal(18,2) NOT NULL
)

insert into test

select 668835, 631560, 36225627, 1, '06-09-2014', 20000
UNION ALL
select 668835, 631560, 36225627, 2, '06-19-2014', 20000
UNION ALL
select 668835, 631558, 36225627, 1, '08-28-2014', 16000

What I need to do is create a new column called Reserve Accuracy Amt for each row that will sum the Reserve Amt for the max reserve date for each Claim ID, Damage ID, and Location ID that is <= the date for that row ( I think i'm saying that right).

so in the example above, the first row has a reserve date of 6-09-2014 and there were no other rows for that group before that date so the new field should just contain 20,000. The second row has the same Claim ID, Damage ID, and Location ID and a reserve date of 6-19-2014. Since this is the max row for that group it should also just have 20,000. Now the third row has a new damage code, but same claim ID and location id with a reserve date of 8-28-2014. So this row should sum the Max row of the 2 damage codes for that claim id and location id. So this row should add the 20,000 from 6-19-2020 (since this is the max row for damage code 631560) and also the 16,000 which is 36,000.

I'm not sure if this can be done using set based logic or maybe a cursor of some sort. The table has many more rows, but I would need this result:
Capture (002)

thanks

Scott

2

It is best to avoid spaces in object names. Try using an _ instead.
Maybe:

SELECT [Claim ID], [Damage ID], [Location ID], [Seq Num], [Reserve Date], [Reserve amt]
	,SUM([Reserve amt])
		OVER (PARTITION BY [Claim ID], [Location ID], [Seq Num]
				ORDER BY [Reserve Date] ROWS UNBOUNDED PRECEDING) AS Reserve_Accuracy_Amt 
FROM dbo.Test
ORDER BY [Reserve Date];
3

Hi All,

so i met with business users and it seems i had the requirements a bit mixed up. I think it might even be a bit simpler now. So i made the test data a bit more simple:

create TABLE test (

[Claim ID] int NOT NULL,

[Damage ID] int NOT NULL,

[Location ID] int NOT NULL,

[Seq Num] int NOT NULL,

[Reserve Date] datetime NOT NULL,

[Reserve amt] Decimal(18,2) NOT NULL

)

insert into test

select 1, 234, 999, 1, '06-09-2014', 20000

UNION ALL

select 1, 234, 999, 2, '06-19-2014', 30000

UNION ALL

select 1, 567, 999, 1, '06-09-2014', 16000

UNION ALL

select 1, 567, 999, 2, '08-19-2014', 25000

what I need is to get the distinct list of Claim ID, Location ID and Reserve Date from the test table which is simple. Once I have that list I need to then create a new Reserve Accuracy Amt column and for each row in the new list and find the max row for each grouping of the Claim ID, Damage ID and Location ID from the original test table where the Reserve Date is <= the Reserve Date for each row of the new list.

so I would need this result in the new table based off the original test table above:
Capture (004)
so in the original table, there are 3 distinct reserve dates for the claim. For 6-9-2014 the original test table only had 2 rows for each Claim ID, Damage ID and Location ID group where the reserve date was <= 6-9-2014 so the amount is the the sum of the 20,000 and 16,000 to get the 36,000. For the 6-19-2014 row, the max for each group would be the 6-19-2014 row from damage 234 and also the 6-9-2014 row for damage 567, since they both are <= 6-19-2014. So the total is 46,000. for the 8-19-2014 row, the max for each group would be the 6-19-2014 row for damage 234 and the 8-19-2014 row for damage 567 so the total would be 55,000.

i was think maybe an outer apply would work here but can't get it working correclty.

sorry for the confusion.

thanks