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.