Geek Answers Handbook

Get classroom hours between dates

I am using Oracle 11g and I have this problem. I could not come up with any ideas to solve it.

I have a table with busy classes. I need to find the hours available between the datetime range. For example, I have rooms A, B and C, the table of occupied classrooms is as follows:

Classroom        start                 end  
   A         10/10/2013 10:00      10/10/2013 11:30  
   B         10/10/2013 09:15      10/10/2013 10:45  
   B         10/10/2013 14:30      10/10/2013 16:00

I need to get something like this:

with date time range between '10/10/2013 07:00' and '10/10/2013 21:15'

Classroom    avalailable_from        available_to  
   A         10/10/2013 07:00      10/10/2013 10:00  
   A         10/10/2013 11:30      10/10/2013 21:15  
   B         10/10/2013 07:00      10/10/2013 09:15  
   B         10/10/2013 10:45      10/10/2013 14:30  
   B         10/10/2013 16:00      10/10/2013 21:15  
   C         10/10/2013 07:00      10/10/2013 21:15

Is there any way to do this using sql or pl / sql?

+4
source share
2 answers

, , , Wernfried's, , . - , , , 15- : CTE, , , selects, .

with date_time_range as (
  select to_date('10/10/2013 07:00', 'DD/MM/YYYY HH24:MI') as date_start,
    to_date('10/10/2013 21:15', 'DD/MM/YYYY HH24:MI') as date_end
  from dual
),
time_slots as (
  select level as slot_num,
    dtr.date_start + (level - 1) * interval '15' minute as slot_start,
    dtr.date_start + level * interval '15' minute as slot_end
  from date_time_range dtr
  connect by level <= (dtr.date_end - dtr.date_start) * (24 * 4) -- 15-minutes
)
select * from time_slots;

57 15- . CTE date_time_range , time_slots, , ( , JDBC ).

, , , 171 (3x57) ; - , 153 15- , .

with date_time_range as (...),
time_slots as (...),
free_slots as (
  select c.classroom, ts.slot_num, ts.slot_start, ts.slot_end,
    lag(ts.slot_end) over (partition by c.classroom order by ts.slot_num)
      as lag_end,
    lead(ts.slot_start) over (partition by c.classroom order by ts.slot_num)
      as lead_start
  from time_slots ts
  cross join classrooms c
  left join occupied_classrooms oc on oc.classroom = c.classroom
    and not (oc.occupied_end <= ts.slot_start 
      or oc.occupied_start >= ts.slot_end)
  where oc.classroom is null
)
select * from free_slots;

. ; , , :

with date_time_range as (...),
time_slots as (...),
free_slots as (...),
free_slots_extended as (
  select fs.classroom, fs.slot_num,
    case when fs.lag_end is null or fs.lag_end != fs.slot_start
      then fs.slot_start end as slot_start,
    case when fs.lead_start is null or fs.lead_start != fs.slot_end
      then fs.slot_end end as slot_end
  from free_slots fs
)
select * from free_slots_extended
where (fse.slot_start is not null or fse.slot_end is not null);

12 . ( where 141 153 , , ):

CLASSROOM   SLOT_NUM SLOT_START       SLOT_END       
--------- ---------- ---------------- ----------------
A                  1 2013-10-10 07:00                  
A                 12                  2013-10-10 10:00 
A                 19 2013-10-10 11:30                  
A                 57                  2013-10-10 21:15 
B                  1 2013-10-10 07:00                  
B                  9                  2013-10-10 09:15 
B                 16 2013-10-10 10:45                  
B                 30                  2013-10-10 14:30 
B                 37 2013-10-10 16:00                  
B                 57                  2013-10-10 21:15 
C                  1 2013-10-10 07:00                  
C                 57                  2013-10-10 21:15

, , , :

...
select distinct fse.classroom,
  nvl(fse.slot_start, lag(fse.slot_start)
    over (partition by fse.classroom order by fse.slot_num)) as slot_start,
  nvl(fse.slot_end, lead(fse.slot_end)
    over (partition by fse.classroom order by fse.slot_num)) as slot_end
from free_slots_extended fse
where (fse.slot_start is not null or fse.slot_end is not null)

:

with date_time_range as (
  select to_date('10/10/2013 07:00', 'DD/MM/YYYY HH24:MI') as date_start,
    to_date('10/10/2013 21:15', 'DD/MM/YYYY HH24:MI') as date_end
  from dual
),
time_slots as (
  select level as slot_num,
    dtr.date_start + (level - 1) * interval '15' minute as slot_start,
    dtr.date_start + level * interval '15' minute as slot_end
  from date_time_range dtr
  connect by level <= (dtr.date_end - dtr.date_start) * (24 * 4) -- 15-minutes
),
free_slots as (
  select c.classroom, ts.slot_num, ts.slot_start, ts.slot_end,
    lag(ts.slot_end) over (partition by c.classroom order by ts.slot_num)
      as lag_end,
    lead(ts.slot_start) over (partition by c.classroom order by ts.slot_num)
      as lead_start
  from time_slots ts
  cross join classrooms c
  left join occupied_classrooms oc on oc.classroom = c.classroom
    and not (oc.occupied_end <= ts.slot_start
      or oc.occupied_start >= ts.slot_end)
  where oc.classroom is null
),
free_slots_extended as (
  select fs.classroom, fs.slot_num,
    case when fs.lag_end is null or fs.lag_end != fs.slot_start
      then fs.slot_start end as slot_start,
    case when fs.lead_start is null or fs.lead_start != fs.slot_end
      then fs.slot_end end as slot_end
  from free_slots fs
)
select distinct fse.classroom,
  nvl(fse.slot_start, lag(fse.slot_start)
    over (partition by fse.classroom order by fse.slot_num)) as slot_start,
  nvl(fse.slot_end, lead(fse.slot_end)
    over (partition by fse.classroom order by fse.slot_num)) as slot_end
