Finding Missing Time Interval using SQL

From: Karth Panchan <keyantech_at_gmail.com>
Date: Thu, 27 Jun 2013 23:55:11 -0400
Message-ID: <CAFj-6g-hUxcGaV3WyU3fDnqdbD0W7J6-URaakFDX_Cfxvv7jjw_at_mail.gmail.com>

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

Received on Fri Jun 28 2013 - 05:55:11 CEST