I know appreciation is suppose to be expressed privately, but I am in such awe over Larry's select statement, I need to publicly show my gratitude.
So ..., Larry thank you very very much, the statement works great! The study of this "simple" technique will make an SQL believer out of the most dye-hard cursor fan, I promise! Chuck Lockwood ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ LockData Technologies, Inc. 309 Main Avenue, Hawley, Pa 18428 Phone: 570-226-7340 ~ Fax: 570-226-7341 Email: [EMAIL PROTECTED] ~ http://www.lockdata.com ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Lawrence Lustig Sent: Tuesday, October 23, 2001 10:25 PM To: [EMAIL PROTECTED] Subject: Re: SQL Impossibility? Sorry for the premature send on the previous response. > Is there an SQL way to return the empty slots: > 13:00 13:30 > 14:30 15:00 SELECT End, MIN(t3.Start) + FROM YourTable t1, YourTable t3 + WHERE NOT EXISTS + (SELECT * FROM YourTable t2 WHERE t2.Start = t1.End) + AND t3.Start > t1.End + GROUP BY End Translated into "English Query Language" this says: Select all the End times that are not also in the table as a Start time (the NOT EXISTS part) and also give me the minimum Start time that is greater than the selected End time. The period from the "unstarted" end to the next highest start time is a hole in the schedule. -- Larry > Chuck Lockwood wrote: > > > > Without using a cursor, > > given a table with time values: > > > > START END > > 12:30 13:00 > > 13:30 14:00 > > 14:00 14:30 > > 15:00 15:30 > > > > Is there an SQL way to return the empty slots: > > 13:00 13:30 > > 14:30 15:00 > > > > I'm thinking a view that starts with row 2 > > joined with the table and sprinkled with a function > > or two. Something weird like that! > > > > Any hopes? > >
