January 8, 2011 at 12:42 pm
I've inherited a table that essentially manages slots of available time. The existing table looks like this:
CREATE TABLE [dbo].[ProctorSlot](
[ProctorSlotId] [int] IDENTITY(1,1) NOT NULL,
[StartDateTime] [datetime] NULL,
[EndDateTime] [datetime] NULL,
[AvailableSlots] [int] NULL,
PRIMARY KEY CLUSTERED
(
[ProctorSlotId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
This thing is VERY difficult to work with, not to mention the potential for dups. There must be a better way. Is there some sort of "pattern" or classic approach to this sort of thing?
One of the primary questions we need to ask of this data is Get all the available time slots in "N" min. intervals that are between a start date and an end date and have enough time to cover a requested duration. The desired output is a one column table with all the available date/times in the requested interval. So the function request looks something like this:
create function GetAvailability
@startDate DateTime
,@EndDate DateTime
,@intervalMins int
,@durationMins int
RETURNS @AvailData TABLE(AvailDateTime DateTime)
.
.
.
The only way to get the desired output is to get all the candidate rows, and loop through every interval between startDate/endDate for each candidate, and build the output. This function becomes very difficult to write against the ProctorSlots table. There has to be a much better schema to support this sort of thing.
I hope this makes sense. Thanks!
.
January 8, 2011 at 1:02 pm
Are the data in your [ProctorSlot] the time spans already reserved?
If so, you could use a calendar table together with a tally table to get your "N" min interval.
I don't think it's going to be too complicated.
And for sure, you don't need a loop for it!
If you could provide the required table def and some ready to use sample data as described in the first link in my signature together with your expected result you'll most probably get a coded and tested solution.
January 9, 2011 at 12:01 am
I think I see where you're going with the calendar table. Here's what I have so far. I hope this is enough code for you to see what I'm doing.
--* Here is the simulated ProctorSlot table
DECLARE @ProctorSlot TABLE(
ProctorSlotId int IDENTITY(1,1) PRIMARY KEY NOT NULL,
StartDateTime datetime NULL,
EndDateTime datetime NULL,
AvailableSlots int NULL
)
INSERT INTO @ProctorSlot(StartDateTime, EndDateTime, AvailableSlots) VALUES('7/1/2011 09:00:00 AM', '7/1/2011 12:00:00 PM', 20)
INSERT INTO @ProctorSlot(StartDateTime, EndDateTime, AvailableSlots) VALUES('7/1/2011 01:00:00 PM', '7/1/2011 03:30:00 PM', 10)
INSERT INTO @ProctorSlot(StartDateTime, EndDateTime, AvailableSlots) VALUES('7/1/2011 06:00:00 PM', '7/1/2011 09:00:00 PM', 20)
SELECT * FROM @ProctorSlot
--* Blackouts must also be considered. These must be excluded from the results
DECLARE @Blackouts TABLE(
BlackoutId int IDENTITY(1,1) PRIMARY KEY NOT NULL,
StartDateTime datetime NULL,
EndDateTime datetime NULL
)
INSERT INTO @Blackouts(StartDateTime, EndDateTime) VALUES('12/25/2011 12:00:00 AM', '12/25/2011 11:59:59 PM')
INSERT INTO @Blackouts(StartDateTime, EndDateTime) VALUES('1/1/2011 12:00:00 AM', '1/1/2011 11:59:59 PM')
INSERT INTO @Blackouts(StartDateTime, EndDateTime) VALUES('7/4/2011 12:00:00 AM', '7/4/2011 11:59:59 PM')
--* Here are the simulated request arguments. So the caller is requesting all available time slots between
--* 9AM and 5PM, on 7/1/2011, in 15 min intervals. Each slot must have sufficient time available to accommodate the requested
--* duration
DECLARE @ReqStart DateTime, @ReqEnd DateTime, @ReqIntMins int, @ReqDurMins int
SELECT @ReqStart = '7/1/2011 09:00:00 AM', @ReqEnd = '7/1/2011 05:00:00 PM', @ReqIntMins = 15, @ReqDurMins = 45
--* I know I need a calendar table here. The calendar table should span from @ReqStart to @ReqEnd in @ReqIntMins intervals
DECLARE @CalTable TABLE (
CalDateTime DateTime
)
--* The only way I know how to create a calendar table is by looping.
DECLARE @ThisInterval DateTime
SET @ThisInterval = @ReqStart
WHILE @ThisInterval <= @ReqEnd
BEGIN
INSERT INTO @CalTable(CalDateTime) VALUES(@ThisInterval)
SET @ThisInterval = DATEADD(MINUTE, @reqIntMins, @ThisInterval)
END
SELECT * FROM @CalTable
DECLARE @ResultTable TABLE(TimeSlot DateTime PRIMARY KEY NOT NULL)
--* So now armed with these tableS, I should be able to create a query that returns only the available dates/times with sufficient duration,
--* but I'm not sure how to handle that one.
SELECT * FROM @ResultTable
BTW, Here's the existing code that I inherited. I think you'll understand why I'm trying to get rid of it!
😀
ALTER PROCEDURE [dbo].[GetProctorAvailability]
@selectedDate datetime
,@slotEndDate datetime
,@slotDuration int = 15
,@slotPad int = 30
AS
BEGIN
IF @slotEndDate IS NOT NULL
SET @slotEndDate = dateadd(HH, 36, @selectedDate)
DECLARE @AvailableTimeSlots TABLE(
SlotId int IDENTITY(1,1) PRIMARY KEY,
availabledate datetime,
availableslots int,
canstart bit
)
-- set the initial timeslots
declare OLPSlots cursor for
(
select startdatetime, enddatetime, availableslots
from ProctorSlot where startdatetime between @selectedDate and @slotEndDate
union
select startdatetime, enddatetime, availableslots
from ProctorSlot where enddatetime between @selectedDate and @slotEndDate
);
declare @starting datetime;
declare @ending datetime;
declare @available int;
open OLPSlots;
fetch next from OLPSlots into @starting, @ending, @available;
while @@fetch_status = 0
begin
declare @timeupdate datetime = @starting;
while @timeupdate < @ending
begin
insert into @AvailableTimeSlots values(@timeupdate, @available,case when @available = 0 then 0 else 1 end);
set @timeupdate = dateadd(MI, @slotDuration, @timeupdate);
end
insert into @AvailableTimeSlots values(@ending, @available, case when @available = 0 then 0 else 1 end);
fetch next from OLPSlots into @starting, @ending, @available;
end
close OLPSlots;
deallocate OLPSlots;
-- blankout any blockout times
declare OLPBlackedout cursor for
(
select blackoutstartdatetime, blackoutenddatetime
from OLPBlackout where (blackoutstartdatetime between @selectedDate and @slotEndDate) or
(blackoutenddatetime between @selectedDate and @slotEndDate) or
(@selectedDate between blackoutstartdatetime and blackoutenddatetime) or
(@slotEndDate between blackoutstartdatetime and blackoutenddatetime)
)
open OLPBlackedout;
fetch next from OLPBlackedout into @starting, @ending;
while @@fetch_status = 0
begin
set @timeupdate = @starting;
while @timeupdate < @ending
begin
update @AvailableTimeSlots set availableslots = 0, canstart = 0 where availabledate = @timeupdate;
set @timeupdate = dateadd(MI, @slotDuration, @timeupdate);
end
update @AvailableTimeSlots set availableslots = 0, canstart = 0 where availabledate = @timeupdate;
fetch next from OLPBlackedout into @starting, @ending;
end
close OLPBlackedout;
deallocate OLPBlackedout;
-- reduce availability by slots already taken
declare OLPRegistered cursor for
(
select er.registrationstatusid, er.registrationdatetime, cast((e.duration + @slotPad)/@slotDuration as int)
from eventregistration er inner join event e on er.eventid = e.eventid
INNER JOIN (SELECT ListItemId FROM ListItem WHERE Discriminator = 'EventRegistrationStatus' AND Code IN ('I', 'S', 'T')) li -- inprogress, scheduled, completed
ON li.ListItemId = er.RegistrationStatusId
where er.registrationdatetime between dateadd(HH,-12,@SelectedDate) and @slotEndDate
and er.isactive = 1
);
declare @id bigint;
declare @regDate datetime;
declare @loop int;
open OLPRegistered;
fetch next from OLPRegistered into @id, @regDate, @loop;
while @@fetch_status = 0
begin
declare @innercounter int = 0;
declare @innerdate datetime = @regDate;
while @innercounter < @loop
begin
update @AvailableTimeSlots set availableslots = case when availableslots = 0 then 0 else availableslots - 1 end,
canstart = case when availableslots - 1 <= 0 then 0 else 1 end where availabledate = @innerdate;
set @innerdate = dateadd(MI, @slotDuration, @innerdate);
set @innercounter = @innercounter + 1;
end
fetch next from OLPRegistered into @id, @regDate, @loop;
end
close OLPRegistered;
deallocate OLPRegistered;
--delete from @AvailableTimeSlots where canstart = 0 or availabledate not between @selectedDate and @slotEndDate;
delete from @AvailableTimeSlots where availabledate not between @selectedDate and @slotEndDate;
--declare @djl1 datetime = '2012-12-25 12:34:56';
--declare @djl2 time = '12:34:56';
--declare @djl3 int = 1;
--insert into @AvailableTimeSlots values(@djl1, @djl2, @djl3,@djl3);
SELECT * FROM @AvailableTimeSlots
END
.
January 9, 2011 at 3:57 am
Here's something that should get you started.
A few things to notice:
Instead of using an on-the-fly tally table you should considerto build a permanent one. It's quite helpful in many scenarios, especially when there's a need to expand ranges. See the related link in my signature for more details.
Depending on the number of rows you're dealing with for proctorslot and Blackouts you might want to union those two outside of the cte into an indexed temp table.
The grp column in the final output can be used to find consecutive time slots. Based on that you could calculate the available time slots with begin and end time as well as periods in between. Just aggregate ove the grp column.
;WITH cte_tally AS
-- build a tally table on the fly holding a few thousand rows (at least 2k)
(
SELECT Number N
FROM master..spt_values WHERE TYPE='P'
),
cte_blocked_periods AS
-- get all time spans to be excluded within the period to be evaluated
(
SELECT startdatetime,enddatetime
FROM @proctorslot
WHERE startdatetime <= @ReqEnd and enddatetime >= @ReqStart
UNION ALL
SELECT startdatetime,enddatetime
FROM @Blackouts
WHERE startdatetime <= @ReqEnd and enddatetime >= @ReqStart
),
cte_minutes AS
-- build a pseudo calendar table based on the date range and the requied interval
(
SELECT DATEADD(mi,N*@ReqIntMins,@ReqStart) slots
FROM cte_tally
WHERE N <= DATEDIFF(mi,@ReqStart,@ReqEnd)/@ReqIntMins
)
-- final output
SELECT -- all time slots without a relevant entry in cte_blocked_periods
slots,
DATEADD(mi,-(ROW_NUMBER() OVER(ORDER BY slots )-1) * @ReqIntMins,slots) grp
FROM cte_minutes
WHERE
NOT EXISTS
(
SELECT 1
FROM cte_blocked_periods
WHERE cte_minutes.slots >=cte_blocked_periods.startdatetime
AND cte_minutes.slots <cte_blocked_periods.enddatetime
)
January 9, 2011 at 11:40 am
Thanks Lutz! That will definitely get me over the hump!
.
January 14, 2011 at 11:22 pm
One more thing on this one. I also have a "Holiday" table. Yea, I know it's redundant, I'm working on getting rid of it in favor of the "Blackouts" table, which is why I didn't mention it, but it turns out I can't get rid of the holidays table just yet. I'm stuck with it for now, so dates in the holiday table need to be excluded from the available slots as well.
My first instinct was to UNION the holidays into the cte_blocked_periods cte, but there are a couple catches with that. First, I'll need to transform the Holidays into a range. So instead of 12/25/2011, I need to figure out how to transform each holiday into 12/25/2011 12:00:00 AM - 12/25/2011 11:59:59 PM (I think). Second, the HolidayDate column is a "Date" NOT a "DateTime" which seems to complicate things.
Maybe I'm just completely going the wrong direction here, but it seems like a robust approach in that I could support holidays that do not span an entire day.
(BTW, I posted a similar question in another forum. I'll clean that up if you can help me out here). THANKS!
Here's where I was going, but it doesn't seem to work the way I expected:
cte_blocked_periods AS
-- get all time spans to be excluded within the period to be evaluated
(
SELECT startdatetime,enddatetime
FROM ProctorSlot
WHERE startdatetime <= @ReqEnd and enddatetime >= @ReqStart
UNION ALL
SELECT BlackoutStartDateTime, BlackoutEndDateTime
FROM OLPBlackout
WHERE BlackoutStartDateTime <= @ReqEnd and BlackoutEndDateTime >= @ReqStart
UNION ALL
SELECT DATEADD(SECOND, 1, CAST(HolidayDate AS DateTime)) AS HolidayStartTime, DATEADD(SECOND, -1, CAST(DATEADD(day, 1, HolidayDate) AS DateTime)) AS HolidayEndTime FROM Holiday
),
Here's an example of what the Holiday table looks like:
DECLARE @Holiday TABLE (HolidayId int identity(1,1) primary key, HolidayDate Date)
INSERT INTO @Holiday(HolidayDate) VALUES('7/4/2011')
INSERT INTO @Holiday(HolidayDate) VALUES('12/24/2011')
INSERT INTO @Holiday(HolidayDate) VALUES('12/25/2011')
INSERT INTO @Holiday(HolidayDate) VALUES('12/31/2011')
INSERT INTO @Holiday(HolidayDate) VALUES('8/11/2011') --* Yea, my birthday is a holiday in my db :)
select * from @Holiday
.
January 15, 2011 at 3:01 am
To include your Holiday table, you could use
UNION ALL
SELECT CAST(HolidayDate AS DATETIME),CAST(HolidayDate AS DATETIME)+1
FROM @Holiday
WHERE CAST(HolidayDate AS DATETIME) <= @ReqEnd AND CAST(HolidayDate AS DATETIME)+1 >= @ReqStart
Just be aware the query against the Holiday table will be performed as a table scan. But based on the content of the table I wouldn't expect too many rows to cause a significant issue.
January 15, 2011 at 1:34 pm
That works marvelously! Thanks again!
.
January 15, 2011 at 1:51 pm
Getting back to the design thing though. I'm still not crazy about the "ProctorSlot" table. I guess the first thing that scares me is duplicates. Is there a way to create a unique index to prevent dups? Or would I have to handle that in a trigger?
If you had to track this sort of thing, would you use this sort of table design to do it?
.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply