Home » SQL & PL/SQL » SQL & PL/SQL » To find the available time intervals for a day (Oracle 10g release 2)
To find the available time intervals for a day [message #292641] Wed, 09 January 2008 08:52 Go to next message
itgowtham
Messages: 2
Registered: January 2008
Junior Member
I have a table named Booked_court_details ,the values in it are:


sql> select * from Booked_court_details;
court_name  from_date	 	Booked_timings  	Booked_by
court1      09-jan-2008  	07:00-09:00		Smith
court1	    09-jan-2008  	12:00-18:00		John
court2      09-jan-2008  	06:30-08:30		Jones
court2	    09-jan-2008		13:00-17:00		Maria



my agenda is now to find the availability status of badminton court for a day .

if my input is:

start_date: 09-01-2008
end_date: 09-01-2008

i expect the following output:


court_name 	date		Available_Timings

court1     09-jan-2008	  [00:00-07:00],[09:00-12:00],[18:00-23:59]
court2     09-jan-2008	  [00:00-06:30],[08:30-13:00],[17:00-23:59] 



how to get the time exact time intervals subtracted from the booked time intervals as in Available_Timings column?(like the one in output shown above)

kindly let me know how to do it in PL/SQL

[Updated on: Wed, 09 January 2008 09:53]

Report message to a moderator

Re: to find time intervals for a day [message #292649 is a reply to message #292641] Wed, 09 January 2008 09:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your specification is clear as mud for me.

Post what you have, what you want, and so on as well as a test case (create table and insert statements).

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button to verify.
Also post your Oracle version (4 decimals).

Regards
Michel
Re: To find the available time intervals for a day [message #292761 is a reply to message #292641] Wed, 09 January 2008 15:58 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
The following is one method broken down step by step. Next time, please post create table and insert statements for sample data and some attempt of your own.

-- starting data:
SCOTT@orcl_11g> SELECT * FROM booked_court_details
  2  /

COURT_NAME FROM_DATE         BOOKED_TIMI BOOKED_BY
---------- ----------------- ----------- ---------
court1     09-jan-2008 00:00 07:00-09:00 Smith
court1     09-jan-2008 00:00 12:00-18:00 John
court2     09-jan-2008 00:00 06:30-08:30 Jones
court2     09-jan-2008 00:00 13:00-17:00 Maria


-- get start and end booked times:
SCOTT@orcl_11g> SELECT court_name,
  2  	    TO_DATE
  3  	      (TO_CHAR (from_date, 'dd-mon-yyyy')
  4  	       || ' '
  5  	       || SUBSTR (booked_timings, 1, INSTR (booked_timings, '-') - 1),
  6  	       'dd-mon-yyyy hh24:mi') AS start_time,
  7  	    TO_DATE
  8  	      (TO_CHAR (from_date, 'dd-mon-yyyy')
  9  	       || ' '
 10  	       || SUBSTR (booked_timings, INSTR (booked_timings, '-') + 1),
 11  	       'dd-mon-yyyy hh24:mi') AS end_time
 12  FROM   booked_court_details
 13  /

COURT_NAME START_TIME        END_TIME
---------- ----------------- -----------------
court1     09-jan-2008 07:00 09-jan-2008 09:00
court1     09-jan-2008 12:00 09-jan-2008 18:00
court2     09-jan-2008 06:30 09-jan-2008 08:30
court2     09-jan-2008 13:00 09-jan-2008 17:00


-- use lag and lead analytic functions to get times inbetween start and end booked times:
SCOTT@orcl_11g> SELECT court_name,
  2  	    NVL
  3  	      (LAG (end_time) OVER
  4  		 (PARTITION BY court_name
  5  		  ORDER BY start_time),
  6  	       TO_DATE ('09-01-2008', 'dd-mm-yyyy')) AS start_time,
  7  	    start_time AS end_time
  8  FROM   (SELECT court_name,
  9  		    TO_DATE
 10  		      (TO_CHAR (from_date, 'dd-mon-yyyy')
 11  		       || ' '
 12  		       || SUBSTR (booked_timings, 1, INSTR (booked_timings, '-') - 1),
 13  		       'dd-mon-yyyy hh24:mi') AS start_time,
 14  		    TO_DATE
 15  		      (TO_CHAR (from_date, 'dd-mon-yyyy')
 16  		       || ' '
 17  		       || SUBSTR (booked_timings, INSTR (booked_timings, '-') + 1),
 18  		       'dd-mon-yyyy hh24:mi') AS end_time
 19  	     FROM   booked_court_details)
 20  UNION
 21  SELECT court_name,
 22  	    end_time AS start_time,
 23  	    NVL
 24  	      (LEAD (start_time) OVER
 25  		 (PARTITION BY court_name
 26  		  ORDER BY start_time),
 27  	       TO_DATE ('10-01-2008', 'dd-mm-yyyy')) AS end_time
 28  FROM   (SELECT court_name,
 29  		    TO_DATE
 30  		      (TO_CHAR (from_date, 'dd-mon-yyyy')
 31  		       || ' '
 32  		       || SUBSTR (booked_timings, 1, INSTR (booked_timings, '-') - 1),
 33  		       'dd-mon-yyyy hh24:mi') AS start_time,
 34  		    TO_DATE
 35  		      (TO_CHAR (from_date, 'dd-mon-yyyy')
 36  		       || ' '
 37  		       || SUBSTR (booked_timings, INSTR (booked_timings, '-') + 1),
 38  		       'dd-mon-yyyy hh24:mi') AS end_time
 39  	     FROM   booked_court_details)
 40  /

COURT_NAME START_TIME        END_TIME
---------- ----------------- -----------------
court1     09-jan-2008 00:00 09-jan-2008 07:00
court1     09-jan-2008 09:00 09-jan-2008 12:00
court1     09-jan-2008 18:00 10-jan-2008 00:00
court2     09-jan-2008 00:00 09-jan-2008 06:30
court2     09-jan-2008 08:30 09-jan-2008 13:00
court2     09-jan-2008 17:00 10-jan-2008 00:00

6 rows selected.


-- get the format you want:
SCOTT@orcl_11g> SELECT court_name,
  2  	    TO_CHAR (start_time, 'dd-mon-yyyy') AS the_date,
  3  	    '[' || TO_CHAR (start_time, 'hh24:mi') || '-' || TO_CHAR (end_time, 'hh24:mi') || ']' AS available
  4  FROM (SELECT court_name,
  5  		    NVL
  6  		      (LAG (end_time) OVER
  7  			 (PARTITION BY court_name
  8  			  ORDER BY start_time),
  9  		       TO_DATE ('09-01-2008', 'dd-mm-yyyy')) AS start_time,
 10  		    start_time AS end_time
 11  	     FROM   (SELECT court_name,
 12  			    TO_DATE
 13  			      (TO_CHAR (from_date, 'dd-mon-yyyy')
 14  			       || ' '
 15  			       || SUBSTR (booked_timings, 1, INSTR (booked_timings, '-') - 1),
 16  			       'dd-mon-yyyy hh24:mi') AS start_time,
 17  			    TO_DATE
 18  			      (TO_CHAR (from_date, 'dd-mon-yyyy')
 19  			       || ' '
 20  			       || SUBSTR (booked_timings, INSTR (booked_timings, '-') + 1),
 21  			       'dd-mon-yyyy hh24:mi') AS end_time
 22  		     FROM   booked_court_details)
 23  	     UNION
 24  	     SELECT court_name,
 25  		    end_time AS start_time,
 26  		    NVL
 27  		      (LEAD (start_time) OVER
 28  			 (PARTITION BY court_name
 29  			  ORDER BY start_time),
 30  		       TO_DATE ('10-01-2008', 'dd-mm-yyyy')) AS end_time
 31  	     FROM   (SELECT court_name,
 32  			    TO_DATE
 33  			      (TO_CHAR (from_date, 'dd-mon-yyyy')
 34  			       || ' '
 35  			       || SUBSTR (booked_timings, 1, INSTR (booked_timings, '-') - 1),
 36  			       'dd-mon-yyyy hh24:mi') AS start_time,
 37  			    TO_DATE
 38  			      (TO_CHAR (from_date, 'dd-mon-yyyy')
 39  			       || ' '
 40  			       || SUBSTR (booked_timings, INSTR (booked_timings, '-') + 1),
 41  			       'dd-mon-yyyy hh24:mi') AS end_time
 42  		     FROM   booked_court_details))
 43  /

COURT_NAME THE_DATE    AVAILABLE
---------- ----------- -------------
court1     09-jan-2008 [00:00-07:00]
court1     09-jan-2008 [09:00-12:00]
court1     09-jan-2008 [18:00-00:00]
court2     09-jan-2008 [00:00-06:30]
court2     09-jan-2008 [08:30-13:00]
court2     09-jan-2008 [17:00-00:00]

6 rows selected.


-- concatenate the results using the aggregate method of your choice;
-- wm_concat is an undocumented 11g function and therefore unsupported;
-- you can use Tom Kyte's stragg or whatever method you like:
SCOTT@orcl_11g> COLUMN "Available_Timings" FORMAT A45
SCOTT@orcl_11g> SELECT court_name AS "court_name",
  2  	    the_date AS "date",
  3  	    wm_concat (available) AS "Available_Timings"
  4  FROM   (SELECT court_name,
  5  		    TO_CHAR (start_time, 'dd-mon-yyyy') AS the_date,
  6  		    '[' || TO_CHAR (start_time, 'hh24:mi') || '-' || TO_CHAR (end_time, 'hh24:mi') || ']' AS available
  7  	     FROM (SELECT court_name,
  8  			    NVL
  9  			      (LAG (end_time) OVER
 10  				 (PARTITION BY court_name
 11  				  ORDER BY start_time),
 12  			       TO_DATE ('09-01-2008', 'dd-mm-yyyy')) AS start_time,
 13  			    start_time AS end_time
 14  		     FROM   (SELECT court_name,
 15  				    TO_DATE
 16  				      (TO_CHAR (from_date, 'dd-mon-yyyy')
 17  				       || ' '
 18  				       || SUBSTR (booked_timings, 1, INSTR (booked_timings, '-') - 1),
 19  				       'dd-mon-yyyy hh24:mi') AS start_time,
 20  				    TO_DATE
 21  				      (TO_CHAR (from_date, 'dd-mon-yyyy')
 22  				       || ' '
 23  				       || SUBSTR (booked_timings, INSTR (booked_timings, '-') + 1),
 24  				       'dd-mon-yyyy hh24:mi') AS end_time
 25  			     FROM   booked_court_details)
 26  		     UNION
 27  		     SELECT court_name,
 28  			    end_time AS start_time,
 29  			    NVL
 30  			      (LEAD (start_time) OVER
 31  				 (PARTITION BY court_name
 32  				  ORDER BY start_time),
 33  			       TO_DATE ('10-01-2008', 'dd-mm-yyyy')) AS end_time
 34  		     FROM   (SELECT court_name,
 35  				    TO_DATE
 36  				      (TO_CHAR (from_date, 'dd-mon-yyyy')
 37  				       || ' '
 38  				       || SUBSTR (booked_timings, 1, INSTR (booked_timings, '-') - 1),
 39  				       'dd-mon-yyyy hh24:mi') AS start_time,
 40  				    TO_DATE
 41  				      (TO_CHAR (from_date, 'dd-mon-yyyy')
 42  				       || ' '
 43  				       || SUBSTR (booked_timings, INSTR (booked_timings, '-') + 1),
 44  				       'dd-mon-yyyy hh24:mi') AS end_time
 45  			     FROM   booked_court_details)))
 46  GROUP  BY court_name, the_date
 47  ORDER  BY court_name, the_date
 48  /

court_name date        Available_Timings
---------- ----------- ---------------------------------------------
court1     09-jan-2008 [00:00-07:00],[09:00-12:00],[18:00-00:00]
court2     09-jan-2008 [00:00-06:30],[08:30-13:00],[17:00-00:00]

SCOTT@orcl_11g> 

Re: To find the available time intervals for a day [message #292945 is a reply to message #292641] Thu, 10 January 2008 04:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64122
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
LOL, I had to read Barbara's post to understand the question!

Here's another solution:
drop table t purge;
create table t (
  court       varchar2(10),
  from_date   date,
  booked_time varchar2(12),
  booked_by   varchar2(10)
  )
/
alter session set nls_date_format='DD-mon-YYYY' nls_date_language=american;
insert into t values ('court1', '09-jan-2008', '07:00-09:00', 'Smith');
insert into t values ('court1', '09-jan-2008', '12:00-18:00', 'John');
insert into t values ('court2', '09-jan-2008', '06:30-08:30', 'Jones');
insert into t values ('court2', '09-jan-2008', '13:00-17:00', 'Maria');
commit;

SQL> select * from t;
COURT      FROM_DATE        BOOKED_TIME  BOOKED_BY
---------- ---------------- ------------ ----------
court1     09/01/2008 00:00 07:00-09:00  Smith
court1     09/01/2008 00:00 12:00-18:00  John
court2     09/01/2008 00:00 06:30-08:30  Jones
court2     09/01/2008 00:00 13:00-17:00  Maria

4 rows selected.

SQL> alter session set nls_date_format='DD/MM/YYYY HH24:MI';

Session altered.

SQL> col avail_times format a50
SQL> with 
  2    step1 as ( -- get from_time and to_time of booked periods
  3      select court,
  4             to_date(to_char(from_date,'DD/MM/YYYY ')||substr(booked_time,1,5),
  5                     'DD/MM/YYYY HH24:MI') from_time,
  6             to_date(to_char(from_date,'DD/MM/YYYY ')||substr(booked_time,7),
  7                     'DD/MM/YYYY HH24:MI') to_time
  8      from t
  9    ),
 10    step2 as ( -- number the booked period each day and add an extra row
 11      select court, from_time, to_time, 
 12             row_number() over (partition by court, trunc(from_time) order by from_time) rn,
 13             lag(to_time) over (partition by court, trunc(from_time) order by from_time)
 14               prev_to_time
 15      from step1
 16      union all
 17      select distinct 
 18             court, trunc(from_time)+1-1/1440, null, null, 
 19             max(to_time) over (partition by court, trunc(from_time))
 20      from step1
 21    ),
 22    step3 as ( -- get the available times
 23      select court, to_char(trunc(from_time),'DD-mon-YYYY') dat,
 24             decode(rn,
 25                    1,    to_char(from_time, '"[00:00-"HH24:MI"]"'),
 26                    null, to_char(prev_to_time, '"["HH24:MI"-24:00]"'),
 27                    to_char(prev_to_time, '"["HH24:MI') || '-' ||
 28                      to_char(from_time, 'HH24:MI"]"')
 29                   ) avail_time,
 30             decode(rn,null,count(*) over(partition by court, trunc(from_time)),rn) rn,
 31             count(*) over(partition by court, trunc(from_time)) cnt
 32      from step2
 33    )
 34  select court, dat,
 35         substr(sys_connect_by_path(avail_time,','),2) avail_times
 36  from step3
 37  where rn = cnt
 38  connect by prior court = court and prior dat = dat and prior rn = rn-1
 39  start with rn=1
 40  order by 1, 2
 41  /
COURT      DAT         AVAIL_TIMES
---------- ----------- --------------------------------------------------
court1     09-jan-2008 [00:00-07:00],[09:00-12:00],[18:00-24:00]
court2     09-jan-2008 [00:00-06:30],[08:30-13:00],[17:00-24:00]

2 rows selected.

Currently it does not work well if one period start at 00:00 or end at midnight and not at all if it crosses midnight.
Execute each step to see how it works.

Regards
Michel
Re: To find the available time intervals for a day [message #293569 is a reply to message #292945] Mon, 14 January 2008 02:29 Go to previous message
itgowtham
Messages: 2
Registered: January 2008
Junior Member
Many thanks to Barbara and Michel
Previous Topic: Difficulty in joining two tables!
Next Topic: Join diffrent tables
Goto Forum:
  


Current Time: Wed Dec 07 05:08:17 CST 2016

Total time taken to generate the page: 0.12796 seconds