RE: Finding Missing Time Interval using SQL

You need missing StartTimes right? You should be able to create an object of StartTimes 00:00 - thru 23:00 and outer join to it to get the missing intervals I believe. Does that help any?

Chris

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Karth Panchan Sent: Thursday, June 27, 2013 10:55 PM
To: oracle-l_at_freelists.org
Subject: Finding Missing Time Interval using SQL

All
Need help with SQL to find Missing Time Interval.

My query returns data as given below

Data1

Column StartTime EndTime
======= ======= =======

T2               9:00          18:00
T3               20:00         23:00

Data2

Column StartTime EndTime
======= ======= =======

T1               15:00          20:00
T3               20:00          07:00

Want to find Time Not on my Data in 24 hours with First StartTime on each Data Set.

Example: Data1

First StartTime: 9:00 AM (T2 record)
Add 24 hours, which will be 9:00AM Next day.

Expected Result to get missing time interval for Data1 18:00 - 20:00
23:00 - 9:00 (next day)

For Data2 Expected result
7:00 - 15:00 Next Day

Anyone come across to calculate missing time interval? Can I use PL/SQL for this like pipeline function?

Any help/directions/references I highly appreciate.

Thanks in advance.
Karth

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Fri Jun 28 2013 - 15:26:33 CEST