Finding 'Available Time Slot' in a DateTime column

Question

Finding 'Available Time Slot' in a DateTime column

scptech

Right there with Babe

Points: 733
More actions
December 17, 2008 at 7:43 am

#126541

In an appointment scheduling app, I have a DateTime column named scheduledDate and an int column named durataionMin.
sceduledDate contains the date and time of appointment and durationMin the length in minutes.
I am looking for some easy way (using just SQL statements and no looping logic) to find an available sceduling slot of a certain duration, within working hours ( 9 - 5).
Example: find the next available time slot for 60 minutes, between 1/1/09 and 1/15/09.
Thanks in advance for your expert help on this.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

Please refer to the link in my signature for an example of how to supply table structure and sample data for this request. What version of SQL Server are you trying to write this query on?

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

scptech Right there with Babe Points: 733 More actions · Answer 2

I am trying this on SQL Svr 2000, SQL Express 2005 and SQL Express enterprise.....I have clients who use all three.

As to table structure:

CREATE TABLE [dbo].[appointments] (

[apptSeq] [int] IDENTITY (1, 1) NOT NULL ,

[scheduledDate] [datetime] NOT NULL ,

[durationMin] [int] NOT NULL ,

) ON [PRIMARY]

GO

R. van Laake SSCommitted Points: 1533 More actions · Answer 3

Hi,

I had a same kind of issue, and it is discussed and solved (at least for me) in this thread:

http://www.sqlservercentral.com/Forums/Topic619245-338-1.aspx

Maybe it is helpful you u2

J-440512 SSCertifiable Points: 6285 More actions · Answer 4

-> R. Van Laake

ak started a new thread to elaborate on your work, as suggested by Peso.

J-440512 SSCertifiable Points: 6285 More actions · Answer 5

-> ak

Posting the DDL to create the table is only part of the groundwork required to help us help you.

You must also add some test data and show what the output is expected to be.

Add some test data by providing the following statements:

[font="Courier New"]INSERT INTO Table1 (Field1, Field2, Field3)

SELECT 10, 20, 30 UNION

SELECT 11, 21, 31 UNION

SELECT 12, 22, 32 UNION

SELECT 13, 23, 33 [/font]

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 6

The basic idea is to map future precalculated timeslots against an appointment table and get first (oldest) timeslot that doesn't have a related appointment.

N 56°04'39.16"
E 12°55'05.25"