sqlsql-server

SQL Calculate Running Total Against Positive Transactions


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

Solution

  • In the absence of desired results, here's a minimal reproducible example with the necessary steps...

    1. differentiate between incoming and outgoing (CASE expression)
    2. run both cumulative (rolling) and non-cumulative (total) sums on each of the above
    3. apply business logic to determine both current and final state of each transaction
    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

    fiddle