SQL - 划分一个值并确保它重新计算为原始值

Question

我需要将发票行拆分为(在本例中)3 个部分(每个部分有 2 个小数位)，但我已确保拆分的总和等于原始值。

例如:如果我将 5.13 分成 3%、42% 和 55%(每个四舍五入为 2DP)，我最终得到:

0.15
2.82
2.15
Sum = 5.12.

我能弄清楚如何做到这一点的唯一方法是使用 case 语句并在除最后一行之外的所有行上拆分为 2DP。

对于最后一行，将所有先前值相加并从原始值中减去。这种方法需要一些子选择。我敢肯定有更好的方法可以做到这一点，但是空白。

(仅供引用，它是第 3 方供应商的产品，所以我必须从 MONEY 开始，然后将插入返回到 MONEY 类型的列中，所有值都必须是 2DP

这是我的例子:

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

谁有神奇的算法？

TIA

标记

Answer 1

这对我来说是成功的。在行级别四舍五入到 1000 似乎产生了一个在四舍五入到 100 时有效的总和。根据您的特定需求进行清理和修改。

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