June 11, 2013 at 6:31 pm
Hi Experts,
I need your help on the case defined below. I need to find available schedule based on below defined rules
Schedule is defined for a Camera Room. But a Camera Room may have multiple schedules.
Weekly schedule is stored on week day basis, whereas onetime schedule is stored as actual date time.
• Weekly
o There can be weekly schedules for a Specific Camera Room, or ANY (-1), In this case -1 overrides
• Onetime
o There can be multiple weekly schedules for a resource.
• Onetime Overrides Weekly schedules.
o Available
o Unavailable
Script is attached to create schema and insert some sample data. I also have attached a sheet where I have tried to explain what should be the actual outcome should be. The green ones should come as is, but the white ones should be overridden with the values as specified in side.(Override column)
Please let me know if I have missed something or for more information.
Thanks everybody in advance for help.
June 12, 2013 at 8:51 am
Just putting the code inline.
Create Table Schedule (
Studio int,
PromotionID int,
CameraRoomID int ,
StartTime DateTime,
EndTime DateTime,
ScheduleType int, -- 1 Weekly--2 OneTime
Available int) --1 Available--2- Closed
GO
--Delete from Schedule
Insert into Schedule
Values
(1, 1, 1,'2000-01-02 11:00', '2000-01-02 18:10',1,1 ) , --Weekly Schedule Sunday
(1, 1, 1,'2000-01-03 10:00', '2000-01-03 21:00',1 ,1) , --Weekly Schedule Monday
(1, 1, 1,'2000-01-04 10:00', '2000-01-04 21:00',1 ,1) , --Weekly Schedule Tuesday
(1, 1, 1,'2000-01-05 10:00', '2000-01-05 21:00',1 ,1) , --Weekly Schedule Wednesday
(1, 1, 1,'2000-01-06 10:00', '2000-01-06 21:00',1 ,1) , --Weekly Schedule Thursday
(1, 1, 1,'2000-01-07 10:00', '2000-01-07 21:00',1 ,1) , --Weekly Schedule Friday
(1, 1, 1,'2000-01-08 10:00', '2000-01-08 20:50',1 ,1) , --Weekly Schedule Saturday
(1, 1, 1,'2013-06-12 08:00', '2013-06-12 17:00',2 ,1) , --One Time Schedule --Wed, Avail
(1, 1, 1,'2013-06-13 00:00', '2013-06-14 00:00',2 ,2) , --One Time Schedule --Thu, Unavail
--2nd Studio 1st Resource
(2, 1, 1,'2000-01-02 11:00', '2000-01-02 18:10',1,1 ) , --Weekly Schedule Sunday
(2, 1, 1,'2000-01-03 10:00', '2000-01-03 21:00',1 ,1) , --Weekly Schedule Monday
(2, 1, 1,'2000-01-04 10:00', '2000-01-04 21:00',1 ,1) , --Weekly Schedule Tuesday
(2, 1, 1,'2000-01-05 10:00', '2000-01-05 21:00',1 ,1) , --Weekly Schedule Wednesday
(2, 1, 1,'2000-01-06 10:00', '2000-01-06 21:00',1 ,1) , --Weekly Schedule Thursday
(2, 1, 1,'2000-01-07 10:00', '2000-01-07 21:00',1 ,1) , --Weekly Schedule Friday
(2, 1, 1,'2000-01-08 10:00', '2000-01-08 20:50',1 ,1) , --Weekly Schedule Saturday
(2, 1, 1,'2013-06-12 08:00', '2013-06-12 17:00',2 ,1) , --One Time Schedule --Wed, Avail
(2, 1, 1,'2013-06-13 00:00', '2013-06-14 00:00',2 ,1) , --One Time Schedule --Thu, avail
--2nd Studio 2nd Resource
(2, 1, 2,'2000-01-02 11:00', '2000-01-02 18:10',1,1 ) , --Weekly Schedule Sunday
(2, 1, 2,'2000-01-03 10:00', '2000-01-03 21:00',1 ,1) , --Weekly Schedule Monday
(2, 1, 2,'2000-01-04 10:00', '2000-01-04 21:00',1 ,1) , --Weekly Schedule Tuesday
(2, 1, 2,'2000-01-05 10:00', '2000-01-05 21:00',1 ,1) , --Weekly Schedule Wednesday
(2, 1, 2,'2000-01-06 10:00', '2000-01-06 21:00',1 ,1) , --Weekly Schedule Thursday
(2, 1, 2,'2000-01-07 10:00', '2000-01-07 21:00',1 ,1) , --Weekly Schedule Friday
(2, 1, 2,'2000-01-08 10:00', '2000-01-08 20:50',1 ,1) , --Weekly Schedule Saturday
(2, 1, 2,'2013-06-12 08:00', '2013-06-12 17:00',2 ,1) , --One Time Schedule --Wed, Avail
(2, 1, 2,'2013-06-13 00:00', '2013-06-13 00:00',2 ,1) , --One Time Schedule --Thu, avail
-- Case of Override of CameraRoomID = -1 Where It applies to all resource for that date and week day
(2, 1, -1,'2013-06-13 00:00', '2013-06-14 00:00',2 ,2), --One Time Schedule --Thu, Unavail
--if a weekly schedule with Camera room ID = -1 and it opens up late then it also restricts the schedule of other camera rooms.
(2, 1, -1,'2000-01-02 13:00', '2000-01-02 16:00',1,1 ) --Weekly Schedule Sunday, for all
GO
Select Studio,
PromotionID,
CameraRoomID,
datename( weekday,StartTime),StartTime,EndTime,
ScheduleType,
Available
from Schedule
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply