January 28, 2009 at 4:24 pm
I am using SQL Server 2005 Express.
A table named appts is defined such:
CREATE TABLE [dbo].[apptts] (
[apptSeq] [int] IDENTITY(1,1) NOT NULL,
[apptDateTime] [datetime] NOT NULL,
[durationMin] [int] NOT NULL DEFAULT 0,
[endDateTime] [datetime] NOT NULL)
I need to write a query to find the earliest available appointment date with a certain duration. For example: find the next available date that has a 60 minute slot available.
So I try to find the minimum apptDateTime where the minutes between it and the next apptDateTime is at least 60 min.
Here is my query:
select min(A.endDateTime) endDateTime from appts A
where datediff(mi, A.endDateTime,
(select min(A2.apptDateTime) from appts A2
where A2.apptDateTime > A.endDateTime))
>= 60
This returns erroneous results.
Please help!
Test Data:
INSERT INTO [appts] ([apptDateTime],[durationMin],[endDateTime])VALUES('Feb 9 2009 8:00:00:000AM',60,'Feb 9 2009 9:00:00:000AM')
INSERT INTO [appts] ([apptDateTime],[durationMin],[endDateTime])VALUES('Feb 9 2009 9:00:00:000AM',120,'Feb 9 2009 11:00:00:000AM')
INSERT INTO [appts] ([apptDateTime],[durationMin],[endDateTime])VALUES('Feb 9 2009 11:00:00:000AM',60,'Feb 9 2009 12:00:00:000PM')
INSERT INTO [appts] ([apptDateTime],[durationMin],[endDateTime])VALUES('Feb 9 2009 12:00:00:000PM',60,'Feb 9 2009 1:00:00:000PM')
INSERT INTO [appts] ([apptDateTime],[durationMin],[endDateTime])VALUES('Feb 9 2009 1:00:00:000PM',30,'Feb 9 2009 1:30:00:000PM')
INSERT INTO [appts] ([apptDateTime],[durationMin],[endDateTime])VALUES('Feb 9 2009 1:30:00:000PM',30,'Feb 9 2009 2:00:00:000PM')
INSERT INTO [appts] ([apptDateTime],[durationMin],[endDateTime])VALUES('Feb 10 2009 9:00:00:000AM',60,'Feb 10 2009 10:00:00:000AM')
INSERT INTO [appts] ([apptDateTime],[durationMin],[endDateTime])VALUES('Feb 10 2009 10:30:00:000AM',90,'Feb 10 2009 12:00:00:000PM')
INSERT INTO [appts] ([apptDateTime],[durationMin],[endDateTime])VALUES('Feb 10 2009 2:30:00:000PM',30,'Feb 10 2009 3:00:00:000PM')
INSERT INTO [appts] ([apptDateTime],[durationMin],[endDateTime])VALUES('Feb 11 2009 1:00:00:000PM',30,'Feb 11 2009 1:30:00:000PM')
INSERT INTO [appts] ([apptDateTime],[durationMin],[endDateTime])VALUES('Feb 12 2009 8:00:00:000AM',240,'Feb 12 2009 12:00:00:000PM')
INSERT INTO [appts] ([apptDateTime],[durationMin],[endDateTime])VALUES('Feb 13 2009 8:00:00:000AM',540,'Feb 13 2009 5:00:00:000PM')
January 29, 2009 at 1:40 am
Your query doesn't return the correct results because you don't limit the join to only connect to the next record in the sequence - you're comparing each record with every later record.
The approach below uses a two-stage CTE to correctly assign a sequence to the records. The first part (appttCTE) gives the records a sequence by apptDateTime. This is used rather than the identity column because records are not guaranteed to be inserted in chronological order.
The second part (freeCTE) joins the result of the first query to the second using on the sequence number (rn) to the sequence plus one. This creates a row which is composed of the details of each appointment and the next appointment which follows it.
From there, it's just a question of comparing the end time of the appointment with the start time of the next (which you already had in your query) to work out how much free time is available between them.
It's not directly related to your question, but storing both duration and end time in your table is redundant (as one piece of information is derivable from the other) and potentially a source of data corruption if one was updated without changing the other.
If you must have both, store one (end date is probably more use) and present the other as a calculated column.
Also, the table name in your sample DDL doesn't match the table name in the query you supplied (apptts vs appts). You may need to adjust the table name my query to get it to run in your environment.
;WITH appttCTE
AS
(SELECT apptDateTime
,endDateTime
,row_number() OVER (ORDER BY apptDateTime) AS rn
FROM apptts
)
,freeCTE
AS
(SELECT a1.rn
,a1.endDateTime AS nextApptStartTime
,datediff(mi,a1.endDateTime,ISNULL(a2.apptDateTime,a1.endDateTime + 1)) AS freeTime
FROM appttCTE a1
LEFT JOIN appttCTE a2
ON a2.rn = a1.rn + 1
)
SELECT TOP 1 nextApptStartTime
FROM freeCTE
WHERE freeTime >= 60
ORDER BY rn
January 29, 2009 at 2:45 pm
Are appointments available 24 hours a day, 7 days a week?
If not, you will need to take account of the start and end of appointment availability on each day.
Also, if there is an available slot at the beginning of a particular day, eharper's query won't retrieve it.
One simple way of resolving these issues would be to insert dummy appointments covering the time intervals when appointments cannot not be booked. To distinguish between true appointments and dummy appointments, I've added a field named "dummy" of type bit.
ALTER TABLE dbo.appts ADD dummy bit NOT NULL DEFAULT(0)
GO
INSERT INTO dbo.appts (apptDateTime, durationMin, endDateTime, dummy) VALUES('2009-02-08T17:00:00', 15 * 60, '2009-02-09T08:00:00', 1)
INSERT INTO dbo.appts (apptDateTime, durationMin, endDateTime, dummy) VALUES('2009-02-09T17:00:00', 15 * 60, '2009-02-10T08:00:00', 1)
INSERT INTO dbo.appts (apptDateTime, durationMin, endDateTime, dummy) VALUES('2009-02-10T17:00:00', 15 * 60, '2009-02-11T08:00:00', 1)
INSERT INTO dbo.appts (apptDateTime, durationMin, endDateTime, dummy) VALUES('2009-02-11T17:00:00', 15 * 60, '2009-02-12T08:00:00', 1)
INSERT INTO dbo.appts (apptDateTime, durationMin, endDateTime, dummy) VALUES('2009-02-12T17:00:00', 15 * 60, '2009-02-13T08:00:00', 1)
INSERT INTO dbo.appts (apptDateTime, durationMin, endDateTime, dummy) VALUES('2009-02-13T17:00:00', 15 * 60, '2009-02-14T08:00:00', 1)
With the dummy appointments inserted, eharper's query should work fine.
January 29, 2009 at 2:55 pm
I'd build a "Schedules" table, comparable to a Calendar table, for this kind of thing.
It would contain something like ten years of dates, broken down by minutes.
Then, you could join that to an appointments table, and find spans that are any particular size pretty easily, using a gaps calculation.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 29, 2009 at 8:25 pm
eharper (and others): Thank you very much responding to me.
You are right about storing redundant data - in actuality I do not store the endDateTime, and both my table and the query are far more complex than what I shared here. (They have office hours taken into account). I was trying to keep things simple so I dummied down the example.
Could you help me understand what is the issue with my query? Does the reference to A.endDateTime in the inner query not reference the A.endDateTime in the outer query?
I cannot use CTE because it is a SQL2005 concept only, and I have to have this work on both SQL2000 and SQL2005.
Thanks again.
January 30, 2009 at 9:57 am
The following should work on SQL 2000. It does use a triangular join within the derived table so there may be performance issues if the appts table has a large number of rows.
SELECT TOP 1 T.endDateTime
FROM (
SELECT A1.endDateTime, IntervalUntilNextAppt = MIN(DATEDIFF(minute, A1.endDateTime, A2.apptDateTime))
FROM appts A1 inner join appts A2 ON (A1.endDateTime <= A2.apptDateTime)
GROUP BY A1.endDateTime
) T
WHERE T.IntervalUntilNextAppt >= 60
ORDER BY T.endDateTime
January 30, 2009 at 10:13 am
Thank you, that works.
Could you comment on the following query, which lists all the available slots?
select X.thisAppt, datediff(mi, X.thisAppt, X.nextAppt) gapTillNextAppt from
(select A1.endDateTime thisAppt, min(A2.apptDateTime) nextAppt from appts A1, appts A2
where A2.apptDateTime >= A1.endDateTime
group by A1.endDateTime) X
where datediff(mi, X.thisAppt, X.nextAppt) > 60
January 30, 2009 at 10:41 am
If the TOP 1 and ORDER BY clauses were removed from my query and the >= 60 condition changed to > 60, it would be functionaly equivalent to your query. I've checked the estimated query plans for both and they are very similar, though yours is probably faster since the DATEDIFF function is outside the triangular join.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply