Need your help!! - 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)

Need your help!!

Author

Topic

ask_sql_team
Starting Member

1 Post

Posted - 2004-02-11 : 05:42:13

HI Friends,
I need some help from you all regarding the formation of a sql query.

We have 3 tables in here. First one storing all possible time slots for people working in for a organization tblTimeSlot , we have divided time into time slots, so for each 30mins interval we add in a timeslot.

tblTimeSlots Example
------------ -------------
timeSlotID(int) (2)
timeSlotTime(varchar) (10.00am)

tblWorkerHours
the second one is tblUserHours containing the details of the start time and end time , date etc for user working in for a day.

tblWorkerHours
------------
userHrID(int)
userID(int)
userStartSlot(int)
userEndSlot(int)
userDate(datetime)

tblBooking
-----------
Now the user can be booked for time intervals in multiples of 30 mins (Ex: 60mins so user is booked for 2 slots). i.e we have 30mins=1slot. So this table stores the time slots when the user is booked in for a day.

tblBooking
---------
bookingID(int)
userID(int)
startSlot(int)
endSlot(int)
date(int)

Suppose i want to fetch all possible timeslots when a given user is available for a day.Ex: John will be working on 10th Feb from 9am-9pm(slot 0-24), and is booked between 10am-11pm(slot 3-4) and 1pm-2pm(slot 8-9).

Now if i want to fetch all possible time slots when the user is available on 10th Feb, what will the query be like.

I have tried various combinations but no success yet.

Please remember i need to achieve the following task using in a single sql query.

PLease help on this.

Regards
ask_sql_team

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2004-02-11 : 09:05:19

Here I assume that all booking slots are correct, i.e.,
they are ranged only within working slots:

create table t1 (s1 int, s2 int)
go
insert into t1
select 0, 9 union all -- working slots
select 15, 23
go
create table t2 (s1 int, s2 int)
go
insert into t2
select 2, 4 union all -- booking slots
select 6, 7 union all
select 15, 20
go

select s1, min(q) s2
from (
select s1, (select min(s1) from t2 t where t.s1>=t1.s1)-1 q
from t1
union all
select s1, s2
from t1
union all
select s2+1, (select min(s2) from t1 t where t.s2>=t2.s2)
from t2
union all
select s2+1, (select min(s1) from t2 t where t.s1>=t2.s2)-1
from t2
)z
group by s1 having min(q)>=s1
order by 1, 2

Available slots:


s1          s2          
----------- ----------- 
0           1
5           5
8           9
21          23

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources