November 8, 2011 at 5:59 am
Can anyone help with the following?
I have a table that stores appointments and another that stores
peoples availability.
People can specify dates and times they are available and when they're
not available.
I want to write a query that checks who is available for each
appointment based on them being available for over 50% of the
appointment.
For example, if i have the following appointments from
14:00-16:00, 9:00-17:30 and 20:00-06:00, and different people
available from 8:00-20:00, 12:00-18:00, 10:00-14:00, 12:00-16:00 (but
unavailable from 08:00-12:00), 17:00-23:00, All Day, and another All
Day but unavailable from 16:00-23:59, how do i work out which people
are available for my appointments for over 50% of the appointments.
Non-availability supersedes availability.
Thanks in advance for any suggestions.
November 8, 2011 at 6:05 am
What do you have so far in terms of the query? You may want to consider a temporary table where to slice the proposed meeting time in let's say one minute chunks then just assign already booded time and count free chunks for availability.
Please, do not construct this as my endorsement of the solution. Never seen a system designed to overbook resources on purpose before 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.November 8, 2011 at 6:05 am
Dan Williams-449672 (11/8/2011)
Can anyone help with the following?I have a table that stores appointments and another that stores
peoples availability.
People can specify dates and times they are available and when they're
not available.
I want to write a query that checks who is available for each
appointment based on them being available for over 50% of the
appointment.
For example, if i have the following appointments from
14:00-16:00, 9:00-17:30 and 20:00-06:00, and different people
available from 8:00-20:00, 12:00-18:00, 10:00-14:00, 12:00-16:00 (but
unavailable from 08:00-12:00), 17:00-23:00, All Day, and another All
Day but unavailable from 16:00-23:59, how do i work out which people
are available for my appointments for over 50% of the appointments.
Non-availability supersedes availability.
Thanks in advance for any suggestions.
so much of it depends on the actual layout of the table.
do you have a start datedatetime and an enddatetime , and is it one appointment per row?
it seems to me that you could do the datediff in minutes for each appointment, and sum them together...
then divide by the total time per day, right? 14400 minutes in a 24 hour day, but is your biz, say 9:00 to 5:00?
you show us the actual table CREATE TABLE definition, as well as a couple of rows of sample data, and we'll be able to whip up an example.
Lowell
November 8, 2011 at 6:07 am
PaulB-TheOneAndOnly (11/8/2011)
Never seen a system designed to overbook resources on purpose before
Don't airlines do this routinely on the basis that some people will cancel at the last minute or simply not show up?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 8, 2011 at 6:13 am
SQL Kiwi (11/8/2011)
PaulB-TheOneAndOnly (11/8/2011)
Never seen a system designed to overbook resources on purpose beforeDon't airlines do this routinely on the basis that some people will cancel at the last minute or simply not show up?
Not in the sense of over-booking partial flight legs like this system does - nope. 😀
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.November 8, 2011 at 6:22 am
PaulB-TheOneAndOnly (11/8/2011)
Not in the sense of over-booking partial flight legs like this system does
Fair enough. Booking part of a flight is an intriguing idea though - parachutes supplied?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
November 8, 2011 at 6:33 am
my appointments table contains smalldatetimes for start and end datetimes. one appointment per row
appointment
-------------
id
startdatetime
enddatetime
peopleavailability
----------------
id
startdatetime
enddatetime
availtype (Available,Sick,On holiday, etc)
I have a calendar table that contains dates and a times table that contains hours and minutes.
So far i have created a table function to return minute records between 2 datetimes, then was trying to do a join on the respective tables, but it's not particularly quick
here's my function
CREATEFUNCTION [dbo].[fnTimes] (@dateFrom smalldatetime, @dateTo smalldatetime)
RETURNS TABLE
AS
RETURN (
select dt + Times.Time as [DateTime]
from calendar
cross join Times
where dt >= cast(@datefrom as date) and dt <= @dateto and
dt+times.time >= @datefrom and dt+times.time <= @dateto
)
Then to return the minutes for an appointment, i run the following query
SELECT avail.[Datetime]
FROM Appointment a
CROSS apply fntimes(a.startdatetime, a.enddatetime)) AS [Avail]
so for an appointment for today from 12:00 - 12:05, the query returns
08/11/2011 12:00
08/11/2011 12:02
08/11/2011 12:03
08/11/2011 12:04
08/11/2011 12:05
now i was trying to do the same for the people availability table and inner join against the above, but the performance is very slow as i may have several people with lots of availabilty.
any ideas or am i heading down the wrong road?
November 8, 2011 at 7:42 am
i missed out 12:01 and that there's a PersonId in my peopleavailability/resources table
November 8, 2011 at 9:08 am
Dan Williams-449672 (11/8/2011)
any ideas or am i heading down the wrong road?
You're definitely headed down the wrong road, but without easily consumable sample data and expected results, you're unlikely to get much help.
Check out Jeff Moden's article Forum Etiquette: How to post data/code on a forum to get the best help[/url]
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 8, 2011 at 9:55 am
ok thanks for the reply. I'll draft something up and post it asap.
November 8, 2011 at 12:08 pm
ok here you go
CREATE TABLE [dbo].[Appointments](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Start] [smalldatetime] NOT NULL,
[End] [smalldatetime] NOT NULL,
CONSTRAINT [PK_Appointments] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[Availability](
[Id] [int] IDENTITY(1,1) NOT NULL,
[PersonId] [int] NOT NULL,
[AvailFrom] [smalldatetime] NOT NULL,
[AvailTo] [smalldatetime] NOT NULL,
[AvailType] [varchar](50) NOT NULL,
CONSTRAINT [PK_Availability] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT INTO Appointments (Start, [End])
SELECT 'Nov 16 2011 2:00PM','Nov 16 2011 4:00PM' UNION ALL
SELECT 'Nov 16 2011 9:00AM','Nov 16 2011 5:30PM' UNION ALL
SELECT 'Nov 16 2011 8:00PM','Nov 17 2011 6:00AM'
GO
insert into Availability (PersonId, AvailFrom, AvailTo, AvailType)
SELECT '1','Nov 16 2011 8:00AM','Nov 16 2011 8:00PM','Available' UNION ALL
SELECT '2','Nov 16 2011 12:00PM','Nov 16 2011 6:00PM','Available' UNION ALL
SELECT '3','Nov 16 2011 10:00AM','Nov 16 2011 2:00PM','Available' UNION ALL
SELECT '4','Nov 16 2011 12:00PM','Nov 16 2011 4:00PM','Available' UNION ALL
SELECT '4','Nov 16 2011 8:00AM','Nov 16 2011 12:00PM','Unavailable' UNION ALL
SELECT '5','Nov 16 2011 5:00PM','Nov 16 2011 11:00PM','Available' UNION ALL
SELECT '6','Nov 16 2011 12:00AM','Nov 16 2011 11:59PM','Available' UNION ALL
SELECT '7','Nov 16 2011 12:00AM','Nov 16 2011 11:59PM','Available' UNION ALL
SELECT '7','Nov 16 2011 4:00PM','Nov 16 2011 11:59PM','Unavailable'
GO
Now i need to find out which PersonId's are available for over 50% of each of my appointments.
November 8, 2011 at 12:10 pm
SQL Kiwi (11/8/2011)
PaulB-TheOneAndOnly (11/8/2011)
Not in the sense of over-booking partial flight legs like this system doesFair enough. Booking part of a flight is an intriguing idea though - parachutes supplied?
:w00t: shhhhhhh... don't put that kind of ideas on their brains Paul.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.November 8, 2011 at 1:59 pm
SQL Kiwi (11/8/2011)
PaulB-TheOneAndOnly (11/8/2011)
Not in the sense of over-booking partial flight legs like this system doesFair enough. Booking part of a flight is an intriguing idea though - parachutes supplied?
That assumes the half you're traveling ends before the landing. What if you want to overbook someone for the second half? REALLY powerful pogo sticks?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
November 8, 2011 at 2:03 pm
... and just TRY getting a parachute through security at a U.S. airport.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
November 8, 2011 at 2:08 pm
Finding overlap is easy.
If the AvailableTo is later than the AppointmentStart, and the AvailableFrom is earlier than the AppointmentEnd, then they have an overlap. That's a simple join condition.
What you'd need to do is determine what "half" means in this case (mainly in terms of accuracy). If minutes is close enough, then calculate the datediff in minutes of the overlap, and the datediff in minutes of the appointment, and see if it's greater-than-or-equal to.
The overlap time is the lesser of the difference between the AppointmentStart and the AvailabilityEnd, vs the difference between AvailabilityStart and AppointmentEnd.
E.g.: Appointment is 1:30 to 4:30 (3 hours = 180 minutes, half = 90 minutes), availability is 1:00 to 2:00. Overlap is Apt Start to Avail End, = 30 minutes, is not a valid match. Another person has availability from 1:00 to 5:00, overlap is 1:30 to 4:30, which is 180, valid match. Another person has availability from 2:00 to 4:00, overlap is Apt Start to Avail End minus Apt Start to Avail Start = 1:30 to 4:00 = 150 minutes = valid match.
Think you can write the join math for that one or do you need help on that?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply