November 1, 2012 at 3:24 am
Hi Guys,
I am currently generating a results set as follows;
[font="Courier New"]
Policy Claim Job Excess
A 1 E 50.00
A 1 F 0.00
A 2 X 50.00
A 2 Y 0.00
A 2 Z 0.00
[/font]
I need to split the excess evenly over the lines of the claims, so for claim 1 = 25.00 on each line. on claim two it would need to be 16.66,16.67,16.67 (I don't care which line gets the 16.66)
If I didn't need to deal with rounding I could use sum(excess)/count(job) in a CTE and then join the CTE back to original data set.
I am thinking I might just use a Temp Table, the dataset is only 750K rows long but wanted to know if anyone had a code snippet that might do it as part of the query.
November 1, 2012 at 3:46 am
Using the Floor/Ceiling with an accounting fudge (*100)/100.00 get it to work.
(
Policy char(1)
,Claim Int
,Job Char(1)
,Excess Decimal (19,2)
)
INSERT into #PolClaim
Values ('A',1,'E',50.00)
,('A',1,'F',50.00)
,('A',2,'X',50.00)
,('A',2,'Y',50.00)
,('A',2,'Z',50.00)
;
With PolicyClaimLines
AS
(
Select Policy,Claim,count(*) LineCount
From #PolClaim
Group by Policy,Claim
)
Select
Pol.Policy
,Pol.Claim
,Job
,Excess
,Excess/LineCount
,Row_Number() OVER (Partition By Pol.Policy,Pol.Claim ORDER by Job)
,CASE Row_Number() OVER (Partition By Pol.Policy,Pol.Claim ORDER by Job)
WHEN 1 then FLOOR((Excess/LineCount)*100)/100.00
ELSE CEILING((Excess/LineCount)*100)/100.00
END
From
#PolClaim Pol
JOIN PolicyClaimLines PCL
ON PCL.Policy=Pol.Policy
AND PCL.Claim=Pol.Claim
_________________________________________________________________________
SSC Guide to Posting and Best Practices
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply