How To Calculate Running balance using SQL

Question

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

In modern SQL this is done using window functions which DB2 also supports — user330315
– user330315, Commented Nov 25, 2022 at 10:51

Mark Barinstein · Accepted Answer · 2022-11-25 11:17:01Z

1

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

answered Nov 25, 2022 at 11:17

Mark Barinstein

12.8k2 gold badges11 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lennart - Slava Ukraini · Accepted Answer · 2022-11-26 07:12:52Z

0

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

answered Nov 26, 2022 at 7:12

Lennart - Slava Ukraini

7,2111 gold badge23 silver badges37 bronze badges

Collectives™ on Stack Overflow

How To Calculate Running balance using SQL

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related