How To Calculate Running balance using SQL-CodePudding

If I have total qty = 100. and it has been shipped in 4 phases line 40, 10, 25, 25 that equals to 100. when I am running this query:

Someone Helped me with this query. I want the same runnable for DB2.

SET totalQty = -1;
SELECT 
  IF(@totalQty<0, pl.quantity, @totalQty) AS totalQty, 
  pr.invoiceqty, 
  @totalQty:=(@totalQty - pr.invoiceqty) AS balance 
FROM 
  purchaseorderline pl, replenishmentrequisition pr

I am getting result like this :

--total qty--   --invoice qty-- --balance qty--
100 40  60
100 10  90
100 25  75
100 25  70

The result I want :

--total qty--   --invoice qty-- --balance qty--
100 40  60
60  10  50
50  25  25
25  25  00

CodePudding user response：

It would be good enough, if you provided some sample data in a table form and not just what you get on it.

WITH MYTAB (PHASE_ID, QTY) AS
(
-- Your initial data as the result of
-- your base SELECT statement
VALUES
  (1, 40)
, (2, 10)
, (3, 25)
, (4, 25)
)
SELECT 
  QTY   QTY_TOT - QTY_RTOT  AS "total qty"
, QTY                       AS "invoice qty"
, QTY_TOT - QTY_RTOT        AS "balance qty"
FROM 
(
  SELECT 
    PHASE_ID
  , QTY
  -- Running total sum
  , SUM (QTY) OVER (ORDER BY PHASE_ID)  AS QTY_RTOT
  -- Total sum
  , SUM (QTY) OVER ()                   AS QTY_TOT
  FROM MYTAB
)
ORDER BY PHASE_ID

total qty	invoice qty	balance qty
100	40	60
60	10	50
50	25	25
25	25	0

CodePudding user response：

A variation of Marks answer is:

WITH MYTAB (PHASE_ID, QTY) AS
(
    -- Your initial data as the result of
    -- your base SELECT statement
    VALUES (1, 40)
         , (2, 10)
         , (3, 25)
         , (4, 25)
)
SELECT QTY_TOT  AS "total qty"
     , QTY      AS "invoice qty"
     , coalesce(lead(QTY_TOT) over (order by phase_id),0) AS "balance qty"
FROM 
( SELECT PHASE_ID
       , QTY
       -- Running total sum
       , SUM (QTY) OVER (ORDER BY PHASE_ID desc)  AS qty_tot
  FROM MYTAB
)
ORDER BY PHASE_ID

It uses lead at the outer level instead of sum over the entire window at the inner level

Fiddle