0

I have a table that stores Inventory Data by InventoryID and week. I need to somehow query and keep a running total of the DeltaQty from the prior weeks in order to figure out what is the Cumulative AvailQty for each week (Running total + AvailableQty)

CREATE TABLE InventoryWeekly (
    StartDate date NULL,
    EndDate date NULL,
    InventoryID varchar(11) NULL,
    AvailableQty float NULL,
    OutgoingQty float NULL,
    DeltaQty int NULL,
    CumulativeAvailQty int NULL
);

INSERT INTO InventoryWeekly (InventoryID, StartDate, EndDate, OutgoingQty, AvailableQty, DeltaQty, CumulativeAvailQty)
VALUES
('00069','2023-01-09','2023-01-15', 1, 2, 1, 0),
('00069','2023-01-16','2023-01-22', 2, 2, 0, 0),
('00069','2023-01-23','2023-01-29', 3, 0, -3, 0),
('00071','2023-01-09','2023-01-15', 5, 8, 3, 0),
('00071','2023-01-16','2023-01-22', 2, 3, 1, 0),
('00071','2023-01-23','2023-01-29', 3, 1, -2, 0);

I've created this fiddle.

I've tried using LAG function but it isn't cumulative.

Nick T
  • 3
  • 3
  • can you add your expected output for given sample data? – Somendra Kanaujia Jan 15 '23 at 14:58
  • Why are you using a `float` for some quantities and an `int` for others? A `float` is likely poor choice for the data type here, as it's unlikely a base 2 value will be an appropriate value for how much you have of something. – Thom A Jan 15 '23 at 15:02
  • Does this answer your question? [Calculate a Running Total in SQL Server](https://stackoverflow.com/questions/860966/calculate-a-running-total-in-sql-server) – siggemannen Feb 11 '23 at 14:12

1 Answers1

0

Let's start with

ALTER TABLE InventoryWeekly ADD CONSTRAINT InventoryWeekly_PK PRIMARY KEY CLUSTERED (StartDate)

It would help if you could describe what the output looks like.

A running total is just the sum of all previous values, which you can obtain as

SELECT iw.StartDate, iw.EndDate, iw.InventoryID, iw.AvailableQty, iw.OutgoingQty, iw.DeltaQty,
    SUM(h.AvailableQty) AS TotalPreviousAvailableQuantity
FROM InventoryWeekly iw
    LEFT JOIN InventoryWeekly h ON iw.StartDate > h.StartDate
GROUP BY iw.StartDate, iw.EndDate, iw.InventoryID, iw.AvailableQty, iw.OutgoingQty, iw.DeltaQty
Richard Barraclough
  • 2,625
  • 3
  • 36
  • 54
  • All supported versions of SQL Server (and even some unsupported) allow for windowing of `SUM` so why not use that? A triangular join isn't going to be efficient here; especially with larger numbers of rows. – Thom A Jan 15 '23 at 15:04