I Need to Keep a Running Total From Two Columns

• ducksql@gmail.com

  SSCrazy

  Points: 2826

  More actions

  August 11, 2016 at 1:31 pm

  #400565

  IF OBJECT_ID('TestData', 'U') IS NOT NULL BEGIN DROP TABLE TestData END

  IF OBJECT_ID('TestResults', 'U') IS NOT NULL BEGIN DROP TABLE TestResults END

  CREATE TABLE TestData (item NVARCHAR(30), [RType] NVARCHAR(20), FMonth INT, FYear INT, Qty DECIMAL(10, 2), TotalQty DECIMAL(10, 2))

  INSERT TestData SELECT '1A14483', '2.Supply', 8, 2016, 330, 330;

  INSERT TestData SELECT '1A14483', '3.Demand', 8, 2016, -279.35, -279.35;

  INSERT TestData SELECT '1A14483', '2.Supply', 9, 2016, 600, NULL;

  INSERT TestData SELECT '1A14483', '3.Demand', 9, 2016, -988.8, NULL;

  INSERT TestData SELECT '1A14483', '2.Supply', 10, 2016, 750, NULL;

  INSERT TestData SELECT '1A14483', '3.Demand', 10, 2016, -498.2, NULL;

  INSERT TestData SELECT '1A14483', '2.Supply', 11, 2016, 600, NULL;

  INSERT TestData SELECT '1A14483', '3.Demand', 11, 2016, -361.2, NULL;

  INSERT TestData SELECT '1A14483', '3.Demand', 12, 2016, -332.6, NULL;

  INSERT TestData SELECT '1A14483', '2.Supply', 1, 2017, 20, NULL;

  INSERT TestData SELECT '1A14483', '3.Demand', 1, 2017, -510, NULL;

  INSERT TestData SELECT '1A14483', '2.Supply', 2, 2017, 510, NULL;

  INSERT TestData SELECT '1A14483', '3.Demand', 2, 2017, -510, NULL;

  INSERT TestData SELECT '1A14483', '2.Supply', 3, 2017, 510, NULL;

  INSERT TestData SELECT '1A14483', '3.Demand', 3, 2017, -510, NULL;

  INSERT TestData SELECT '1A14483', '2.Supply', 4, 2017, 485, NULL;

  INSERT TestData SELECT '1A14483', '3.Demand', 4, 2017, -485, NULL;

  INSERT TestData SELECT '1A14483', '2.Supply', 5, 2017, 485, NULL;

  INSERT TestData SELECT '1A14483', '3.Demand', 5, 2017, -485, NULL;

  INSERT TestData SELECT '1A14483', '2.Supply', 6, 2017, 485, NULL;

  INSERT TestData SELECT '1A14483', '3.Demand', 6, 2017, -485, NULL;

  INSERT TestData SELECT '1A14483', '2.Supply', 7, 2017, 485, NULL;

  INSERT TestData SELECT '1A14483', '3.Demand', 7, 2017, -485, NULL;

  SELECT * FROM TestData

  CREATE TABLE TestResults (item NVARCHAR(30), [RType] NVARCHAR(20), FMonth INT, FYear INT, Qty DECIMAL(10, 2), TotalQty DECIMAL(10, 2))

  INSERT TestResults SELECT '1A14483', '2.Supply', 8, 2016, 330, 330;

  INSERT TestResults SELECT '1A14483', '3.Demand', 8, 2016, -279.35, -279.35;

  INSERT TestResults SELECT '1A14483', '2.Supply', 9, 2016, 600, 930;

  INSERT TestResults SELECT '1A14483', '3.Demand', 9, 2016, -988.8, -1268.15;

  INSERT TestResults SELECT '1A14483', '2.Supply', 10, 2016, 750, 1680;

  INSERT TestResults SELECT '1A14483', '3.Demand', 10, 2016, -498.2, -1766.35;

  INSERT TestResults SELECT '1A14483', '2.Supply', 11, 2016, 600, 2280;

  INSERT TestResults SELECT '1A14483', '3.Demand', 11, 2016, -361.2, -2127.55;

  INSERT TestResults SELECT '1A14483', '3.Demand', 12, 2016, -332.6, -2460.15;

  INSERT TestResults SELECT '1A14483', '2.Supply', 1, 2017, 20, 2300;

  INSERT TestResults SELECT '1A14483', '3.Demand', 1, 2017, -510, -2970.15;

  INSERT TestResults SELECT '1A14483', '2.Supply', 2, 2017, 510, 2810;

  INSERT TestResults SELECT '1A14483', '3.Demand', 2, 2017, -510, -3480.15;

  INSERT TestResults SELECT '1A14483', '2.Supply', 3, 2017, 510, 3320;

  INSERT TestResults SELECT '1A14483', '3.Demand', 3, 2017, -510, -3990.15;

  INSERT TestResults SELECT '1A14483', '2.Supply', 4, 2017, 485, 3805;

  INSERT TestResults SELECT '1A14483', '3.Demand', 4, 2017, -485, -4475.15;

  INSERT TestResults SELECT '1A14483', '2.Supply', 5, 2017, 485, 4290;

  INSERT TestResults SELECT '1A14483', '3.Demand', 5, 2017, -485, -4960.15;

  INSERT TestResults SELECT '1A14483', '2.Supply', 6, 2017, 485, 4775;

  INSERT TestResults SELECT '1A14483', '3.Demand', 6, 2017, -485, -5445.15;

  INSERT TestResults SELECT '1A14483', '2.Supply', 7, 2017, 485, 5260;

  INSERT TestResults SELECT '1A14483', '3.Demand', 7, 2017, -485, -5930.15;

  SELECT * FROM TestResults

  IF OBJECT_ID('TestData', 'U') IS NOT NULL BEGIN DROP TABLE TestData END

  IF OBJECT_ID('TestResults', 'U') IS NOT NULL BEGIN DROP TABLE TestResults END

  Using the sample code from above, I need to take the TestData table and make it look like the TestResults by taking the TotalQty in the TestData table from the previous month (grouped by Rtype, Fmonth, Fyear), adding it to the next month's Qty, and putting the result in the next month's TotalQty column. Then repeat for the next month until all records are gone. Is this possible?

  Thank you.

  Steve

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  August 11, 2016 at 1:47 pm

  #1895101

  Check the following article for the best options on doing this in 2008 and previous versions.

  http://www.sqlservercentral.com/articles/T-SQL/68467/

  If by any chance you're on 2012 or a more recent version, a simpler option is available.

  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

• Y.B.

  SSChampion

  Points: 11649

  More actions

  August 11, 2016 at 2:12 pm

  #1895110

  This gave me the results you were looking for.

  SELECT item, RType, FMonth, FYear, Qty,

  SUM(QTy) OVER (PARTITION BY item ORDER BY FYear, FMonth) AS TotalQty

  FROM TestData

  WHERE RType = '2.Supply'

  UNION ALL

  SELECT item, RType, FMonth, FYear, Qty,

  SUM(QTy) OVER (PARTITION BY item ORDER BY FYear, FMonth) AS TotalQty

  FROM TestData

  WHERE RType = '3.Demand'

  ORDER BY item, FYear, FMonth, RTYPE

  Cheers,

  ---

  SELECT quote FROM brain WHERE original = 1
  0 rows returned

• ducksql@gmail.com

  SSCrazy

  Points: 2826

  More actions

  August 11, 2016 at 2:42 pm

  #1895121

  That works! Thanks!

• ducksql@gmail.com

  SSCrazy

  Points: 2826

  More actions

  August 11, 2016 at 2:43 pm

  #1895122

  Thank you for the article, Luis. I will be adding that to my bookmarks.