Seems like a simple problem. Need to decrease an Amt by a fixed maximum spread over several rows.

  • I have an issue where I have multiple rows of data and I need to reduce a dollar amount by a fixed maximum. I am going to throw some code in here to give a rudimentary idea of the data and what the final result should be.

    declare @tbl table

    (LineNum int,

    Code varchar(2),

    Amt money,

    MaxAmt money

    )

    declare @tbl2 table

    (Code varchar(2),

    MaxAmt money)

    insert into @tbl (LineNum, Code, Amt, MaxAmt)

    values

    (1,'AA',10.00,50.00),

    (2,'AA',20.00,50.00),

    (3,'AA',40.00,50.00)

    insert @tbl2

    select distinct Code, MaxAmt from @tbl

    I need to run an update so that the result of the following query:

    select LineNum, Code, Amt, MaxAmt from

    @tbl

    Looks like this:

    LineNum Code Amt MaxAmt

    ----------- ---- --------------------- ---------------------

    1 AA 10.00 50.00

    2 AA 20.00 50.00

    3 AA 20.00 50.00

    (3 row(s) affected)

    I have tried cursors but got unexpected results or the MaxAmt always defaulted to the original even if I updated it. This seems like a simple problem but I have been banging my head against the wall for 2 days now. I've written some pretty complicated updates with less effort than this and I must have some mental block that is keeping me from figuring this out.

    Any help will be appreciated.

    always get a backup before you try that.

  • Have you tried the Quirky Update? It's explained in the following article: http://www.sqlservercentral.com/articles/T-SQL/68467/

    Be sure to understand it, follow the rules and test it correctly.

    Here's an idea using your sample tables.

    declare @tbl table

    (LineNum int PRIMARY KEY CLUSTERED,

    Code varchar(2),

    Amt money,

    MaxAmt money

    )

    insert into @tbl (LineNum, Code, Amt, MaxAmt)

    values

    (1,'AA',10.00,50.00),

    (2,'AA',20.00,50.00),

    (3,'AA',10.00,50.00),

    (4,'AA',40.00,50.00)

    DECLARE @Amt money = 0,

    @AcumAmt money = 0

    UPDATE t SET

    @Amt = Amt = CASE WHEN @AcumAmt + Amt < MaxAmt THEN Amt

    WHEN MaxAmt - @AcumAmt < 0 THEN 0

    ELSE MaxAmt - @AcumAmt END,

    @AcumAmt = @AcumAmt + Amt

    FROM @tbl t

    OPTION (MAXDOP 1)

    SELECT *

    FROM @tbl

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • An alternative solution, not as efficient as QU but should be easier to understand

    😎

    USE tempdb;

    GO

    SET NOCOUNT ON;

    declare @tbl table

    (LineNum int,

    Code varchar(2),

    Amt money,

    MaxAmt money

    )

    insert into @tbl (LineNum, Code, Amt, MaxAmt)

    values

    (1,'AA',10.00,50.00),

    (2,'AA',20.00,50.00),

    (3,'AA',10.00,50.00),

    (4,'AA',40.00,50.00),

    (5,'AA',10.00,50.00);

    SELECT

    T.LineNum

    ,T.Code

    ,T.Amt

    ,CASE

    WHEN (X.ACAmt) = T.MaxAmt THEN 0.0

    WHEN (T.Amt + X.ACAmt) <= T.MaxAmt THEN T.Amt

    WHEN (T.Amt + X.ACAmt) > T.MaxAmt AND X.ACAmt < T.MaxAmt THEN (T.MaxAmt - X.ACAmt)

    ELSE 0.0

    END AS NEW_Amt

    ,T.MaxAmt

    ,X.ACAmt

    FROM @tbl T

    CROSS APPLY

    (SELECT ISNULL(SUM(Amt),0) AS ACAmt FROM @tbl T2

    WHERE T.LineNum > T2.LineNum

    AND T.Code = T2.Code) AS X;

    Results

    LineNum Code Amt NEW_Amt MaxAmt ACAmt

    -------- ---- ------ -------- ------- ------

    1 AA 10.00 10.0000 50.00 0.00

    2 AA 20.00 20.0000 50.00 10.00

    3 AA 10.00 10.0000 50.00 30.00

    4 AA 40.00 10.0000 50.00 40.00

    5 AA 10.00 0.0000 50.00 80.00

  • Hey-O

    I got Luis suggestion first and implemented it and it worked like a charm! Since my rows are interspersed I tried it on my larger data set and used a cursor to feed in matching rows and got the result I want every time. It is also very efficient. I can't say thanks enough for such a great/elegant solution.

    thanks guys.

    always get a backup before you try that.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply