Aggregate Values By Group

[scabral1979]

Hi,

I have the following sample table with some sample data:

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
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 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 (see attached image)

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.

thanks

 

[imex]

Hi,

Try:

with CTE_Sum as
(
    select [Claim ID], [Location ID], [Reserve Date], sum([Reserve amt]) as [Reserve amt]
    from test
    gropu by [Claim ID], [Location ID], [Reserve Date]
)

select 
    [Claim ID], 
    [Location ID], 
    [Reserve Date], 
    sum([Reserve amt]) 
        over(partition by [Claim ID], [Location ID] order by [Reserve Date]) as [Reserve amt]
from CTE_Sum

Hope this help

[URL unfurl="true"]https://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]