June 24, 2016 at 4:18 am
one table will have a total amount available and the other table will have line items for the amount to decrement (think amount left on gift card). If the total amount remaining is less than the line item amount then line item amount should be set to total amount available with total amount set to zero.
CREATE TABLE qamt(id INT, amt INT);
CREATE TABLE qtot(id INT, tot INT);
INSERT INTO qamt
VALUES(1,10),
(1,20),
(1,30),
(2,90),
(2,10),
(3,150),
(4,20),
(4,40),
(4,50),
(4,70);
INSERT INTO qtot
VALUES(1,100),
(2,100),
(3,100),
(4,100);
I want to result below the format..
id tot amt RTS_BALANCE AC_BALANCE
----------- ----------- ----------- ----------- -----------
1 100 10 90 90
1 100 20 70 70
1 100 30 40 40
2 100 90 10 10
2 100 10 0 0
3 100 150 0 -50
4 100 20 80 80
4 100 40 40 40
4 100 50 0 -10
4 100 70 0 -80
July 11, 2016 at 2:10 pm
As I recall, 2005 doesn't support windowed functions, so you have to deal with any running totals problem (decrementing from a total falls under the category of running totals) in one of two ways: Either use a loop or a cursor to do row by row decrements, or use the infamous quirky update method.
Read this to get you started:
http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 26, 2022 at 2:36 pm
This was removed by the editor as SPAM
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply