Ron and Bob, My example is flawed. The start and end times are not going to be 1/2 hour increments. Could be 15 minutes or 5 hours.
I am looking for the holes or unaccounted for time. 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 Bob Simms Sent: Tuesday, October 23, 2001 6:45 PM To: [EMAIL PROTECTED] Subject: Re: SQL Impossibility? Chuck, You might try something like this: If the table is TIME, and the columns are TIME1 and TIME2 then if you use this command: SELECT TIME1+1800, TIME1+3600 FROM TIMES WHERE TIME1+1800 NOT IN + (SELECT TIME1 FROM TIMES) you will get the right answer, with the following problems: 1. You get an extra entry (15:30 - 16:00) 2. It won't work if you have 2 or more empty slots in a row. . . . Bob At 06:21 PM 10/23/01, you 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? > >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 >~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ > ------------------------------------------------------------ Bob Simms Robert A. Simms and Co., Los Angeles 818-345-5306 Fax 818-345-5136 E-Mail: mailto:[EMAIL PROTECTED] URL: http://www.pacificnet.net/simms -------------------------------------------------------------
