Apologies! My mistake sorry.

It should total the 100. Updated code attached:

declare @policy table

(policy_number int)

insert into @policy

select 100

union

select 101

union

select 102

declare @policy_risk table

(policy_number int,

risk_type int,

premium_class char(3))

insert into @policy_risk

select 100, 1, 'AA1'

union

select 100, 1, 'ACC'

union

select 100, 1, 'GHT'

union

select 100, 1, 'DES'

union

select 100, 1, 'FIR'

union

select 101, 1, 'FIR'

union

select 101, 1, 'SEC'

union

select 101, 1, 'PPL'

union

select 102, 2, 'STA'

union

select 102, 2, 'KLS'

union

select 102, 2, 'ZZA'

union

select 102, 3, 'HTD'

union

select 102, 3, 'DTR'

declare @claim table

(claim_number int,

policy_number int,

risk_type int,

premium_class char(3),

total_paid money,

total_recovered money)

insert into @claim

select 1, 101, 1, 'FIR', 345, 0

union

select 2, 102, 2, 'STA', 7465.21, 100

declare @claim_recoveries table

(claim_recovery_id int identity(1,1),

claim_number int,

policy_number int,

risk_type int,

premium_class char(3),

treaty char(3),

reinsurance_id int,

total_recovered money)

insert into @claim_recoveries

select 2, 102, 2, 'STA', 'QSH',50, 50

union all

select 2, 102, 2, 'STA', 'AAA',51, 50

select * from @policy

select * from @policy_risk

select * from @claim

select * from @claim_recoveries