check for time overlaps - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

check for time overlaps

Author

Topic

dotnet
Starting Member

3 Posts

Posted - 2006-05-05 : 17:52:21

Hi,
I have these fields in servicestable
SID(int PK)
TechID(int FK)
ServiceDate (smalldatetime)
length (int 30 minutes intervals)

and TechsTable with these fields
TechID (int PK)
ScheduleID(int FK)
first_name
last_name
date_hired

and TechSchedule with these fields
ScheduleID(int PK)
mondaystart (smalldatetime)
mondayend (smalldatetime)
and so on for the rest of week

My question is how can I search for the first available tech using this structure and how can i make sure no time overlaps happens when booking a tech?

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-05 : 21:39:44

Please provide some sample data and the expected result

dotnet
Starting Member

3 Posts

Posted - 2006-05-05 : 22:27:43

Thanks for the response
first I forgot to add ServiceStartTime (smalldatetime) field into the servicestable

tables data should be like this
ServicesTable
1 | 1 | 07/07/2006 | 5:30 PM | 60
2 | 2 | 06/06/2006 | 2:30 PM | 180

TechsTable data
1 | 1 | FirstName | LastName | 12/03/2000
2 | 2 | SecondTechF |secondTechL | 07/01/2004

TechSchedule Data
1 | 8:30 AM | 5:00 PM | 9:00 AM | 5:30 PM | 12:00 PM | 8:30 PM ......
2 |12:00 PM |8:30 PM | 9:00 AM | 5:30 PM and so on ..

I want to be able to enter 07/07/2006 and 5:30 PM for the first service call in the table and only see SecondTech (2) since the call durration is 180 minutes and his shift will not be over sinces hes starting at 12:00 PM and finishing at 8:30 PM and the first tech will be offwork at that time

does that make sense?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-05-05 : 23:31:37

It is really too bad that you have a denormalized design for the TechSchedule table because that makes the query a lot more difficult.

You can find the end time of the services as:
@ServiceEnd = dateass(mi,length*30,ServiceDate)


Select
	*
from
	TechSchedule
where
	(
	mondaystart <= @ServiceDate	and
	mondayend >= @ServiceEnd
	)
	or
	(
	Tuestart <= @ServiceDate	and
	Tueend >= @ServiceEnd
	)
	or
	... and so on ...

CODO ERGO SUM

dotnet
Starting Member

3 Posts

Posted - 2006-05-05 : 23:44:01

Thanks for the response Micheal,
Would you mind telling me whats the best design in your opinion? I dont have much experiece in SQL as you can see but I would love to learn from the experts

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources