2 Replies Latest reply on Sep 4, 2014 2:29 PM by Boneist

    CONNECT BY LEVEL with dates

    945123
    945123 Sep 4, 2014 2:07 PM

      I am trying to select available time slots (hours) between two date columns.

      I'm using CONNECT BY LEVEL with a non-equal operator:

      SELECT

            (dates.start_date_time + (level - 1) /24) start_date_time,

            dates.examiner_id

            ,dates.schedule_id   

      FROM (SELECT -- bmv.schedule.common.data.AutoExaminerSchView 

             schedule_id

             ,start_date_time

             ,end_date_time

             ,examiner_id

             ,exam_status

            FROM schedule_test

            WHERE start_date_time >= TO_DATE('1/6/2014 7','MM/DD/YYYY HH24')  -- during the

          AND end_date_time <= TO_DATE('1/10/2014 15','MM/DD/YYYY HH24')  -- scheduling week

          AND exam_status = 'AVAILABLE'                               -- with available schedule

          AND ROWNUM < 2) dates

      WHERE EXTRACT(HOUR FROM NUMTODSINTERVAL(   -- before the end hour (4 pm)

        (dates.start_date_time + (level - 1) /24) - TRUNC(

        (dates.start_date_time + (level - 1) /24)), 'DAY')) < TO_NUMBER(TO_CHAR(dates.end_date_time, 'HH24'))

      CONNECT BY dates.start_date_time + (level - 1) /24 <= dates.end_date_time -- each hour between dates

      ORDER BY (dates.start_date_time + (level - 1) /24),dates.examiner_id,dates.schedule_id;

       

      The docs say:

      Operators other than the equal sign (=) are theoretically possible in CONNECT BY clauses. However, this can result in an infinite loop through the possible combinations.

      My strategy works well for one record, but when there are more than one record the results become exponentially large.

       

      The attached SQL+ script sets up some test data and runs two sample queries.

      Could someone demonstrate the correct way to select these data?

      Any pointers and help are appreciated.

      • 751 Views
      • Tags:
        • 1. Re: CONNECT BY LEVEL with dates
          Frank Kulash
          Frank Kulash Sep 4, 2014 2:22 PM (in response to 945123)

          Hi,

           

          If you use  CONNECT BY x <= y  , then at least one of the following should be true:

          • There is exactly 1 row in the table
          • You have other conditions, using the PRIOR operator, in the CONNECT BY clause

           

          Try something like this:

          CONNECT BY   start_date_time + ((level - 1) /24)  <= end_date_time      -- each hour between dates

                  AND  PRIOR schedule_id                    = schedule_id         -- or anything that is unique

                  AND  PRIOR SYS_GUID ()                    IS NOT NULL

          I hope this answers your question.

          If not, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all tables involved, and also post the results you want from that data.  Post it right in this site.

          Explain, using specific examples, how you get those results from that data.

          Always say which version of Oracle you're using (e.g., 11.2.0.2.0).

          See the forum FAQ: Re: 2. How do I ask a question on the forums?

          • 2. Re: CONNECT BY LEVEL with dates
            Boneist
            Boneist Sep 4, 2014 2:29 PM (in response to 945123)

            You just need to add some conditions in the connect by.

                       and prior schedule_id = schedule_id
           and prior dbms_random.value is not null

             

            Btw, I don't think you need the where clause; you ought to be able to amend the time period in the connect by to restrict the rows as required. You might need to add trunc(<date>, 'hh') as necessary (maybe in the select list too, if your start/end times aren't always on the hour but you need your report to be on the hour).

             

            Maybe something like:

             

            SELECT     (dates.start_date_time + (LEVEL - 1) / 24) start_date_time,
            dates.examiner_id,
            dates.schedule_id
FROM       (SELECT               -- bmv.schedule.common.data.AutoExaminerSchView
                   schedule_id,
                   start_date_time,
                   end_date_time,
                   examiner_id,
                   exam_status
            FROM   schedule_test
            WHERE      start_date_time >= TO_DATE ('1/6/2014 7', 'MM/DD/YYYY HH24') -- during the
                   AND end_date_time <= TO_DATE ('1/10/2014 15', 'MM/DD/YYYY HH24') -- scheduling week
                   AND exam_status = 'AVAILABLE'      -- with available schedule
                   ) dates
CONNECT BY dates.start_date_time + LEVEL / 24 <= dates.end_date_time -- each hour between dates
           and prior schedule_id = schedule_id
           and prior dbms_random.value is not null
ORDER BY   dates.start_date_time + LEVEL / 24,
           dates.examiner_id, dates.schedule_id;