5 Replies Latest reply: Oct 9, 2012 5:08 AM by Ravetd RSS

    select dates with no data

    961967
      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
          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
            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
              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
                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
                  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.