November 8, 2011 at 2:44 pm
No guarantees on performance, but here's your overlap logic.
select ap.*,DATEDIFF(minute,start,[end]) as duration,ca.PersonId,ca.AvailFrom,ca.AvailTo,ca.MinutesOverlap
from Appointments ap
cross apply (select *,
DATEDIFF(minute, case when AvailFrom >= ap.Start then AvailFrom else ap.Start end
,case when AvailTo <= ap.[End] then AvailTo else ap.[End] end) MinutesOverlap
from Availability
) ca
where 1.0*MinutesOverlap/DATEDIFF(minute,start,[end]) > .50
and AvailType = 'Available'
order by ap.Id,MinutesOverlap desc,PersonId
__________________________________________________
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 9, 2011 at 7:21 am
There are problems with your data model that make this problem more difficult. Specifically, it allows conflicting values. For example,
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'
Person '7' is marked as being both available and unavailable in the range 04:00PM-11:59PM. It will be much easier to solve this if you clean up the data so that there is only one status per time period. You may need to use a temp table/variable to do this cleanup if you can't change the actual data. You can use a similar approach to what the Dixie Flatline used for the appointment overlaps to determine status overlaps.
Another question I have is why the end dates don't use midnight. All of your end dates end on the hour—except for midnight. Since you have appointments that cross days, it doesn't make sense to have availabilities that don't.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
November 10, 2011 at 4:20 am
That's great, it seems to work a treat.
What i might do for people that are unavailable, is run a separate query to return all peopleId's that are not available and remove them from my list of people that were available.
Thanks again.
November 10, 2011 at 4:22 am
And regarding the availabilities ending at 23:59, that was just an example to show someone was available for the whole day. People can set availability that run over several days if they wish.
November 10, 2011 at 9:29 am
Dan Williams-449672 (11/10/2011)
And regarding the availabilities ending at 23:59, that was just an example to show someone was available for the whole day. People can set availability that run over several days if they wish.
The point is that the day ends at midnight not at 23:59. I once worked on a system that made this incorrect assumption and one of my colleagues was working from home and entered a gift in the minute between 23:59 and 00:00 and she was unable to produce a receipt for that gift, because the application assumed that the day ended at 23:59. We figured out a work around, but we wouldn't have had to if the application used the correct time for the end of the day.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply