I have an instance where I need to be calculating the running total by positive transactions. This exercise is to determine which positive entries in our warehouse entries is still open as we need to determine the date of inventory based off of our item ledger entries. We are using NAV 2018 for reference.
I already have the running total ([Qty (Base) Running Total]
) per item and bin ([Item No_], [Location Code], [Bin Code]
):
select
we.[Entry No_],
we.[Registering Date],
we.[Item No_],
we.[Location Code],
we.[Bin Code],
we.[Qty_ (Base)],
sum(we.[Qty_ (Base)]) over (partition by we.[Item No_], we.[Location Code], we.[Bin Code]
order by we.[Entry No_]) as [Qty (Base) Running Total],
(select sum(we2.[Qty_ (Base)])
from [Warehouse Entry] we2 with(nolock)
where we2.[Item No_] = we.[Item No_]
and we2.[Location Code] = we.[Location Code]
and we2.[Bin Code] = we.[Bin Code]) as [Total Qty Base In Bin]
from
[Warehouse Entry] we with(nolock)
In the example dataset below (where I have filtered the dataset down to 1 item and 1 bin already), I need to know when the initial 1280 (and all positive transactions) went to 0 so that I do not include that entry as "still in inventory".
In the dataset when I manually sum the Qty (Base)
I can see that the initial 1280 was zeroed out by Entry No 927025. Similarly, Entry No 656541 for 600 is zeroed out by Entry No 1205470, etc.
Dataset (Entry No., Registering Date, Qty (Base)
are my data, Qty (Base) Running Total
is computed by the above query):
Entry No. | Registering Date | Qty (Base) | Qty (Base) Running Total |
---|---|---|---|
427752 | 4/17/2025 | 1280 | 1280 |
577708 | 4/28/2025 | -100 | 1180 |
593215 | 4/29/2025 | -130 | 1050 |
611579 | 4/30/2025 | -130 | 920 |
616674 | 4/30/2025 | -10 | 910 |
616682 | 4/30/2025 | -10 | 900 |
616686 | 4/30/2025 | -10 | 890 |
616758 | 4/30/2025 | -10 | 880 |
616913 | 4/30/2025 | -10 | 870 |
622854 | 4/30/2025 | -20 | 850 |
622863 | 4/30/2025 | -20 | 830 |
622875 | 4/30/2025 | -20 | 810 |
622885 | 4/30/2025 | -20 | 790 |
622902 | 4/30/2025 | -20 | 770 |
633070 | 5/1/2025 | -120 | 650 |
656541 | 5/2/2025 | 600 | 1250 |
671925 | 5/5/2025 | -30 | 1220 |
704308 | 5/7/2025 | -20 | 1200 |
736014 | 5/8/2025 | -20 | 1180 |
853955 | 5/19/2025 | -50 | 1130 |
884492 | 5/21/2025 | -10 | 1120 |
884495 | 5/21/2025 | -10 | 1110 |
884499 | 5/21/2025 | -10 | 1100 |
884501 | 5/21/2025 | -10 | 1090 |
884505 | 5/21/2025 | -10 | 1080 |
884509 | 5/21/2025 | -10 | 1070 |
884515 | 5/21/2025 | -10 | 1060 |
884517 | 5/21/2025 | -10 | 1050 |
884519 | 5/21/2025 | -10 | 1040 |
884521 | 5/21/2025 | -10 | 1030 |
887327 | 5/21/2025 | -120 | 910 |
893571 | 5/22/2025 | -10 | 900 |
893573 | 5/22/2025 | -10 | 890 |
893578 | 5/22/2025 | -10 | 880 |
893582 | 5/22/2025 | -10 | 870 |
893584 | 5/22/2025 | -10 | 860 |
893586 | 5/22/2025 | -10 | 850 |
893591 | 5/22/2025 | -10 | 840 |
893593 | 5/22/2025 | -10 | 830 |
893598 | 5/22/2025 | -10 | 820 |
893600 | 5/22/2025 | -10 | 810 |
899565 | 5/22/2025 | -160 | 650 |
913031 | 5/23/2025 | 640 | 1290 |
927025 | 5/23/2025 | -60 | 1230 |
1042437 | 6/6/2025 | -80 | 1150 |
1043369 | 6/6/2025 | -60 | 1090 |
1120232 | 6/16/2025 | -20 | 1070 |
1120236 | 6/16/2025 | -20 | 1050 |
1125853 | 6/16/2025 | -20 | 1030 |
1125858 | 6/16/2025 | -20 | 1010 |
1125864 | 6/16/2025 | -20 | 990 |
1125996 | 6/16/2025 | -20 | 970 |
1126000 | 6/16/2025 | -30 | 940 |
1205340 | 6/24/2025 | -10 | 930 |
1205342 | 6/24/2025 | -10 | 920 |
1205346 | 6/24/2025 | -10 | 910 |
1205350 | 6/24/2025 | -10 | 900 |
1205356 | 6/24/2025 | -10 | 890 |
1205362 | 6/24/2025 | -10 | 880 |
1205364 | 6/24/2025 | -10 | 870 |
1205368 | 6/24/2025 | -170 | 700 |
1205381 | 6/24/2025 | -10 | 690 |
1205470 | 6/24/2025 | -10 | 680 |
1205476 | 6/24/2025 | -10 | 670 |
1222807 | 6/25/2025 | -200 | 470 |
1225247 | 6/25/2025 | -30 | 440 |
1225249 | 6/25/2025 | -30 | 410 |
1225251 | 6/25/2025 | -30 | 380 |
1225253 | 6/25/2025 | -30 | 350 |
1225255 | 6/25/2025 | -30 | 320 |
1225259 | 6/25/2025 | -30 | 290 |
1225273 | 6/25/2025 | -20 | 270 |
1241866 | 6/27/2025 | -30 | 240 |
1241881 | 6/27/2025 | -30 | 210 |
1242022 | 6/27/2025 | -30 | 180 |
1242026 | 6/27/2025 | -30 | 150 |
1242109 | 6/27/2025 | -30 | 120 |
1242115 | 6/27/2025 | -30 | 90 |
1242175 | 6/27/2025 | -20 | 70 |
1246598 | 6/27/2025 | -70 | 0 |
1265596 | 6/30/2025 | 320 | 320 |
1265604 | 6/30/2025 | 720 | 1040 |
1289533 | 7/2/2025 | -100 | 940 |
1296320 | 7/2/2025 | -90 | 850 |
1296323 | 7/2/2025 | 90 | 940 |
1324258 | 7/7/2025 | 220 | 1160 |
1396957 | 7/14/2025 | -30 | 1130 |
1396964 | 7/14/2025 | -30 | 1100 |
1396968 | 7/14/2025 | -30 | 1070 |
1396973 | 7/14/2025 | -30 | 1040 |
1467117 | 7/21/2025 | -20 | 1020 |
1467161 | 7/21/2025 | -100 | 920 |
1470236 | 7/21/2025 | -10 | 910 |
1470348 | 7/21/2025 | -10 | 900 |
1470422 | 7/21/2025 | -10 | 890 |
1470497 | 7/21/2025 | -10 | 880 |
1471788 | 7/21/2025 | -10 | 870 |
1499118 | 7/23/2025 | -300 | 570 |
1531689 | 7/25/2025 | -20 | 550 |
1531692 | 7/25/2025 | -20 | 530 |
1531697 | 7/25/2025 | -20 | 510 |
1531699 | 7/25/2025 | -20 | 490 |
1531703 | 7/25/2025 | -20 | 470 |
In the absence of desired results, here's a minimal reproducible example with the necessary steps...
CREATE TABLE [Warehouse Entry] (
whatever INT,
something INT,
qty INT
);
INSERT INTO
[Warehouse Entry]
VALUES
(1, 1, +75),
(1, 2, -50),
(1, 3, +75),
(1, 4, -50),
(1, 5, -50),
(1, 6, -50),
(1, 7, +99)
WITH
split AS
(
SELECT
*,
CASE WHEN qty < 0 THEN 0 ELSE qty END AS qty_in,
CASE WHEN qty > 0 THEN 0 ELSE -qty END AS qty_out
FROM
[Warehouse Entry]
),
stats AS
(
SELECT
*,
SUM(qty ) OVER (PARTITION BY whatever ORDER BY something) AS current_stock,
SUM(qty_in ) OVER (PARTITION BY whatever ORDER BY something) AS rolling_qty_in,
SUM(qty_in ) OVER (PARTITION BY whatever ) AS total_qty_in,
SUM(qty_out) OVER (PARTITION BY whatever ORDER BY something) AS rolling_qty_out,
SUM(qty_out) OVER (PARTITION BY whatever ) AS total_qty_out
FROM
split
)
SELECT
*,
CASE
WHEN qty < 0 THEN NULL
WHEN rolling_qty_in < total_qty_out THEN 0
ELSE rolling_qty_in - total_qty_out
END
AS qty_remaining_at_close,
CASE
WHEN qty > 0 THEN NULL
WHEN current_stock >= 0 THEN 'order_fullfilled'
WHEN rolling_qty_out <= total_qty_in THEN 'delayed_fullfillment'
WHEN rolling_qty_out > total_qty_in - qty_out THEN 'delayed_partial_fullfillment'
ELSE 'unfulfilled'
END
AS qty_out_status
FROM
stats
whatever | something | qty | qty_in | qty_out | current_stock | rolling_qty_in | total_qty_in | rolling_qty_out | total_qty_out | qty_remaining_at_close | qty_out_status |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | 1 | 75 | 75 | 0 | 75 | 75 | 249 | 0 | 200 | 0 | null |
1 | 2 | -50 | 0 | 50 | 25 | 75 | 249 | 50 | 200 | null | order_fullfilled |
1 | 3 | 75 | 75 | 0 | 100 | 150 | 249 | 50 | 200 | 0 | null |
1 | 4 | -50 | 0 | 50 | 50 | 150 | 249 | 100 | 200 | null | order_fullfilled |
1 | 5 | -50 | 0 | 50 | 0 | 150 | 249 | 150 | 200 | null | order_fullfilled |
1 | 6 | -50 | 0 | 50 | -50 | 150 | 249 | 200 | 200 | null | delayed_fullfillment |
1 | 7 | 99 | 99 | 0 | 49 | 249 | 249 | 200 | 200 | 49 | null |