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
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