BigQuery SQL running arithmetic balance view

user_10465862 0
Viewed 54

I have 2 tables which I'd like to join to get to the desired view below:

Table 1: Contracts

Contracts* Quantity
Contract 1 10
Contract 2 20
Contract 3 30

Table 2: Deliveries

Date Vehicle* Contracts Delivered
01/01/2022 Vehicle A Contract 1 5
02/01/2022 Vehicle B Contract 1 5
02/01/2022 Vehicle B Contract 2 10
03/01/2022 Vehicle C Contract 2 10
03/01/2022 Vehicle C Contract 3 15

Desired view (shows the current outstanding quantity, to be offset against the delivery for that particular contract)

* - Denotes primary key for that table

Date Vehicle Contracts Quantity Outstanding Delivered Balance
01/01/2022 Vehicle A Contract 1 10 10 5 5
02/01/2022 Vehicle B Contract 1 10 5 5 0
02/01/2022 Vehicle B Contract 2 20 20 10 10
03/01/2022 Vehicle C Contract 2 20 10 10 0
03/01/2022 Vehicle C Contract 3 30 30 15 15 
select

  deliveries.date,
  deliveries.vehicle, 
  contracts.contracts,
  contracts.quantity,
  outstanding (?),
  contracts.quantity - contracts.delivered as delivered
  balance (?)

from contracts
left join deliveries on deliveries.contracts = contracts.contracts

I am not sure how to write the expression for outstanding and balance.

Does it involve sum(quantity - delivered) over (partition by vehicle order by contract) ?

google-bigquery sql
1 Answers

Consider below approach

select date, vehicle, contracts, quantity, 
  quantity - ifnull(sum(delivered) over prev, 0) outstanding, 
  delivered, 
  quantity - delivered - ifnull(sum(delivered) over prev, 0)  balance
from deliveries d 
left join contracts c
using (contracts)
window prev as (partition by contracts order by date rows between unbounded preceding and 1 preceding)

if applied to sample data in your question - output is

enter image description here

user_52219440
Related