Skip to Main Content
  • Questions

  • Query for generating resultset with continuos dates for existing data

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sakthi.

Asked: February 06, 2017 - 6:37 pm UTC

Last updated: February 06, 2017 - 6:52 pm UTC

Version: 11.2

Viewed 1000+ times

You Asked

Dear Tom,

I have a table which stores all the account balances with dates. A row is inserted into this table only for those days where the account has transactions. The table looks as below.

create table account_balance (account varchar2(16), b_date date, balance number);

And the data in the table is as below

Insert into account_balance
(ACCOUNT, b_date, Balance)
Values
('my_acc_1234', TO_DATE('03/28/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 100);
Insert into account_balance
(ACCOUNT, b_date, balance)
Values
('my_acc_1234', TO_DATE('04/01/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 200);
Insert into account_balance
(ACCOUNT, b_date, balance)
Values
('my_acc_1234', TO_DATE('04/05/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1000);


SQL> select * from account_balance order by b_date;

ACCOUNT B_DATE BALANCE
---------------- ----------- ----------
my_acc_1234 28/03/2016 100
my_acc_1234 01/04/2016 200
my_acc_1234 05/04/2016 1000

I want to have a view that has all the calendar dates with the running balance as of that date.

as below

ACCOUNT B_DATE BALANCE
---------------- ----------- ----------
my_acc_1234 28/03/2016 100
my_acc_1234 29/03/2016 100
my_acc_1234 30/03/2016 100
my_acc_1234 31/03/2016 100
my_acc_1234 01/04/2016 200
my_acc_1234 02/04/2016 200
my_acc_1234 03/04/2016 200
my_acc_1234 04/04/2016 200
my_acc_1234 05/04/2016 1000

Whats the best way to write such a query.

Thanks,

and Connor said...

SQL> create table account_balance (account varchar2(16), b_date date, balance number);

Table created.

SQL>
SQL> Insert into account_balance
  2  (ACCOUNT, b_date, Balance)
  3  Values
  4  ('my_acc_1234', TO_DATE('03/28/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 100);

1 row created.

SQL>
SQL> Insert into account_balance
  2  (ACCOUNT, b_date, balance)
  3  Values
  4  ('my_acc_1234', TO_DATE('04/01/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 200);

1 row created.

SQL>
SQL> Insert into account_balance
  2  (ACCOUNT, b_date, balance)
  3  Values
  4  ('my_acc_1234', TO_DATE('04/05/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1000);

1 row created.

SQL>
SQL>
SQL> select * from account_balance;

ACCOUNT          B_DATE       BALANCE
---------------- --------- ----------
my_acc_1234      28-MAR-16        100
my_acc_1234      01-APR-16        200
my_acc_1234      05-APR-16       1000

3 rows selected.


So we need a table of "all" of the dates - we can do that using a WITH clause. I've just picked a couple of months to start with


SQL>
SQL> with all_the_dates as
  2    ( select date '2016-03-01' + rownum -1 dte
  3      from dual
  4      connect by level <= 61
  5    )
  6  select d.dte,
  7         a.*
  8  from  all_the_dates d,
  9        account_balance a
 10  where d.dte = a.b_date(+)
 11  order by 1;

DTE       ACCOUNT          B_DATE       BALANCE
--------- ---------------- --------- ----------
01-MAR-16
02-MAR-16
03-MAR-16
04-MAR-16
05-MAR-16
06-MAR-16
07-MAR-16
08-MAR-16
09-MAR-16
10-MAR-16
11-MAR-16
12-MAR-16
13-MAR-16
14-MAR-16
15-MAR-16
16-MAR-16
17-MAR-16
18-MAR-16
19-MAR-16
20-MAR-16
21-MAR-16
22-MAR-16
23-MAR-16
24-MAR-16
25-MAR-16
26-MAR-16
27-MAR-16
28-MAR-16 my_acc_1234      28-MAR-16        100
29-MAR-16
30-MAR-16
31-MAR-16
01-APR-16 my_acc_1234      01-APR-16        200
02-APR-16
03-APR-16
04-APR-16
05-APR-16 my_acc_1234      05-APR-16       1000
06-APR-16
07-APR-16
08-APR-16
09-APR-16
10-APR-16
11-APR-16
12-APR-16
13-APR-16
14-APR-16
15-APR-16
16-APR-16
17-APR-16
18-APR-16
19-APR-16
20-APR-16
21-APR-16
22-APR-16
23-APR-16
24-APR-16
25-APR-16
26-APR-16
27-APR-16
28-APR-16
29-APR-16
30-APR-16

61 rows selected.


Now we want to fill in the blanks - we can use an analytic function for that - I've done just the BALANCE column, but you can do the same for the others


SQL>
SQL>
SQL> with all_the_dates as
  2    ( select date '2016-03-01' + rownum -1 dte
  3      from dual
  4      connect by level <= 61
  5    )
  6  select x.*, last_value(balance ignore nulls) over ( order by dte ) flow_down_balance
  7  from
  8  (
  9    select d.dte,
 10           a.*
 11    from  all_the_dates d,
 12          account_balance a
 13    where d.dte = a.b_date(+)
 14  ) x
 15  order by 1;

DTE       ACCOUNT          B_DATE       BALANCE FLOW_DOWN_BALANCE
--------- ---------------- --------- ---------- -----------------
01-MAR-16
02-MAR-16
03-MAR-16
04-MAR-16
05-MAR-16
06-MAR-16
07-MAR-16
08-MAR-16
09-MAR-16
10-MAR-16
11-MAR-16
12-MAR-16
13-MAR-16
14-MAR-16
15-MAR-16
16-MAR-16
17-MAR-16
18-MAR-16
19-MAR-16
20-MAR-16
21-MAR-16
22-MAR-16
23-MAR-16
24-MAR-16
25-MAR-16
26-MAR-16
27-MAR-16
28-MAR-16 my_acc_1234      28-MAR-16        100               100
29-MAR-16                                                     100
30-MAR-16                                                     100
31-MAR-16                                                     100
01-APR-16 my_acc_1234      01-APR-16        200               200
02-APR-16                                                     200
03-APR-16                                                     200
04-APR-16                                                     200
05-APR-16 my_acc_1234      05-APR-16       1000              1000
06-APR-16                                                    1000
07-APR-16                                                    1000
08-APR-16                                                    1000
09-APR-16                                                    1000
10-APR-16                                                    1000
11-APR-16                                                    1000
12-APR-16                                                    1000
13-APR-16                                                    1000
14-APR-16                                                    1000
15-APR-16                                                    1000
16-APR-16                                                    1000
17-APR-16                                                    1000
18-APR-16                                                    1000
19-APR-16                                                    1000
20-APR-16                                                    1000
21-APR-16                                                    1000
22-APR-16                                                    1000
23-APR-16                                                    1000
24-APR-16                                                    1000
25-APR-16                                                    1000
26-APR-16                                                    1000
27-APR-16                                                    1000
28-APR-16                                                    1000
29-APR-16                                                    1000
30-APR-16                                                    1000

61 rows selected.


And we if need to manipulate the date range so it just covers the data in question, we can do that as well if needed.

SQL>
SQL> with
  2  all_the_dates as
  3    ( select
  4        ( select min(b_date) from account_balance ) + rownum - 1  dte
  5      from dual
  6      connect by level <= ( select max(b_date)-min(b_date) from account_balance )
  7    )
  8  select x.*, last_value(balance ignore nulls) over ( order by dte ) flow_down_balance
  9  from
 10  (
 11    select d.dte,
 12           a.*
 13    from  all_the_dates d,
 14          account_balance a
 15    where d.dte = a.b_date(+)
 16  ) x
 17  order by 1;

DTE       ACCOUNT          B_DATE       BALANCE FLOW_DOWN_BALANCE
--------- ---------------- --------- ---------- -----------------
28-MAR-16 my_acc_1234      28-MAR-16        100               100
29-MAR-16                                                     100
30-MAR-16                                                     100
31-MAR-16                                                     100
01-APR-16 my_acc_1234      01-APR-16        200               200
02-APR-16                                                     200
03-APR-16                                                     200
04-APR-16                                                     200

8 rows selected.

SQL>
SQL>


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, February 07, 2017 - 3:28 am UTC


Using Table unnesting

Rajeshwaran, Jeyabal, February 07, 2017 - 7:32 am UTC

Rather than generating an entire data using WITH clause, other option would be use TABLE un-nesting of generating a required sub-sets only.

demo@ORA11G> select * from account_balance order by b_date;

ACCOUNT          B_DATE         BALANCE
---------------- ----------- ----------
my_acc_1234      28-MAR-2016        100
my_acc_1234      01-APR-2016        200
my_acc_1234      05-APR-2016       1000

demo@ORA11G> select account, start_date + column_value - 1 as new_dt, balance
  2  from ( select account,b_date as start_date,
  3     lead(b_date) over(order by b_date) as end_date,
  4     balance
  5  from account_balance ) t1,
  6      table( cast( multiset(select level
  7      from dual
  8      connect by level <= (end_date - start_date))
  9      as sys.odcinumberlist ) ) t2 ;

ACCOUNT          NEW_DT         BALANCE
---------------- ----------- ----------
my_acc_1234      28-MAR-2016        100
my_acc_1234      29-MAR-2016        100
my_acc_1234      30-MAR-2016        100
my_acc_1234      31-MAR-2016        100
my_acc_1234      01-APR-2016        200
my_acc_1234      02-APR-2016        200
my_acc_1234      03-APR-2016        200
my_acc_1234      04-APR-2016        200
my_acc_1234      05-APR-2016       1000

9 rows selected.

demo@ORA11G>

MODEL Clause

Rajeshwaran, Jeyabal, February 07, 2017 - 10:26 am UTC 

demo@ORA11G> select * from account_balance;

ACCOUNT          B_DATE         BALANCE
---------------- ----------- ----------
my_acc_1234      28-MAR-2016        100
my_acc_1234      01-APR-2016        200
my_acc_1234      05-APR-2016       1000

demo@ORA11G> select account, x as new_dt, balance
  2  from (
  3  select rowid rid,
  4          account , b_date as start_dt,
  5          lead(b_date,1,b_date+1) over(order by b_date)-1 as end_dt,
  6          balance
  7  from account_balance
  8       )
  9  model
 10    return updated rows
 11    partition by (rid)
 12    dimension by ( to_date('01-jan-2000','dd-mon-yyyy') x )
 13    measures( account, start_dt, end_dt, balance )
 14    rules (
 15      balance[ for x from start_dt[to_date('01-jan-2000','dd-mon-yyyy')] to
 16                      end_dt[to_date('01-jan-2000','dd-mon-yyyy')]
 17                increment numtodsinterval(1,'day') ] = balance[to_date('01-jan-2000','dd-mon-yyyy')] ,
 18      account[ for x from start_dt[to_date('01-jan-2000','dd-mon-yyyy')] to
 19                      end_dt[to_date('01-jan-2000','dd-mon-yyyy')]
 20                increment numtodsinterval(1,'day') ] = account[to_date('01-jan-2000','dd-mon-yyyy')] )
 21  order by x , balance ;

ACCOUNT          NEW_DT         BALANCE
---------------- ----------- ----------
my_acc_1234      28-MAR-2016        100
my_acc_1234      29-MAR-2016        100
my_acc_1234      30-MAR-2016        100
my_acc_1234      31-MAR-2016        100
my_acc_1234      01-APR-2016        200
my_acc_1234      02-APR-2016        200
my_acc_1234      03-APR-2016        200
my_acc_1234      04-APR-2016        200
my_acc_1234      05-APR-2016       1000

9 rows selected.

demo@ORA11G>

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.