I am using the awesome stored procedure created by Aaron Bertrand here as a start since it almost does what I need. Sadly my SQL skills are not as good as his so I need some help here. What I need to do is to find free time slots between a start and end date constrained by the time of work of a specific user. My requirements are a little different from OP:
- I only need the free slots from 1 user.
- A user could have different work time on each day of the week.
- A user could not work some days of the week
- A user can have a break, an interval when he cannot do any work
- There is no recursive events
So, the new table structure would be something like this:
CREATE TABLE [dbo].[event]
(
[event_id] [int] IDENTITY(1,1) NOT NULL,
[event_start] [datetime] NULL,
[event_end] [datetime] NULL,
[UserId] [int] NULL
) ON [PRIMARY]
CREATE TABLE [dbo].[Users]
(
[UserID] [int] IDENTITY(1,1) NOT NULL,
[Username] [varchar](32) NULL,
[Sunday] [bit] NULL,
[Monday] [bit] NULL,
[Tuesday] [bit] NULL,
[Wednesday] [bit] NULL,
[Thursday] [bit] NULL,
[Friday] [bit] NULL,
[Saturday] [bit] NULL,
[WorkStartSunday] [time](7) NULL,
[WorkEndSunday] [time](7) NULL,
[IntervalStartSunday] [time](7) NULL,
[IntervalEndSunday] [time](7) NULL,
[WorkStartMonday] [time](7) NULL,
[WorkEndMonday] [time](7) NULL,
[IntervalStartMonday] [time](7) NULL,
[IntervalEndMonday] [time](7) NULL,
[WorkStartTuesday] [time](7) NULL,
[WorkEndTuesday] [time](7) NULL,
[IntervalStartTuesday] [time](7) NULL,
[IntervalEndTuesday] [time](7) NULL,
[WorkStartWednesday] [time](7) NULL,
[WorkEndWednesday] [time](7) NULL,
[IntervalStartWednesday] [time](7) NULL,
[IntervalEndWednesday] [time](7) NULL,
[WorkStartThursday] [time](7) NULL,
[WorkEndThursday] [time](7) NULL,
[IntervalStartThursday] [time](7) NULL,
[IntervalEndThursday] [time](7) NULL,
[WorkStartFriday] [time](7) NULL,
[WorkEndFriday] [time](7) NULL,
[IntervalStartFriday] [time](7) NULL,
[IntervalEndFriday] [time](7) NULL,
[WorkStartSaturday] [time](7) NULL,
[WorkEndSaturday] [time](7) NULL,
[IntervalStartSaturday] [time](7) NULL,
[IntervalEndSaturday] [time](7) NULL,
CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED
(
[UserID] 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
And some sample data will be this:
INSERT [dbo].[Users] ([UserID], [Username], [Sunday], [Monday], [Tuesday], [Wednesday], [Thursday], [Friday], [Saturday], [WorkStartSunday], [WorkEndSunday], [IntervalStartSunday], [IntervalEndSunday], [WorkStartMonday], [WorkEndMonday], [IntervalStartMonday], [IntervalEndMonday], [WorkStartTuesday], [WorkEndTuesday], [IntervalStartTuesday], [IntervalEndTuesday], [WorkStartWednesday], [WorkEndWednesday], [IntervalStartWednesday], [IntervalEndWednesday], [WorkStartThursday], [WorkEndThursday], [IntervalStartThursday], [IntervalEndThursday], [WorkStartFriday], [WorkEndFriday], [IntervalStartFriday], [IntervalEndFriday], [WorkStartSaturday], [WorkEndSaturday], [IntervalStartSaturday], [IntervalEndSaturday])
VALUES (1, N'User A', 0, 1, 1, 0, 0, 0, 0, NULL, NULL, NULL, NULL, CAST(N'08:00:00' AS Time), CAST(N'18:00:00' AS Time), CAST(N'12:00:00' AS Time), CAST(N'13:00:00' AS Time), CAST(N'10:00:00' AS Time), CAST(N'15:00:00' AS Time), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL)
I need to be able to pass the duration of the event, the start/end date and the UserId. All other data comes from the user table. I was able to resolve requirement 3 and partially 2. 2 is my biggest problem - I can start at the right time but do not know how I can make it to end at the right time. 4 I did not even try yet :/
Warning: This is the first time ever that I play with such a complex stored procedures so it is not beautiful what I done to it (sorry, Aaron). I am sure this will make DBAs cry. Here is what I got until now (it is in the form of a SQL because it is easier to test):
--parameters
declare @UserId int = 1
declare @Duration INT = 30;
declare @StartDate SMALLDATETIME = '12-22-2014';
declare @EndDate SMALLDATETIME = '12-25-2014';
--user configurations
declare @Sunday bit = 0;
declare @Monday bit = 0;
declare @Tuesday bit = 0;
declare @Wednesday bit = 0;
declare @Thursday bit = 0;
declare @Friday bit = 0;
declare @Saturday bit = 0;
declare @StartSunday time;
declare @EndSunday time;
declare @StartMonday time;
declare @EndMonday time;
declare @StartIntervalMonday time;
declare @EndIntervalMonday time;
declare @StartTuesday time;
declare @EndTuesday time;
declare @StartIntervalTuesday time;
declare @EndIntervalTuesday time;
declare @StartWednesday time;
declare @EndWednesday time;
declare @StartThursday time;
declare @EndThursday time;
declare @StartFriday time;
declare @EndFriday time;
declare @StartSaturday time;
declare @EndSaturday time;
--load user configurations
select
@Sunday = us.Sunday,
@Monday = us.Monday,
@Tuesday = us.Tuesday,
@Wednesday = us.Wednesday,
@Thursday = us.Thursday,
@Friday = us.Friday,
@Saturday = us.Saturday,
@StartSunday = us.WorkStartMonday,
@EndSunday = us.WorkEndSunday,
@StartMonday = us.WorkStartMonday,
@EndMonday = us.WorkEndMonday,
@StartIntervalMonday = us.IntervalStartMonday,
@EndIntervalMonday = us.IntervalEndMonday,
@StartTuesday = us.WorkStartTuesday,
@EndTuesday = us.WorkEndTuesday,
@StartIntervalTuesday = us.IntervalStartTuesday,
@EndIntervalTuesday = us.IntervalEndTuesday,
@StartWednesday = us.WorkStartWednesday,
@EndWednesday = us.WorkEndWednesday,
@StartThursday = us.WorkStartThursday,
@EndThursday = us.WorkEndThursday,
@StartFriday = us.WorkStartFriday,
@EndFriday = us.WorkEndThursday,
@StartSaturday = us.WorkStartSaturday,
@EndSaturday = us.WorkEndThursday
from
Users us
where
us.UserId = @UserID
;WITH dRange(d) AS
(
SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate)+1)
DATEADD(DAY, n-1, @StartDate)
FROM (SELECT n = ROW_NUMBER() OVER (ORDER BY [object_id])
FROM sys.objects) AS x
where DATEADD(DAY, n-1, @StartDate) >= @StartDate
and DATEADD(DAY, n-1, @StartDate) <= @EndDate
and DATEPART(WEEKDAY,DATEADD(DAY, n-1, @StartDate)) in
(
IIF(@Sunday =1,1,null),
IIF(@Monday =1,2,null),
IIF(@Tuesday =1,3,null),
IIF(@Wednesday =1,4,null),
IIF(@Thursday =1,5,null),
IIF(@Friday =1,6,null),
IIF(@Saturday=1,7,null)
)
), possible(ds, de) AS
(
--with this horrendous thing I set the start time for each weekday
SELECT
CASE
WHEN DATEPART(WEEKDAY,dRange.d) = 1 THEN DATEADD(MINUTE, @Duration*rn, DATEADD(HOUR, datepart(HOUR, @StartSunday), dRange.d))
WHEN DATEPART(WEEKDAY,dRange.d) = 2 THEN DATEADD(MINUTE, @Duration*rn, DATEADD(HOUR, datepart(HOUR, @StartMonday), dRange.d))
WHEN DATEPART(WEEKDAY,dRange.d) = 3 THEN DATEADD(MINUTE, @Duration*rn, DATEADD(HOUR, datepart(HOUR, @StartTuesday), dRange.d))
WHEN DATEPART(WEEKDAY,dRange.d) = 4 THEN DATEADD(MINUTE, @Duration*rn, DATEADD(HOUR, datepart(HOUR, @StartWednesday), dRange.d))
WHEN DATEPART(WEEKDAY,dRange.d) = 5 THEN DATEADD(MINUTE, @Duration*rn, DATEADD(HOUR, datepart(HOUR, @StartThursday), dRange.d))
WHEN DATEPART(WEEKDAY,dRange.d) = 6 THEN DATEADD(MINUTE, @Duration*rn, DATEADD(HOUR, datepart(HOUR, @StartFriday), dRange.d))
WHEN DATEPART(WEEKDAY,dRange.d) = 7 THEN DATEADD(MINUTE, @Duration*rn, DATEADD(HOUR, datepart(HOUR, @StartSaturday), dRange.d))
END,
CASE
WHEN DATEPART(WEEKDAY,dRange.d) = 1 THEN DATEADD(MINUTE, @Duration*rn + @Duration, DATEADD(HOUR, datepart(HOUR, @StartSunday), dRange.d))
WHEN DATEPART(WEEKDAY,dRange.d) = 2 THEN DATEADD(MINUTE, @Duration*rn + @Duration, DATEADD(HOUR, datepart(HOUR, @StartMonday), dRange.d))
WHEN DATEPART(WEEKDAY,dRange.d) = 3 THEN DATEADD(MINUTE, @Duration*rn + @Duration, DATEADD(HOUR, datepart(HOUR, @StartTuesday), dRange.d))
WHEN DATEPART(WEEKDAY,dRange.d) = 4 THEN DATEADD(MINUTE, @Duration*rn + @Duration, DATEADD(HOUR, datepart(HOUR, @StartWednesday), dRange.d))
WHEN DATEPART(WEEKDAY,dRange.d) = 5 THEN DATEADD(MINUTE, @Duration*rn + @Duration, DATEADD(HOUR, datepart(HOUR, @StartThursday), dRange.d))
WHEN DATEPART(WEEKDAY,dRange.d) = 6 THEN DATEADD(MINUTE, @Duration*rn + @Duration, DATEADD(HOUR, datepart(HOUR, @StartFriday), dRange.d))
WHEN DATEPART(WEEKDAY,dRange.d) = 7 THEN DATEADD(MINUTE, @Duration*rn + @Duration, DATEADD(HOUR, datepart(HOUR, @StartSaturday), dRange.d))
END
FROM
--but here is the problem - if I understood it correctly, this select limits the day events but I was not able to do a CASE here since I do not know how to access the date
--@StartMonday/@EndMonday should change to reflect the specific day of the week
(SELECT TOP ((DATEDIFF(HOUR,@StartMonday,@EndMonday)*60)/@Duration) rn = ROW_NUMBER() OVER (ORDER BY [object_id])-1 FROM sys.objects)
AS x
CROSS JOIN dRange
)
SELECT p.ds, p.de
FROM possible AS p
where not exists
(
select 1 from
(
select UserId,event_start,event_end
from event
where event_start >= @StartDate
and event_start < DATEADD(DAY, 1, @EndDate)
) as sub
WHERE sub.UserId IN
(
SELECT UserId FROM Users AS m
WHERE UserId = @UserId
AND (p.de > sub.event_start AND p.ds < sub.event_end)
)
)
order by ds
It is obvious that different weekdays (with different star/end) should return a different number of rows but I do not know how to do it on the select top. At this point I still do not know how will I resolve the Interval problem but maybe this is another question, or maybe I can ignore them at client side. Help?
Edit 1
THe problem is that the TOP clause should limit to the number of hours of the specific week day.
If I pass to start/end the date 12-22-2014 and put (SELECT TOP ((DATEDIFF(HOUR,@StartMonday,@EndMonday)*60)/@Duration), it works perfectly.
If I pass to start/end the date 12-23-2014 and put (SELECT TOP ((DATEDIFF(HOUR,@StartTuesday,@EndTuesday)*60)/@Duration), it works perfectly.
But if I pass start 12-22-2014 and end 12-23-2014, it does not work because I do not know hot to do a CASE there. Here is the results:
The configuration says:
WorkStartMonday WorkEndMonday WorkStartTuesday WorkEndTuesday
08:00 18:00 10:00 15:00
With start/end at the same day (22/12/2014, Duration = 60) the result is:
2014-12-22 08:00:00 - 2014-12-22 09:00:00
2014-12-22 09:00:00 - 2014-12-22 10:00:00
2014-12-22 10:00:00 - 2014-12-22 11:00:00
2014-12-22 11:00:00 - 2014-12-22 12:00:00
2014-12-22 12:00:00 - 2014-12-22 13:00:00
2014-12-22 13:00:00 - 2014-12-22 14:00:00
2014-12-22 14:00:00 - 2014-12-22 15:00:00
2014-12-22 15:00:00 - 2014-12-22 16:00:00
2014-12-22 16:00:00 - 2014-12-22 17:00:00
2014-12-22 17:00:00 - 2014-12-22 18:00:00
With start/end on 23/12/2014, the result is also correct:
2014-12-23 10:00:00 2014-12-23 11:00:00
2014-12-23 11:00:00 2014-12-23 12:00:00
2014-12-23 12:00:00 2014-12-23 13:00:00
2014-12-23 13:00:00 2014-12-23 14:00:00
2014-12-23 14:00:00 2014-12-23 15:00:00
This happens because the TOP clause limits to the total hours of each weekday. If I have more than one day I should do a case on that TOP condition but how can I access the "date"?
