Account table details are as below:

AcctNbr BalAmt BalChgDate
111     50      04/01/2021
111     70     05/01/2021
111     100     06/01/2021
111     40     07/01/2021
111     30     07/07/2021

I can find the average daily balance by finding the ending balance for each day like below:

AcctNbr BalAmt BalChgDate
111     50      04/01/2021
111     50      04/02/2021
111     50      04/03/2021
---------------------------------------------------
111     50      04/30/2021
111     70     05/01/2021
111     70      05/02/2021
---------------------------------------------------
111     70      05/31/2021
111     100     06/01/2021
---------------------------------------------------
111     40     07/01/2021
---------------------------------------------------
111     30     07/07/2021

I am thinking of using connect by to get all the 365 days in a subquery and then making a left join of this subquery with above table. Then after this I am not able to figure how to use window function to get the average daily balance

CodePudding user response：

Didn't quite got the daily calc for July month where amount gets changed. Below is based on last value for a day in a given month within a year.

Fiddle

with date_cte (balance_dt) as 
(
select to_date('01-JAN-2021','DD-MON-YYYY')   level-1 from dual connect by level<366
), acct_bal_cte(AcctNbr,BalAmt,BalChgDate) as
(
select 111,50,to_date('04/01/2021','mm/dd/yyyy') from dual union all
select 111,70,to_date('05/01/2021','mm/dd/yyyy') from dual union all
select 111,100,to_date('06/01/2021','mm/dd/yyyy') from dual union all
select 111,40,to_date('07/01/2021','mm/dd/yyyy') from dual union all
select 111,30,to_date('07/07/2021','mm/dd/yyyy') from dual
), rn_cte as 
(
select a.*, row_number() over (partition by extract(month from a.BalChgDate) 
    order by a.BalChgDate) rn from acct_bal_cte a), cte_a as
(
select b.balance_dt balance_dt, a.rn from  rn_cte a, date_cte b
where extract(month from a.BalChgDate) = extract(month from b.balance_dt)
and extract(day from a.BalChgDate) <= extract(day from b.balance_dt)
), cte_b as
(
select balance_dt, max(rn) rn from cte_a group by balance_dt order by rn,balance_dt
)
select a.AcctNbr,a.BalAmt,b.balance_dt from rn_cte a, cte_b b
where extract(month from a.BalChgDate) = extract(month from b.balance_dt)
and a.rn = b.rn
order by b.balance_dt
    

CodePudding user response：

You can use:

WITH date_range (start_date, end_date) AS (
  SELECT DATE '2021-04-01', ADD_MONTHS(DATE '2021-04-01', 12) FROM DUAL
),
balances_within_range (acctnbr, balamt, balchgdate, range_end) AS (
  -- Prior value
  SELECT acctnbr,
         COALESCE(MAX(balamt) KEEP (DENSE_RANK LAST ORDER BY t.balchgdate), 0),
         d.start_date,
         d.end_date
  FROM   table_name t
         INNER JOIN date_range d
         ON (t.balchgdate <= d.start_date)
  GROUP BY t.acctnbr, d.start_date, d.end_date
UNION ALL
  -- Changes within range
  SELECT acctnbr,
         balamt,
         balchgdate,
         d.end_date
  FROM   table_name t
         INNER JOIN date_range d
         ON (d.start_date <= t.BalChgDate AND t.BalChgDate < d.end_date)
),
balance_ranges (acctnbr, balamt, balchgdate, nextchgdate) AS (
  SELECT AcctNbr,
         BalAmt,
         BalChgDate,
         LEAD(BalChgDate, 1, range_end)
           OVER (PARTITION BY AcctNbr ORDER BY BalChgDate)
  FROM   balances_within_range
)
SELECT acctnbr,
       ROUND(
         SUM(balamt * (nextchgdate - balchgdate))
           / SUM(nextchgdate - balchgdate),
         2
       ) AS avg_daily_balance
FROM   balance_ranges
GROUP BY acctnbr

Which, for the sample data:

CREATE TABLE table_name (AcctNbr, BalAmt, BalChgDate) AS
SELECT 111,  50, DATE '2021-04-01' FROM DUAL UNION ALL
SELECT 111,  70, DATE '2021-05-01' FROM DUAL UNION ALL
SELECT 111, 100, DATE '2021-06-01' FROM DUAL UNION ALL
SELECT 111,  40, DATE '2021-07-01' FROM DUAL UNION ALL
SELECT 111,  30, DATE '2021-07-07' FROM DUAL;

Outputs:

ACCTNBR	AVG_DAILY_BALANCE
111	40.96

db<>fiddle here