October 1, 2014 at 6:31 pm
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.
October 1, 2014 at 6:58 pm
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
October 1, 2014 at 11:04 pm
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
October 2, 2014 at 10:34 am
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