By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE inventory (
SKU INT,
TransactionType VARCHAR(32),
WarehouseCode VARCHAR(32),
TransactionDate DATE,
Qty INT
)
INSERT INTO
inventory
VALUES
(100, 'IN', 'WH1', '2021-04-30', 100),
(100, 'IN', 'WH2', '2021-04-30', 50),
(101, 'IN', 'WH1', '2021-04-30', 30),
(101, 'IN', 'WH2', '2021-05-01', 25),
(100, 'OUT', 'WH2', '2021-05-02', 30),
(100, 'OUT', 'WH1', '2021-05-02', 20),
(100, 'OUT', 'WH1', '2021-05-04', 50),
(100, 'OUT', 'WH2', '2021-05-04', 20),
(100, 'OUT', 'WH1', '2021-05-05', 25),
(100, 'IN', 'WH2', '2021-05-10', 30),
(100, 'IN', 'WH1', '2021-05-11', 30),
(101, 'OUT', 'WH2', '2021-05-12', 20),
(100, 'OUT', 'WH1', '2021-05-15', 30),
(100, 'IN', 'WH1', '2021-05-16', 30),
(102, 'IN', 'WH1', '2021-05-15', 25),
(102, 'OUT', 'WH1', '2021-05-17', 2),
(102, 'ADJ', 'WH1', '2021-05-18', 5),
(102, 'ADJ', 'WH1', '2021-05-18', -1);
SELECT * FROM inventory
| SKU | TransactionType | WarehouseCode | TransactionDate | Qty |
|---|---|---|---|---|
| 100 | IN | WH1 | 2021-04-30 | 100 |
| 100 | IN | WH2 | 2021-04-30 | 50 |
| 101 | IN | WH1 | 2021-04-30 | 30 |
| 101 | IN | WH2 | 2021-05-01 | 25 |
| 100 | OUT | WH2 | 2021-05-02 | 30 |
| 100 | OUT | WH1 | 2021-05-02 | 20 |
| 100 | OUT | WH1 | 2021-05-04 | 50 |
| 100 | OUT | WH2 | 2021-05-04 | 20 |
| 100 | OUT | WH1 | 2021-05-05 | 25 |
| 100 | IN | WH2 | 2021-05-10 | 30 |
| 100 | IN | WH1 | 2021-05-11 | 30 |
| 101 | OUT | WH2 | 2021-05-12 | 20 |
| 100 | OUT | WH1 | 2021-05-15 | 30 |
| 100 | IN | WH1 | 2021-05-16 | 30 |
| 102 | IN | WH1 | 2021-05-15 | 25 |
| 102 | OUT | WH1 | 2021-05-17 | 2 |
| 102 | ADJ | WH1 | 2021-05-18 | 5 |
| 102 | ADJ | WH1 | 2021-05-18 | -1 |
WITH
cumulative AS
(
SELECT
*,
SUM(CASE WHEN TransactionType = 'IN' THEN Qty ELSE 0 END)
OVER (
PARTITION BY SKU, WarehouseCode
ORDER BY TransactionDate
)
AS qty_in_so_far,
SUM(CASE WHEN TransactionType = 'OUT' THEN Qty ELSE 0 END)
OVER (
PARTITION BY SKU, WarehouseCode
)
AS qty_out_final
FROM
inventory
)
SELECT
*,
CASE WHEN qty_out_final > qty_in_so_far THEN 0
WHEN qty_in_so_far - qty_out_final > qty THEN qty
ELSE qty_in_so_far - qty_out_final END AS qty_final,
DATEDIFF(day, TransactionDate, '2021-05-20') + 1 AS aging
FROM
cumulative
WHERE
TransactionType = 'IN'
ORDER BY
TransactionDate,
SKU,
WarehouseCode
| SKU | TransactionType | WarehouseCode | TransactionDate | Qty | qty_in_so_far | qty_out_final | qty_final | aging |
|---|---|---|---|---|---|---|---|---|
| 100 | IN | WH1 | 2021-04-30 | 100 | 100 | 125 | 0 | 21 |
| 100 | IN | WH2 | 2021-04-30 | 50 | 50 | 50 | 0 | 21 |
| 101 | IN | WH1 | 2021-04-30 | 30 | 30 | 0 | 30 | 21 |
| 101 | IN | WH2 | 2021-05-01 | 25 | 25 | 20 | 5 | 20 |
| 100 | IN | WH2 | 2021-05-10 | 30 | 80 | 50 | 30 | 11 |
| 100 | IN | WH1 | 2021-05-11 | 30 | 130 | 125 | 5 | 10 |
| 102 | IN | WH1 | 2021-05-15 | 25 | 25 | 2 | 23 | 6 |
| 100 | IN | WH1 | 2021-05-16 | 30 | 160 | 125 | 30 | 5 |