Extrapolating Scheduled Events

[Ultradiv]

This is very complicated for my tired little brain! I hope you can help me.

There is a calendar view (html) which can be viewed 7 days at a time always starting on a Monday. it could be any week.

The events that display on that 7 day view (columns are days rows are every 15 minutes) are extrapolated from one table of events some of which may be one off's and others recurring in different ways, this table gets populated elsewhere with data representing all future events.

The base events table looks like this:
TABLE WR_schedule
cleanID int IDENTITY (1, 1) NOT NULL ,
AccountID int NOT NULL ,
RoundID int NOT NULL ,
CustomerID int NULL ,
CleanStart datetime NULL,
NoSpecificTime int NOT NULL,
CleanLength int NULL ,
Rec int NOT NULL,
Rec_daily varchar (50) NULL,
Rec_weekly_int int NOT NULL,
Rec_monthly_select bit NOT NULL,
Rec_monthly_int int NOT NULL,
scheduleName varchar (50) NULL


CleanStart holds the starting date of the event and the time if NoSpecificTime = 0 else CleanStart time needs to be ignored.
CleanLength is the duration of the event in minutes.
Rec will = 0 if the event is not recurring else
Rec = 1 if is recurring weekly on the days in Rec_daily (1,3,5,7) (1 being monday) and every number of weeks held in Rec_weekly_int (3 = every third week).
Rec = 3 if the event is recurring monthly in two ways, either by position in the month of Cleanstart date (i.e. every third Monday) when Rec_monthly_select is false or by the same date as cleanstart in the month when Rec_monthly_select is true, both these are also selected according to every Rec_monthly_int months (Rec_monthly_int=6 will be every six months)

selecting these events is one thing but we also need to give them times for the events when a time is not specified (NoSpecificTime = 1) Each RoundID has a WR_Round.start and WR_Round.stop time in minutes from midnight in an associated table WR_Round between which times events can be designated a time slot. All events with given start times need to be allowed their time slots in a day and then all the untimed events need to be allocated successive start times using their CleanLength (duration) from the earliest avalable time slot large enough. if there is not enough time in a day then events should occupy time after the WR_Round.stop time. Too many events (or too long duration) should be overlapped with the latest event in the day and marked as overlapping (see output table)

If events with given times overlap, then those events need to be given their overlapping time slots but marked as overlapping (see output table).


Output table.
What is required is a query that returns all the cleanID's with start time in minutes from midnight and whether or not the event is overlapping with another for events occurring during the week being viewed, given a date (this date will determine the week to be viewed i.e. given a Friday the week will start from the Monday before) and a RoundID.

E.g.
CleanID int, start int (minutes from midnight), overlap bit

if this is in a table variable then I can join it with the customer table etc.


I'm very grateful in advance for your input.
i do hope you can help me as I have been struggling with this for days now.

Andy

 

[RyanEK]

Hi,

That's quite a lot to take in. Can you provide sample data with the desired output?

Ryan

 

[Ultradiv]

/*Run this script to get two tables
One is sample data the other is expected output.
Earliest Start and Latest stop times are declared but not actually used here, they will be in the real thing of course
*/

declare @schedule table(
cleanID int IDENTITY (1, 1) PRIMARY KEY,
AccountID int NOT NULL ,
RoundID int NOT NULL ,
CustomerID int NULL ,
CleanStart datetime NULL,
NoSpecificTime int NOT NULL,
CleanLength int NULL ,
Rec int NOT NULL,
Rec_daily varchar (50) NULL,
Rec_weekly_int int NOT NULL,
Rec_monthly_select bit NOT NULL,
Rec_monthly_int int NOT NULL,
scheduleName varchar (100) NULL
)

insert @schedule
select 1,1,1,'1 sep 2008 08:30:00',0,45,0,'',0,0,0,'#1 has time no rec'
union all select 1,1,2,'1 sep 2008',1,30,0,'',0,0,0,'#2 no time no rec'
union all select 1,1,3,'1 sep 2008 08:45:00',0,15,1,'1,3',2,0,0,'#3 has time rec. weekly on mon & wed, every other week'
union all select 1,1,4,'2 sep 2008 14:30:00',0,90,3,'',0,1,1,'#4 has time rec. monthly 1st Tue in month every month'
union all select 1,1,5,'4 sep 2008',1,300,3,'',0,0,6,'#5 has no time. rec. monthly on 4th of every 6th month'


select * from @schedule


--Output required from above data

declare @earliestStart int,@latestStop int
select @earliestStart=500 --minutes from midnight = 08:00 hrs
select @latestStop=1080 --minutes from midnight = 18:00 hrs

--given the date in the week to look at of '4 Sep 2008' and roundID=1

declare @output table
(
[id] int IDENTITY(1, 1) PRIMARY KEY,
CleanID int not NULL,
Cleandate datetime not NULL,
Start int NULL,
Stop int NULL,
overlap bit not NULL
)

insert @output
select 2,'1 sep 2008',500,530,0
union all select 1,'1 sep 2008',530,575,1 --overlap
union all select 3,'1 sep 2008',545,560,1 --overlap
union all select 3,'2 sep 2008',840,930,0
union all select 3,'4 sep 2008',500,800,0

select * from @output

 

[Ultradiv]

sorry cant see how to edit my post

last table should be populated thus

insert @output
select 2,'1 sep 2008',500,530,0
union all select 1,'1 sep 2008',530,575,1 --overlap
union all select 3,'1 sep 2008',545,560,1 --overlap
union all select 4,'2 sep 2008',840,930,0
union all select 5,'4 sep 2008',500,800,0

--I had the cleanID's wrong!

 

[RyanEK]

Hi,

Looking at your example shouldn't 8:00am from midnight be 480 minutes and not 500?

If I understand your question right I think the following is what you want. You may need to tweak the overlapping logic if its not right...

select cleanid, cleanstart,
case when nospecifictime = 1 then 480 else datepart(hh,cleanstart) * 60 end as Start,
case when nospecifictime = 1 then 480 + cleanlength else (datepart(hh,cleanstart) * 60) + cleanlength end as Stop,
case when exists (
	select cleanid from 
	(select cleanid, cleanstart as dstart, 
	 dateadd(mi,cleanlength,cleanstart) as dEnd
	 from @schedule) as t
	 where t.cleanid <> s.cleanid
	 and cleanstart <= dEnd and dateadd(mi,cleanlength,cleanstart) >= dStart
) 
then 1 else 0 end as Overlap
from @schedule s
order by cleanstart

Ryan