Oracle SQL Hierarchy Summation

Question

I have a table TRANS that contains the following records:

TRANS_ID TRANS_DT     QTY    
1        01-Aug-2020  5
1        01-Aug-2020  1
1        03-Aug-2020  2
2        02-Aug-2020  1

The expected output:

TRANS_ID TRANS_DT     BEGBAL TOTAL END_BAL 
1        01-Aug-2020  0      6     6
1        02-Aug-2020  6      0     6      
1        03-Aug-2020  6      2     8
2        01-Aug-2020  0      0     0
2        02-Aug-2020  0      1     1      
2        03-Aug-2020  1      0     1

Each trans_id starts with a beginning balance of 0 (01-Aug-2020). For succeeding days, the beginning balance is the ending balance of the previous day and so on. I can create PL/SQL block to create the output. Is it possible to get the output in 1 SQL statement?

Thanks.

Answer 1

You can use a recursive query to generate the overall date range, cross join it with the list of distinct tran_id, then bring the table with a left join. The last step is aggregation and window functions:

with all_dates (trans_dt, max_dt) as (
    select min(trans_dt), max(trans_dt) from trans group by trans_id
    union all
    select trans_dt + interval '1' day, max_dt from all_dates where trans_dt < max_dt
)
select
    i.trans_id,
    d.trans_dt,
    coalesce(sum(sum(t.qty)) over(partition by i.trans_id order by d.trans_dt), 0) - coalesce(sum(t.qty), 0) begbal,
    coalesce(sum(t.qty), 0) total,
    coalesce(sum(sum(t.qty)) over(partition by i.trans_id order by d.trans_dt), 0) endbal
from all_dates d
cross join (select distinct trans_id from trans) i
left join trans t on t.trans_id = i.trans_id and t.trans_dt = d.trans_dt
group by i.trans_id, d.trans_dt
order by i.trans_id, d.trans_dt

Answer 2

First of all, you need to generate dates, then you need to aggregate your values by TRANS_DT, and then left join your aggregated data to dates. The easiest way to get required sums is to use analitic window functions:

with dates(dt) as ( -- generating dates between min(TRANS_DT) and max(TRANS_DT) from TRANS
   select min(trans_dt) from trans
   union all
   select dt+1 from dates
   where dt+1<=(select max(trans_dt) from trans)
)
,trans_agg as ( -- aggregating QTY in TRANS
   select TRANS_ID,TRANS_DT,sum(QTY) as QTY
   from trans
   group by TRANS_ID,TRANS_DT
)
select -- using left join partition by to get data on daily basis for each trans_id:
   dt,
   trans_id,
   nvl(sum(qty) over(partition by trans_id order by dates.dt range between unbounded preceding and 1 preceding),0) as BEGBAL,
   nvl(qty,0) as TOTAL,
   nvl(sum(qty) over(partition by trans_id order by dates.dt),0) as END_BAL 
from dates
     left join trans_agg tr
          partition by (trans_id)
          on tr.trans_dt=dates.dt;

Full example with sample data:

alter session set nls_date_format='dd-mon-yyyy';
with trans(TRANS_ID,TRANS_DT,QTY) as (
select 1,to_date('01-Aug-2020'),  5 from dual union all
select 1,to_date('01-Aug-2020'),  1 from dual union all
select 1,to_date('03-Aug-2020'),  2 from dual union all
select 2,to_date('02-Aug-2020'),  1 from dual
)
,dates(dt) as ( -- generating dates between min(TRANS_DT) and max(TRANS_DT) from TRANS
   select min(trans_dt) from trans
   union all
   select dt+1 from dates
   where dt+1<=(select max(trans_dt) from trans)
)
,trans_agg as ( -- aggregating QTY in TRANS
   select TRANS_ID,TRANS_DT,sum(QTY) as QTY
   from trans
   group by TRANS_ID,TRANS_DT
)
select 
   dt,
   trans_id,
   nvl(sum(qty) over(partition by trans_id order by dates.dt range between unbounded preceding and 1 preceding),0) as BEGBAL,
   nvl(qty,0) as TOTAL,
   nvl(sum(qty) over(partition by trans_id order by dates.dt),0) as END_BAL 
from dates
     left join trans_agg tr
          partition by (trans_id)
          on tr.trans_dt=dates.dt;

Answer 3

You can as well do like this (I would assume it's a bit faster): Demo

with 
dt_between as (
  select mindt + level - 1 as trans_dt
  from (select min(trans_dt) as mindt, max(trans_dt) as maxdt from t)
  connect by level <= maxdt - mindt + 1
),
dt_for_trans_id as (
  select *
  from dt_between, (select distinct trans_id from t)
),
qty_change as (
  select distinct trans_id, trans_dt,
    sum(qty) over (partition by trans_id, trans_dt) as total,
    sum(qty) over (partition by trans_id order by trans_dt) as end_bal
  from t
  right outer join dt_for_trans_id using (trans_id, trans_dt)
)
select 
  trans_id,
  to_char(trans_dt, 'DD-Mon-YYYY') as trans_dt,
  nvl(lag(end_bal) over (partition by trans_id order by trans_dt), 0) as beg_bal, 
  nvl(total, 0) as total,
  nvl(end_bal, 0) as end_bal
from qty_change q
order by trans_id, trans_dt

dt_between returns all the days between min(trans_dt) and max(trans_dt) in your data.

dt_for_trans_id returns all these days for each trans_id in your data.

qty_change finds difference for each day (which is TOTAL in your example) and cumulative sum over all the days (which is END_BAL in your example).

The main select takes END_BAL from previous day and calls it BEG_BAL, it also does some formatting of final output.

Answer 4

Try this following script using CTE-

Demo Here

WITH CTE 
AS
(
    SELECT DISTINCT A.TRANS_ID,B.TRANS_DT
    FROM your_table A
    CROSS JOIN (SELECT DISTINCT TRANS_DT FROM your_table) B

),
CTE2
AS
(
    SELECT C.TRANS_ID,C.TRANS_DT,SUM(D.QTY) QTY
    FROM CTE C
    LEFT JOIN your_table D 
        ON C.TRANS_ID = D.TRANS_ID 
        AND C.TRANS_DT = D.TRANS_DT
    GROUP BY C.TRANS_ID,C.TRANS_DT
    ORDER BY C.TRANS_ID,C.TRANS_DT
)

SELECT F.TRANS_ID,F.TRANS_DT,
(    
    SELECT COALESCE (SUM(QTY), 0) FROM CTE2 E 
    WHERE E.TRANS_ID = F.TRANS_ID AND E.TRANS_DT < F.TRANS_DT
) BEGBAL,
(    
    SELECT COALESCE (SUM(QTY), 0) FROM CTE2 E 
    WHERE E.TRANS_ID = F.TRANS_ID AND E.TRANS_DT = F.TRANS_DT
) TOTAL ,
(    
    SELECT COALESCE (SUM(QTY), 0) FROM CTE2 E 
    WHERE E.TRANS_ID = F.TRANS_ID AND E.TRANS_DT <= F.TRANS_DT
) END_BAL 
FROM CTE2 F