SQL query to create running balance on hand?

Question

In Teradata, I have a query that returns date, initial, sales, and deliveries.

SELECT
 date, initial, sales, deliveries
FROM sample

I would like to create a new column that acts as a running balance on hand, where the calculation is BOH = initial - sales + deliveries. However, the boh calculation should not begin until the first date when initial is non-zero.

Below is ideally what the column boh should return. How would I go about structuring a query for this?

date  initial  sales  deliveries   boh
---------------------------------------
01-01    0       2        4         0       
01-02    0       0        0         0
01-03    3       1        0         2
01-04    0       4        8         6
01-05    0       2        2         6
01-06    0       1        3         8

Answer 1

You could calculate the earliest date and use it to dertermine if a calculation should be performed.

SELECT 
"date", "initial", "sales", 
SUM(CASE WHEN "date" >= (SELECT MIN("date") FROM tmytable WHERE initial > 0) 
  then  ("initial" - sales + deliveries) 
  ELSE 0 END) OVER(ORDER bY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) boh
FROM tmYtable

date	initial	sales	boh
01-01	0	2	0
01-02	0	0	0
01-03	3	1	2
01-04	0	4	6
01-05	0	2	6
01-06	0	1	8

SELECT 6

fiddle