Question: I have two tables `AVAIL` and `AVAIL_TIMES`. `AVAIL` contains avail\_id, avail\_date, open\_flag. `AVAIL_TIMES` contains avail\_times\_id, Avail\_id, Start\_Time, End\_time. All date and time fields are typed as DATE

If a date is flagged in the avail open\_flag column it means that the facility is open for that date, but the times it is open is listed in avail\_times. There can be multiple time ranges for a particular day.

I need to return a list of times it is not open for that day.

For Example (one day of many)

Open times for day:

```
Start_time: 08:00 End_time 10:00
Start_time: 12:00 End_time 14:00
Start_time: 15:00 End_time 17:00

```

I want it to return something like:

```
00:00 - 07:59
10:01 - 11:59
14:01 - 14:59
17:01 - 23:59

```

I think I would be able to work through this with a temporary table and some plsql logic, but ideally this would be a pure sql solution.

Answer: 
I am not exactly sure how you want to input the date of interest (I used a bind variable, passed in as a string - but that may not be the right way for you, perhaps you want to join to your other table, etc.) - or the exact output you want. In any case, the query below demonstrates the "core" of the code you need to achieve this kind of output from the inputs.

```
alter session set nls_date_format='mm/dd/yyyy hh24:mi';

with
     avail_times ( start_time, end_time ) as (
       select to_date('06/20/2017 08:00'), to_date('06/20/2017 10:00') from dual union all
       select to_date('06/20/2017 12:00'), to_date('06/20/2017 14:00') from dual union all
       select to_date('06/20/2017 15:00'), to_date('06/20/2017 17:00') from dual
     )
select   trunc(min(start_time)) as start_time, min(start_time) as end_time
  from   avail_times
  where  trunc(start_time) = to_date(:input_date, 'mm/dd/yyyy')
union all
select   end_time, 
         lead(start_time, 1, trunc(start_time) + 1) over (order by start_time)
  from   avail_times
  where  trunc(end_time) = trunc(start_time)
order by start_time
;

START_TIME        END_TIME
----------------  ----------------
06/20/2017 00:00  06/20/2017 08:00
06/20/2017 10:00  06/20/2017 12:00
06/20/2017 14:00  06/20/2017 15:00
06/20/2017 17:00  06/21/2017 00:00

```
Another Approach. Hope this helps.

```
SELECT ID,
  START_TME,
  END_TM,
  DIFF_TM
FROM
 --Not part of SQL just to simulate the table data
  (WITH TMP AS
  (SELECT 1 ID,
    TO_DATE('06/27/2017 00:00','mm/dd/yyyy hh24:mi') START_TME,
    TO_DATE('06/27/2017 08:00','mm/dd/yyyy hh24:mi') END_TM
  FROM DUAL
  UNION ALL
  SELECT 1 ID,
    TO_DATE('06/27/2017 10:00','mm/dd/yyyy hh24:mi') START_TME,
    TO_DATE('06/27/2017 15:00','mm/dd/yyyy hh24:mi') END_TM
  FROM DUAL
  UNION ALL
  SELECT 1 ID,
    TO_DATE('06/27/2017 16:00','mm/dd/yyyy hh24:mi') START_TME,
    TO_DATE('06/27/2017 17:00','mm/dd/yyyy hh24:mi') END_TM
  FROM DUAL
  UNION ALL
  SELECT 1 id,
    to_date('06/27/2017 17:00','mm/dd/yyyy hh24:mi') start_tme,
    TO_DATE('06/27/2017 18:00','mm/dd/yyyy hh24:mi') END_TM
  FROM DUAL
  )
  --SQL start from here
SELECT TMP.*,
  LEAD(START_TME) OVER(PARTITION BY ID ORDER BY 1 DESC) next_st_tm,
  LEAD(END_TM) OVER(PARTITION BY ID ORDER BY 1 DESC) NEXT_EN_TM,
  EXTRACT( HOUR FROM TO_TIMESTAMP(LEAD(START_TME) OVER(PARTITION BY ID ORDER BY 1 DESC),'MM/DD/YYYY HH24:MI'))- EXTRACT(HOUR FROM TO_TIMESTAMP(end_tm,'MM/DD/YYYY HH24:MI')) DIFF_TM
FROM TMP
ORDER BY 1 ,
  2
  )
WHERE DIFF_TM <> 0;

```