CODEWITHSUNDEEP
sqlsql-servert-sqlsumvariance
mark1234
mark1234

Reputation: 1121

SQL - Divide a value and make sure it re-sums to original

I need to split an invoice line into (in this instance) 3 parts (each with 2 decimal places), but I have ensure that the sum of the splits adds up to the original value.

For example: if I split 5.13 by 3%, 42% and 55% (each rounded to 2DP) I end up with:

0.15
2.82
2.15
Sum = 5.12.

The only way I can figure out how to do it is to use a case statement and split to 2DP on all but the last row.

For the last row, sum all the previous values and subtract this from the original value. This approach requires a few sub-selects. I'm sure there's a better way to do it, but drawing a blank.

(FYI, it's a 3rd party vendor product, so I have to start with a MONEY and return a insert into a MONEY type column and all values must be 2DP

Here's my example:

DECLARE
    @CurrExtPrice money = 5.13 


    DECLARE @tGLSplit AS TABLE
        (
         ID INT IDENTITY(1,1)
        ,GLCode NVARCHAR(50)
        ,PercentSplit DECIMAL(18, 2)
        )

    INSERT INTO @tGLSplit
            ([GLCode], [PercentSplit])
    Select
         'Split1', 0.03 
    UNION ALL
    Select
        'Split2', 0.55 
    UNION ALL
    Select
        'Split3', 0.42 

    -- Source Date
    SELECT * FROM @tGLSplit [tgs]


    SELECT 
    @CurrExtPrice AS OriginalValue
    ,tg.[PercentSplit]
    ,CAST(@CurrExtPrice * tg.[PercentSplit] AS DECIMAL(18,2)) AS Split2DP
    ,CASE 
        WHEN tg.ID < (SELECT MAX(ID) FROM @tGLSplit)
        THEN
            CAST(@CurrExtPrice * tg.[PercentSplit] AS DECIMAL(18,2))
        ELSE 
            (SELECT @CurrExtPrice - SUM(CAST(@CurrExtPrice * [PercentSplit] AS decimal(18,2))) FROM @tGLSplit WHERE ID < (SELECT MAX(ID) FROM @tGLSplit) )
    END AS NewSplitValue

    FROM @tGLSplit tg

Anyone have a magic algorithm?

TIA

Mark

Upvotes: 3

Views: 1853

Answers (1)

Matt
Matt

Reputation: 1431

This was successful for me. Rounding to the 1000's at a line level seemed to produce a sum that worked when rounded to the 100's. Cleanup and modify to your specific needs.

IF OBJECT_ID('tempdb..#Temp') IS NOT NULL DROP TABLE #Temp
GO

DECLARE @Total MONEY = 5.13;

;WITH 
SplitData AS 
    (   
        SELECT .03 AS SplitPercent
        UNION
        SELECT .42
        UNION
        SELECT .55 AS SplitPercent
    ),
Percents AS 
    (
    SELECT  S.SplitPercent, 
            @Total AS FullMoneyValue, 
            @Total * S.SplitPercent AS Total,
            ROUND(@Total * S.SplitPercent,3,0) AS RoundedTotal
    FROM SplitData AS S
    )
SELECT  P.*
INTO    #Temp
FROM    Percents AS P

SELECT  SUM(P.Total), ROUND(SUM(P.RoundedTotal),2,0)
FROM    #Temp AS P

Upvotes: 1

Related Questions