Home » SQL & PL/SQL » SQL & PL/SQL » Listing all dates in a date range (Oracle, 11g Release 2, Windows 7)
Listing all dates in a date range [message #672048] Sat, 29 September 2018 09:03 Go to next message
Subrata05005
Messages: 1
Registered: September 2018
Junior Member
Hi,
I have a table named actb_vd_bal that has following data:
    AC_NO       VALUE_DT       BALANCE
-------------   ---------       -----
1062800000250	28-JAN-18	21000
1062800000250	02-FEB-18	10501
1062800000250	11-FEB-18	20300
1062800000250	15-FEB-18	12401
I want to take balance from the table from '01-feb-2018' to '28-feb-2018'. I want a query so that I can get output as following:

    AC_NO       VALUE_DT       BALANCE
-------------   ---------       -----
1062800000250	01-FEB-18	21000
1062800000250	02-FEB-18	10501
1062800000250	03-FEB-18	10501
1062800000250	04-FEB-18	10501
1062800000250	05-FEB-18	10501
1062800000250	06-FEB-18	10501
1062800000250	07-FEB-18	10501
1062800000250	08-FEB-18	10501
1062800000250	09-FEB-18	10501
1062800000250	10-FEB-18	10501
1062800000250	11-FEB-18	20300
1062800000250	12-FEB-18	20300
1062800000250	13-FEB-18	20300
1062800000250	14-FEB-18	20300
1062800000250	15-FEB-18	12401
1062800000250	16-FEB-18	12401
1062800000250	17-FEB-18	12401
1062800000250	18-FEB-18	12401
1062800000250	19-FEB-18	12401
1062800000250	20-FEB-18	12401
1062800000250	21-FEB-18	12401
1062800000250	22-FEB-18	12401
1062800000250	23-FEB-18	12401
1062800000250	24-FEB-18	12401
1062800000250	25-FEB-18	12401
1062800000250	26-FEB-18	12401
1062800000250	27-FEB-18	12401
1062800000250	28-FEB-18	12401

[EDITED by LF: applied [code] tags]

[Updated on: Sat, 29 September 2018 09:40] by Moderator

Report message to a moderator

Re: Listing all dates in a date range [message #672049 is a reply to message #672048] Sat, 29 September 2018 09:06 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member

Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read

Report message to a moderator

Re: Listing all dates in a date range [message #672050 is a reply to message #672048] Sat, 29 September 2018 09:25 Go to previous messageGo to next message
John Watson
Messages: 8836
Registered: January 2010
Location: Global Village
Senior Member
I think what you are describing is a need for "data densification". Google it, and you will see what it means. Oracle has various techniques for this. The "partitioned outer join" (another term to Google) is one that might be right for you.

Report message to a moderator

Re: Listing all dates in a date range [message #672051 is a reply to message #672050] Sat, 29 September 2018 09:28 Go to previous messageGo to next message
Littlefoot
Messages: 21765
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Here's one option; apply additional condition to restrict the result to February (shouldn't be too difficult).

SQL> with test (ac_no, value_dt, balance) as
  2    (select 1062800000250, date '2018-01-28', 21000 from dual union all
  3     select 1062800000250, date '2018-02-02', 10501 from dual union all
  4     select 1062800000250, date '2018-02-11', 20300 from dual union all
  5     select 1062800000250, date '2018-02-15', 12401 from dual
  6    ),
  7  inter as
  8    (select ac_no,
  9       value_dt,
 10       balance,
 11       nvl(lead(value_dt) over (partition by ac_no order by value_dt),
 12           last_day(value_dt)
 13          ) lead_value_dt,
 14       --
 15       nvl(lead(value_dt) over (partition by ac_no order by value_dt),
 16           last_day(value_dt)
 17          ) - value_dt diff_days
 18     from test
 19    )
 20  select ac_no,
 21    value_dt + column_value - 1 as value_dt,
 22    balance
 23  from inter,
 24    table(cast(multiset(select level from dual
 25                        connect by level <= diff_days + 1
 26                       ) as sys.odcinumberlist))
 27  order by value_dt;

          AC_NO VALUE_DT      BALANCE
--------------- ---------- ----------
  1062800000250 28.01.2018      21000
  1062800000250 29.01.2018      21000
  1062800000250 30.01.2018      21000
  1062800000250 31.01.2018      21000
  1062800000250 01.02.2018      21000
  1062800000250 02.02.2018      21000
  1062800000250 02.02.2018      10501
  1062800000250 03.02.2018      10501
  1062800000250 04.02.2018      10501
  1062800000250 05.02.2018      10501
  1062800000250 06.02.2018      10501
  1062800000250 07.02.2018      10501
  1062800000250 08.02.2018      10501
  1062800000250 09.02.2018      10501
  1062800000250 10.02.2018      10501
  1062800000250 11.02.2018      10501
  1062800000250 11.02.2018      20300
  1062800000250 12.02.2018      20300
  1062800000250 13.02.2018      20300
  1062800000250 14.02.2018      20300
  1062800000250 15.02.2018      20300
  1062800000250 15.02.2018      12401
  1062800000250 16.02.2018      12401
  1062800000250 17.02.2018      12401
  1062800000250 18.02.2018      12401
  1062800000250 19.02.2018      12401
  1062800000250 20.02.2018      12401
  1062800000250 21.02.2018      12401
  1062800000250 22.02.2018      12401
  1062800000250 23.02.2018      12401
  1062800000250 24.02.2018      12401
  1062800000250 25.02.2018      12401
  1062800000250 26.02.2018      12401
  1062800000250 27.02.2018      12401
  1062800000250 28.02.2018      12401

35 rows selected.

SQL>

[Updated on: Sat, 29 September 2018 09:30]

Report message to a moderator

Re: Listing all dates in a date range [message #672055 is a reply to message #672051] Sat, 29 September 2018 10:24 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3227
Registered: January 2010
Location: Connecticut, USA
Senior Member
There is no need to NVL LEAD. LEAD has second and third parameters, so instead of:

     nvl(lead(value_dt) over (partition by ac_no order by value_dt),
         last_day(value_dt)
        ) lead_value_dt,

We can use

lead(value_dt,1,last_day(value_dt)) over (partition by ac_no order by value_dt)

And connect by level <= diff_days + 1 is wrong - should be connect by level <= diff_days. Otherwise you get double dates:

  1062800000250 02.02.2018      21000
  1062800000250 02.02.2018      10501

Also, if OP is on 12C:

with test (ac_no, value_dt, balance)
  as (
      select 1062800000250, date '2018-01-28', 21000 from dual union all
      select 1062800000250, date '2018-02-02', 10501 from dual union all
      select 1062800000250, date '2018-02-11', 20300 from dual union all
      select 1062800000250, date '2018-02-15', 12401 from dual
     ),
inter
  as (
      select  ac_no,
              value_dt,
              balance,
              lead(value_dt,1,last_day(value_dt) + 1) over (partition by ac_no order by value_dt) lead_value_dt
        from  test
     )
select  ac_no,
        value_dt + offset as value_dt,
        balance
  from  inter,
        lateral(
                select  level - 1 offset
                  from  dual
                  connect by level <= lead_value_dt - value_dt
               )
  order by value_dt
/

          AC_NO VALUE_DT          BALANCE
--------------- --------- ---------------
  1062800000250 28-JAN-18           21000
  1062800000250 29-JAN-18           21000
  1062800000250 30-JAN-18           21000
  1062800000250 31-JAN-18           21000
  1062800000250 01-FEB-18           21000
  1062800000250 02-FEB-18           10501
  1062800000250 03-FEB-18           10501
  1062800000250 04-FEB-18           10501
  1062800000250 05-FEB-18           10501
  1062800000250 06-FEB-18           10501
  1062800000250 07-FEB-18           10501
  1062800000250 08-FEB-18           10501
  1062800000250 09-FEB-18           10501
  1062800000250 10-FEB-18           10501
  1062800000250 11-FEB-18           20300
  1062800000250 12-FEB-18           20300
  1062800000250 13-FEB-18           20300
  1062800000250 14-FEB-18           20300
  1062800000250 15-FEB-18           12401
  1062800000250 16-FEB-18           12401
  1062800000250 17-FEB-18           12401
  1062800000250 18-FEB-18           12401
  1062800000250 19-FEB-18           12401
  1062800000250 20-FEB-18           12401
  1062800000250 21-FEB-18           12401
  1062800000250 22-FEB-18           12401
  1062800000250 23-FEB-18           12401
  1062800000250 24-FEB-18           12401
  1062800000250 25-FEB-18           12401
  1062800000250 26-FEB-18           12401
  1062800000250 27-FEB-18           12401
  1062800000250 28-FEB-18           12401

32 rows selected.

SQL>

SY.

Report message to a moderator

Re: Listing all dates in a date range [message #672057 is a reply to message #672055] Sat, 29 September 2018 15:52 Go to previous message
Littlefoot
Messages: 21765
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Aha. Thank you, Solomon, for both of your objections (as well as 12c solution). I won't edit my previous message as it would make yours irrelevant; I guess that it isn't difficult to fix my code according to your guidelines, so I'll leave it to the OP.

Report message to a moderator

Previous Topic: Query for Matching Record --> as much matches
Next Topic: PL/SQL: could not find program unit being called when calling packages
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun May 28 18:55:43 CDT 2023
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.