SQL to split a value across multiple lines

  • 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.

  • 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