Decrementing total for Sql server 2005

• vs.satheesh

  SSCrazy

  Points: 2392

  More actions

  June 24, 2016 at 4:18 am

  #104286

  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

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  July 11, 2016 at 2:10 pm

  #1889515

  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/%5B/url%5D

  __________________________________________________

  Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
  Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills