sqlteradatawindow-functionsteradata-sql-assistant

SQL query to create running balance on hand?


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

Solution

  • 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