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

nbk · Accepted Answer · 2024-04-17 00:50:02Z

0

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

edited Apr 17, 2024 at 0:50

answered Apr 16, 2024 at 20:40

nbk

49.6k8 gold badges32 silver badges53 bronze badges

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

1 Comment

Fred Over a year ago

For Teradata, the default aggregation group is ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING so for a cumulative sum you want OVER( ORDER BY "date" ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )

The Impaler · Accepted Answer · 2024-04-16 20:22:49Z

0

I don't have a Teradata database handy so I ran the following query in PostgreSQL:

select x.*,
  sum(case when mi <> 0 then initial - sales + deliveries else 0 end) 
    over(order by date) as boh
from (
  select t.*, max(initial) over(order by date) as mi
  from t
) x

Result:

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

See running example at db<>fiddle.

answered Apr 16, 2024 at 20:22

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

4 Comments

Fred Over a year ago

For Teradata, window specification for both SUM and MAX will need ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to give the indicated results.

The Impaler Over a year ago

According to the Teradata Docs that's the default window frame, so there shouldn't be a need to declare it.

Andrew Over a year ago

@TheImpaler - I think you are reading the docs wrong. For Teradata, the default is ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Having said that, I always specify my windows, because Teradata is different from the ANSI here.

The Impaler Over a year ago

@Andrew I stand corrected.

Collectives™ on Stack Overflow

SQL query to create running balance on hand?

2 Answers 2

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related