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:
thanks
Scott