Consecutive Available Blocks in a Schedule

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Consecutive Available Blocks in a Schedule

Author

Topic

smithtod
Starting Member

5 Posts

Posted - 2009-10-07 : 08:29:57

I need help writing the SQL that will find all the open time slots for an employee. I need to be able to find the all the open time slots for an appt that could be any were from 5 mins to up to 1 or more hours at a time. I am open to any design changes you may have to help me here. Also I am running on SQL Server 2005

I create my table here

CREATE TABLE saloniris.dbo.tmp ( AppID int IDENTITY, AppStart datetime, AppFinish datetime)

here is the data I loaded in to the table. This Employee Start at 9:00 a.m. and leave at 8:00 p.m. I don't want to book any appts before or after that. So I have sent the first and last appts to null

AppID AppStart AppFinish
1 null 12/30/2008 9:00:00 AM
2 12/30/2008 9:00:00 AM 12/30/2008 9:45:00 AM
3 12/30/2008 9:45:00 AM 12/30/2008 10:30:00 AM
4 12/30/2008 11:30:00 AM 12/30/2008 12:00:00 PM
5 12/30/2008 1:00:00 PM 12/30/2008 1:45:00 PM
6 12/30/2008 2:00:00 PM 12/30/2008 3:15:00 PM
7 12/30/2008 3:15:00 PM 12/30/2008 4:00:00 PM
8 12/30/2008 5:15:00 PM 12/30/2008 6:00:00 PM
9 12/30/2008 7:00:00 PM 12/30/2008 7:45:00 PM
10 12/30/2008 8:00:00 PM null

Found this code but it does not work for appts less than 1 hour and does not show all the possible start and end times for a appt.

DECLARE @AppNeed int

SET @AppNeed = 1
;WITH CTE
AS ( SELECT
*,
RowNumber = ROW_NUMBER() OVER( ORDER BY AppStart ASC )
FROM saloniris.dbo.tmp
)
SELECT TOP 10 ApptOptions = a.AppFinish
FROM CTE a
INNER JOIN CTE b
ON a.RowNumber = b.RowNumber - 1
WHERE datediff( minute, a.AppFinish, b.AppStart) >= @AppNeed

X002548
Not Just a Number

15586 Posts

Posted - 2009-10-08 : 13:27:32

working on it



SET NOCOUNT ON
GO

CREATE TABLE #myTable99 (AppID int, AppStart datetime, AppFinish datetime)
GO

INSERT INTO #myTable99(AppID, AppStart, AppFinish)
SELECT 1,  null,						'12/30/2008 9:00:00 AM'		UNION ALL
SELECT 2,  '12/30/2008 9:00:00 AM',		'12/30/2008 9:45:00 AM'		UNION ALL
SELECT 3,  '12/30/2008 9:45:00 AM',		'12/30/2008 10:30:00 AM'	UNION ALL
SELECT 4,  '12/30/2008 11:30:00 AM',	'12/30/2008 12:00:00 PM'	UNION ALL
SELECT 5,  '12/30/2008 1:00:00 PM',		'12/30/2008 1:45:00 PM'		UNION ALL
SELECT 6,  '12/30/2008 2:00:00 PM',		'12/30/2008 3:15:00 PM'		UNION ALL
SELECT 7,  '12/30/2008 3:15:00 PM',		'12/30/2008 4:00:00 PM'		UNION ALL
SELECT 8,  '12/30/2008 5:15:00 PM',		'12/30/2008 6:00:00 PM'		UNION ALL
SELECT 9,  '12/30/2008 7:00:00 PM',		'12/30/2008 7:45:00 PM'		UNION ALL
SELECT 10, '12/30/2008 8:00:00 PM',		null
GO

Yuo will need to reference 2 rows to each other...with a self join

Is there any employee code?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam