CODEWITHSUNDEEP
sqloracle-databaseplsqloracle12c
Rajesh
Rajesh

Reputation: 21

Generating Time slots between two time for today

I am editing my question little bit

I have Office Timing Table As follows.

TIME_FROM   TIME_TO     TIME_FROM1    TIME_TO1 TIME_FROM2               TIME_TO2
07:00 AM    14:00 PM    700           1400     06/08/2020 07:00:00 AM   06/08/2020 02:00:00 PM
16:00 PM    18:00 PM    1600          1800     06/08/2020 04:00:00 PM   06/08/2020 06:00:00 PM

Office Starting time is 7.00 AM and ending time is 6.00 PM with break time in between.This Times can vary based on selected Office.

Input parameters are

1.Travel Time in minutes to reach Office

2.Time slot duration in minutes

After taking Travel Time in minutes into consideration,I want to generate time slots of 15 minute(variable) interval between these time ranges like

7.00 AM

7.15 AM

7.30 AM

7.45 AM

8.00 AM .

.

.

.

1.30 PM

1.45 PM

2.00 PM

Second Shift starts here

4.00 PM

4.15 PM

4.30 PM

.

.

.

.

.

5.30 PM

5.45 PM

Scenario 1 :

Travel time needed :31 minutes

Booking attempt time 6.15 AM

Office Opening Time 7.00 AM

Required result

7.00

7.15

.

. 1.45 PM( Dont include shift ending time 2.00PM)

4.00 PM

4.15 PM

.

. 5.45 PM

Scenario 2 :

Travel time needed :31 minutes

Booking attempt time 6.45 AM

Office Opening Time 7.00 AM

Required result

7.16

7.31

.

. 1.46 PM( Dont include shift ending time 2.00PM)

4.00 PM

4.15 PM

.

. 5.45 PM

Scenario 3 :

Travel time needed :31 minutes

Booking attempt time 9.45 AM

Office Opening Time 7.00 AM

Required result

10.16

10.31

.

. 1.46 PM( Dont include shift ending time 2.00PM)

4.00 PM

4.15 PM

.

. 5.45 PM

Scenario 4 :

Travel time needed :31 minutes

Booking attempt time 3 PM

Office Second shift opening Time 4.00 PM

Required result

04.00 PM

04.15 PM

.

. 5.45 PM( Dont include shift ending time 18.00PM)

Scenario 5 :

Travel time needed :31 minutes

Booking attempt time 3.45 PM

Office Second shift opening Time 4.00 PM

Required result

04.16 PM

04.31 PM

.

. 5.46 PM( Dont include shift ending time 18.00PM)

Upvotes: 0

Views: 1244

Answers (2)

Koen Lostrie
Koen Lostrie

Reputation: 18685

WITH 
--cte to determine office hours, this is probably a table irl
office_timing (id, time_from2, time_to2) AS
(
  SELECT 1, TO_DATE('09/08/2020 07:00:00 AM','DD/MM/YYYY HH:MI:SS AM'),     TO_DATE('09/08/2020 02:00:00 PM','DD/MM/YYYY HH:MI:SS AM') FROM dual UNION ALL
  SELECT 2, TO_DATE('09/08/2020 04:00:00 PM','DD/MM/YYYY HH:MI:SS AM'),     TO_DATE('09/08/2020 06:00:00 PM','DD/MM/YYYY HH:MI:SS AM') FROM dual
)
--cte to determine when travel time to office, replace with other values to test. Make this a variable if it is an input parameter
,travel_time (travel_mins) AS
(
  SELECT 31 FROM DUAL
)
--cte to determine slot length, replace with other values to test. Make this a variable if it is an input parameter
,
slot_minutes (mins) AS
(
  SELECT 15 FROM DUAL
)
--cte to determine when query is run, replace with other values to test. Make this a variable if it is an input parameter
,run_date_tab (run_date) AS
(
SELECT 
    TO_DATE('09/08/2020 03:45:00 PM','DD/MM/YYYY HH:MI:SS AM') + travel_mins/1440    
  FROM travel_time
)

--cte to determine start time based on the query run date
--  if run date is in a time slot then take run date
--  if run date is outside time slot then take closest future start date
,
start_time_tab (qry_start_time) AS
(
  SELECT MIN(CASE 
           WHEN  t.time_from2 <= r.run_date AND t.time_to2 > r.run_date
             THEN r.run_date
           WHEN  t.time_from2 > r.run_date 
             THEN t.time_from2
         ELSE 
           NULL
         END)
    FROM run_date_tab r  
         CROSS JOIN office_timing t
        
)
,slots (slot_start_time) AS
(
  SELECT 
    s.qry_start_time +(level - 1) / ((60/m.mins)*24)
    FROM start_time_tab s CROSS JOIN slot_minutes m CONNECT BY
    level < 100
)
SELECT TO_CHAR(s.slot_start_time,'DD/MM/YYYY HH:MI:SS AM')  
  FROM slots s
       JOIN office_timing t ON t.time_from2 < s.slot_start_time AND t.time_to2 > s.slot_start_time;

Upvotes: 1

Belayer
Belayer

Reputation: 14936

Oracle provides a fairly comprehensive date (including timestamp) handling functions. Generally I live by the axiom that once a column is converted to date the only reason to convert to a string it to create a display column. There are however exceptions. One of those is (often times) when the date needs to be sliced and diced and reassembled. That is the case here to properly calculate the end time by your 'match the minute run' requirement .

You are not going to get shift break line from an SQL solution, at least I'm going to do it. You could with PL/SQL by iterating over the results. However, that is an easy task for your presentation layer. I have added a column to the final result indicating the shift. With that in mind:

with time_range (sts, ets)  as 
     ( select case when extract(hour from systimestamp) <= 07
                   then trunc(systimestamp) + interval '07:00' hour to minute 
                   else trunc(systimestamp, 'mi') 
               end sot            
            , case when extract(hour from systimestamp) <= 07
                   then trunc(systimestamp) + interval '18:00' hour to minute  
                   else to_timestamp(to_char(systimestamp,'yyyymmdd') || '18' || to_char(systimestamp,'mi'), 'yyyymmddhh24mi')  
               end eot             
         from dual
     ) 
    , office_hours (start_time, end_time) as
      ( select * from time_range
        union all
        select start_time+interval '15' minute, end_time
          from office_hours
         where start_time < end_time 
      ) 
select to_char(start_time, 'hh.mi am')
     , case when 60 * extract(hour   from cast( start_time as timestamp))
                    + extract(minute from cast( start_time as timestamp)) <= 14*60 
            then 'first shift'
            else 'second shift'
       end shift
  from office_hours;

What it does:
The work of the query is done in 2 CTEs and a select using them:

  1. time_range: determines the start and end times necessary given the current time (on the server).
  2. office_hours: a recursive CTE that computes the 15 minute intervals for start to end times.
  3. Main: Select the start of the range and determines shift.

See fiddle for example. Note: In its current form the query will always return at least 1 row. If run after the end time, it returns a row indication that time.
There are 2 additional queries with a slight modification allowing actual specification of run time instead of getting that from the system. for these I have "set the runtime" in them to 07:00 and 09:02 respectively.

Upvotes: 0

Related Questions