from free_slots_extended fse
where (fse.slot_start is not null or fse.slot_end is not null)
order by 1, 2;

:

CLASSROOM SLOT_START       SLOT_END       
--------- ---------------- ----------------
A         2013-10-10 07:00 2013-10-10 10:00 
A         2013-10-10 11:30 2013-10-10 21:15 
B         2013-10-10 07:00 2013-10-10 09:15 
B         2013-10-10 10:45 2013-10-10 14:30 
B         2013-10-10 16:00 2013-10-10 21:15 
C         2013-10-10 07:00 2013-10-10 21:15

SQL Fiddle.

+4

, " , ". ( 15 ). , .

PL/SQL:

CREATE TABLE Table1
    (Classroom VARCHAR2(10), start_ts DATE, end_ts DATE);    
INSERT INTO Table1 VALUES ('A', TIMESTAMP '2013-01-10 10:00:00', TIMESTAMP '2013-01-10 11:30:00');
INSERT INTO Table1 VALUES ('B', TIMESTAMP '2013-01-10 09:15:00', TIMESTAMP '2013-01-10 10:45:00');
INSERT INTO Table1 VALUES ('B', TIMESTAMP '2013-01-10 14:30:00', TIMESTAMP '2013-01-10 16:00:00');



WITH all_rooms AS
    (SELECT CHR(64+LEVEL) AS ROOM FROM dual CONNECT BY LEVEL <= 3),
all_times AS
    (SELECT  CAST(TIMESTAMP '2013-01-10 07:00:00' + (LEVEL-1) * INTERVAL '15' MINUTE AS DATE) AS TIMES, LEVEL AS SLOT
    FROM DUAL
    CONNECT BY TIMESTAMP '2013-01-10 07:00:00' + (LEVEL-1) * INTERVAL '15' MINUTE <= TIMESTAMP '2013-01-10 21:15:00'),
all_free_slots AS
    (SELECT ROOM, TIMES, SLOT, 
        CASE SLOT-LAG(SLOT, 1, 0) OVER (PARTITION BY ROOM ORDER BY SLOT) 
            WHEN 1 THEN 0
            ELSE 1
        END AS NEW_WINDOW 
    FROM all_times
        CROSS JOIN all_rooms
    WHERE NOT EXISTS 
        (SELECT 1 FROM TABLE1 WHERE ROOM = CLASSROOM AND TIMES BETWEEN START_TS + INTERVAL '1' MINUTE AND END_TS - INTERVAL '1' MINUTE)),
free_time_windows AS
    (SELECT ROOM, TIMES, SLOT, 
        SUM(NEW_WINDOW) OVER (PARTITION BY ROOM ORDER BY SLOT) AS WINDOW_ID
    FROM all_free_slots)
SELECT ROOM, 
    TO_CHAR(MIN(TIMES), 'yyyy-mm-dd hh24:mi') AS free_time_start, 
    TO_CHAR(MAX(TIMES), 'yyyy-mm-dd hh24:mi') AS free_time_end
FROM free_time_windows
GROUP BY ROOM, WINDOW_ID
HAVING MAX(TIMES) - MIN(TIMES) > 0
ORDER BY ROOM, 2;


ROOM FREE_TIME_START    FREE_TIME_END
---- ----------------------------------
A    2013-01-10 07:00   2013-01-10 10:00
A    2013-01-10 11:30   2013-01-10 21:15
B    2013-01-10 07:00   2013-01-10 09:15
B    2013-01-10 10:45   2013-01-10 14:30
B    2013-01-10 16:00   2013-01-10 21:15
C    2013-01-10 07:00   2013-01-10 21:15

, , .

WITH all_rooms AS
    (SELECT CHR(64+LEVEL) AS ROOM FROM dual CONNECT BY LEVEL <= 3),
all_times AS
    (SELECT  CAST(TIMESTAMP '2013-01-10 07:00:00' + (LEVEL-1) * INTERVAL '15' MINUTE AS DATE) AS TIMES, LEVEL AS SLOT
    FROM DUAL
    CONNECT BY TIMESTAMP '2013-01-10 07:00:00' + (LEVEL-1) * INTERVAL '15' MINUTE <= TIMESTAMP '2013-01-10 21:15:00')
SELECT ROOM, TIMES, SLOT, 
    CASE SLOT-LAG(SLOT, 1, 0) OVER (PARTITION BY ROOM ORDER BY SLOT) 
        WHEN 1 THEN 0
        ELSE 1
    END AS NEW_WINDOW 
FROM all_times
    CROSS JOIN all_rooms
WHERE NOT EXISTS (SELECT 1 FROM TABLE1 WHERE ROOM = CLASSROOM AND TIMES BETWEEN START_TS + INTERVAL '1' MINUTE AND END_TS - INTERVAL '1' MINUTE)
ORDER BY ROOM, SLOT
+2

More articles:


All Articles