This discussion is archived
5 Replies Latest reply: Oct 9, 2012 3:08 AM by Ravetd RSS

select dates with no data

961967 Newbie
Currently Being Moderated
Hello all,

I have an PL/SQL query which gives the data between the date interval submitted by the user.
The problem is that i want all the date irrespective of it has data or not

for eg: Let say date parameter is from 1-Jan-2012 to 5-Jan-2012

Now, in the database the available dates are:

1-Jan-2012
2-Jan-2012
5-Jan-2012

So as you can see here that dates 3-Jan-2012 and 4-Jan-2012 is not resulted out by the query. I want all the dates.

So the output should be like this:
1-Jan-2012
2-Jan-2012
3-Jan-2012
4-Jan-2012
5-Jan-2012

Please suggest an appropriate method to complete this task.

Thanks in advance.
  • 1. Re: select dates with no data
    hm Expert
    Currently Being Moderated
    You could use something like this:
    select to_date('01.01.2012','DD.MM.YYYY')+rownum-1 d
    from dual
    connect by to_date('01.01.2012','DD.MM.YYYY')+rownum-1 <= to_date('05.01.2012','DD.MM.YYYY');
    or when you won't repeat the constant values this one:
    with d_values as
    (
     select to_date('01.01.2012','DD.MM.YYYY') startdate,
            to_date('05.01.2012','DD.MM.YYYY') enddate 
     from dual
    )               
    select startdate+rownum-1 d
    from d_values
    connect by startdate+rownum-1 <= enddate;
    Edited by: hm on 08.10.2012 05:54
  • 2. Re: select dates with no data
    Ashu_Neo Pro
    Currently Being Moderated
    Try this..
    SQL> ed
    Wrote file afiedt.buf
    
      1  WITH DATA1 AS
      2  (SELECT TO_DATE('1-JAN-2012') dt from dual UNION ALL
      3   SELECT TO_DATE('2-JAN-2012') dt from dual UNION ALL
      4   SELECT TO_DATE('5-JAN-2012') dt from dual)
      5   SELECT (mdt + level-1) dates FROM
      6    (SELECT MAX(dt) xdt, MIN(dt) mdt FROM data1)
      7*  CONNECT BY level -1 <= xdt - mdt
    SQL> /
    
    DATES
    ---------
    01-JAN-12
    02-JAN-12
    03-JAN-12
    04-JAN-12
    05-JAN-12
    Thanks!
    Ashutosh
  • 3. Re: select dates with no data
    Ravetd Newbie
    Currently Being Moderated
    Hi,

    If you like recursivity (11g only) to generate dates :
    WITH CAL(dt)
    As
    (
    SELECT TO_DATE('1-JAN-2012', 'DD-MON-YYYY') From dual
    Union All
    Select DT+1 From CAL
    Where dt < TO_DATE('5-JAN-2012', 'DD-MON-YYYY')
    )
    Select * From CAL
    Use the generated date with a Partion By Outer Join, this kind of join will fill the gap where there is no data for a date (explanation : http://st-curriculum.oracle.com/obe/db/10g/r2/prod/bidw/outerjoin/outerjoin_otn.htm)

    Regards.
  • 4. Re: select dates with no data
    Ashu_Neo Pro
    Currently Being Moderated
    I didn't get you.
    And the query is throwing error.

    Regarding, use of
    WITH CLAUSE 
    I guess, the OP might be using a table in real time and here we used to show sample data through With clause. So the query should be generic to use it even without with clause.

    Thanks!
  • 5. Re: select dates with no data
    Ravetd Newbie
    Currently Being Moderated
    Hi,

    Since you have a date referential in your own database (Table, View, With clause ...), i think the solution is in the Partition Right Outer Join (explanation : http://st-curriculum.oracle.com/obe/db/10g/r2/prod/bidw/outerjoin/outerjoin_otn.htm)

    regards.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points