Need your help!!!

Question

Need your help!!!

Karan Bajoria

Old Hand

Points: 375
More actions
February 11, 2004 at 3:47 am

#107069

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

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

yellowleonard SSC-Addicted Points: 402 More actions · Answer 1

Just thinking quickly, I might have missed something.

If I've read your columns correctly (I'm assuming that the date in tblBooking is actually a datetime not an int) this should return you worker hours and time slot data for every worker for today. To make it more specific replace the W.userID Like '%' with W.userID = (id) or W.userID Like '(id)'. I've used getDate() because if I know that it will return a date that SQL likes whereas sometimes there can be some confusion between the months and days if you write '10/02/2004' or '02/10/2004' or '2004-02-10', etc

Hope it helps

Select

tblWorkerHours.*,

tblTimeSlots.*

From

--Get all the workers' time slots for today

tblWorkerHours W Join tblTimeSlot T On T.timeSlotID Between W.userStartSlot And W.userEndSlot

And W.userDate = getDate()

And W.userID Like '%'

Where

--exclude booked time slots

T.timeSlotID Not In (

Select

--Get all booked time slots for the worker

T2.timeSlotID

From

tblTimeSlots T2 Join tblBooking B On T2.timeSlotID Between B.startSlot And B.endSlot

And B.userID = W.userID

--Date restriction

And B.[date] = W.userDate

)

Karan Bajoria Old Hand Points: 375 More actions · Answer 2

HI lenmcmanotony,

Thanks for your kind reply.

This will work fine, but there lies another problem here that what if i m looking to get available time slots when the user is available between 10am- 2pm. Here we need to get the first time slot when the user is available.

Regards

Karan

Antares686 SSC Guru Points: 125444 More actions · Answer 3

I think I understand here, however are you attached to your structure and I believe I can offer a simpler, cleaner solution?

Richard Lesh Right there with Babe Points: 761 More actions · Answer 4

Simply add

AND T.timeSlotID >= (select timeSlotID from tblTimeSlots where timeSlotTime = '10.00am')

AND T.timeSlotID < (select timeSlotID from tblTimeSlots where timeSlotTime = '2.00pm')

to the query that lenmcmanotony provided to you. It works assuming that you have numbered your TimeSlots consecutively.

johnsonj SSCrazy Points: 2159 More actions · Answer 5

I agree with Antares686. If you are not attached to the structure, you might want to re-work it some. It could be more efficient and easier to code the SQL.

Joe Johnson
NETDIO,LLC.

Karan Bajoria Old Hand Points: 375 More actions · Answer 6

HI Friends,

The code given by lenmcmanotony worked for me. Thanx lenmcmanotony . . Further i want to make a slight modification, i am trying to now fetch only the 1st available time slots instead of all slots when the user is available.

Lets c how it shapes up

Regards

Karan

yellowleonard SSC-Addicted Points: 402 More actions · Answer 7

This should cover it. I've included Richard Lesh's conditions and made two select statements. The first one gets you a the first time slot for a specific worker based on a variable (or you could hardcode it if you really wanted). The second one should get you the first free time slot id for every worker. Sorry if I've left it a bit too late with my reply.

Select Top 1

W.*,

T.*

From

--Get all the workers' time slots for today

tblWorkerHours W Join tblTimeSlot T On T.timeSlotID Between W.userStartSlot And W.userEndSlot

And W.userDate = getDate()

And W.userID Like '%'

AND T.timeSlotID >= (select timeSlotID from tblTimeSlots where timeSlotTime = '10.00am')

AND T.timeSlotID < (select timeSlotID from tblTimeSlots where timeSlotTime = '2.00pm')

Where

--exclude booked time slots

T.timeSlotID Not In (

Select

--Get all booked time slots for the worker

T2.timeSlotID

From

tblTimeSlots T2 Join tblBooking B On T2.timeSlotID Between B.startSlot And B.endSlot

And B.userID = W.userID

--Date restriction

And B.[date] = W.userDate

)

And W.userID = <insert variable>

Order By T.timeSlotID Ascending --So that minimum is at top of result set

Select

Min(T.timeSlotID) From --Get all the workers' time slots for today tblWorkerHours W Join tblTimeSlot T On T.timeSlotID Between W.userStartSlot And W.userEndSlot And W.userDate = getDate() And W.userID Like '%' AND T.timeSlotID >= (select timeSlotID from tblTimeSlots where timeSlotTime = '10.00am') AND T.timeSlotID < (select timeSlotID from tblTimeSlots where timeSlotTime = '2.00pm') Where --exclude booked time slots T.timeSlotID Not In ( Select --Get all booked time slots for the worker T2.timeSlotID From tblTimeSlots T2 Join tblBooking B On T2.timeSlotID Between B.startSlot And B.endSlot And B.userID = W.userID --Date restriction And B.[date] = W.userDate )

Group By W.userID --Grouping by worker so that the select min(timeslot) gives you each individual worker's min time slot

Order By T.timeSlotID Ascending --So that minimum is at top of result set