getting rows that start and finish between dates

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

Other Forums

MS Access

getting rows that start and finish between dates

Author

Topic

skinny monkey
Starting Member

2 Posts

Posted - 2005-12-11 : 15:14:45

Hi, i have the following table structure:

SupportHours(ShID, ShDayOfWeek, ShFrom, ShTo, ShBreak)

What this table represents is the days, and the hours in that day that a person is working. Heres a sample row:

5 | Monday | 09:30:00 | 14:30:00 | 45 (in minutes)

The query i have been trying to build is to find out if a person is currently working during a period of time. If so, i do not want an insert to happen, as it will be an entry "within" a working date, if that makes sense.

So using the above data as an example, if someone tried to enter a value in the table of Monday, from 10:30 to 13:30, it will not allow it. The same rule applies if the values to be entered are Monday, from 10:00 to 19:00 - even though the end time is greater, it still overlaps an existing date.

I assume the best way to get this done is simply see if the number of rows returned is > 1, which would indicate the person is working between the specified time?

Any help is appreciated!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-11 : 20:17:41

Use 'where not exists' in your insert statement.
Check for not existance of
- ShFrom <= InputFrom <= ShTo
- ShFrom <= InputTo <= ShTo

create table #SupportHours
(
	ShID		int, 
	ShDayOfWeek	varchar(20), 
	ShFrom		datetime, 
	ShTo		datetime, 
	ShBreak		int
)

insert into #SupportHours (ShID, ShDayOfWeek, ShFrom, ShTo, ShBreak)
select	5, 'Monday', '09:30', '14:30', 45

declare
	@ShDayOfWeek	varchar(20),
	@ShFrom		datetime,
	@ShTo		datetime

select	@ShDayOfWeek	= 'Monday',
	@ShFrom		= '10:00',
	@ShTo		= '19:00'

insert into #SupportHours (ShID, ShDayOfWeek, ShFrom, ShTo, ShBreak)
select	5, @ShDayOfWeek, @ShFrom, @ShTo, 45
where	not exists (select * from #SupportHours x
			where	x.ShDayOfWeek	= @ShDayOfWeek
			and	x.ShFrom	<= @ShFrom
			and	x.ShTo		>= @ShFrom)
and	not exists (select * from #SupportHours x
			where	x.ShDayOfWeek	= @ShDayOfWeek
			and	x.ShFrom	<= @ShTo
			and	x.ShTo		>= @ShTo)

select * from #SupportHours

-----------------
[KH]
Where Am I ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-12-12 : 01:26:11

This is Access Forum and the code khtan wrote will work in SQL Server2000. Try to simulate the same in Access

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2005-12-12 : 01:42:32

oops did not realise it is under Access forum

-----------------
[KH]
Don't live your life in one day

skinny monkey
Starting Member

2 Posts

Posted - 2006-01-08 : 14:29:29

Hi, thanks for your help with this one. To get the query to work in access i used the 'Between' keyword, and used khtan's semantics.

Now i have a similar problem, so i thought i'd add it to this thread instead of starting a new one.

The problem is extended slightly that now i need to find support workers who are avaialable to work on a particuar date. Whereas in my original problem i was simply looking for a yes/no value if a worker is busy on a particular date, now i need a list of workers who are available, which involves few more tables.

The following tables are involved in this relationship:

(bold is pk, italic is fk)
SupportHours (as outlined in original post)
SupportWorkers(SwID, SwName, SwSurname, SwDOB etc)
SupportPeriodWorkers (SpwID, SwID, SpID, ShID)

The user will be entering a date in much the same way as outlined in the original post.

So heres the steps i've identified:

Get list of all support workers

Get Id's of support workers who are already working on the user-entered dates

Subtract the "busy" id's from the "available" id's

Return Id's

Where i'm stumped is getting a list of available workers - i have written a subquery that returns the id's of all the unavailable workers, which i was planning on subtracting from all the workers in the SupportWorkers table, which leaves all the available workers for the specified date. But i can't seem to figure out how to make this subtraction, or even if its the best way of performing such a query. I get a niggling feeling that i could be simplified by a left join somewhere.

Heres my query that lists the unavailable workers (with dummy dates entered for the example)


Select 
SW.SwID, SW.SwName, SW.SwSurname, SW.SwLocation, SH.ShFrom, SH.ShTo
from
SupportWorkers SW, SupportHours SH, SupportPeriodWorkers SPW 
where
(ShFrom Between #08:30:00# and #13:45:00# Or ShTo Between #08:30:00# and #13:45:00#)
and SH.ShID= SPW.ShID
and SPW.SwID = SW.SwID

Now how can i subtract from "all workers" the id's from this table?

Thanks for reading.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-09 : 03:22:48

Can you post some sample data and the result you want?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources