I am trying to build schedule system for classes
A Schedule have a repeated type like Daily, Weekly, Monthly.
Now i am unable to check the new schedule adding is already exists/ in between another schedule etc.,
The fields of my form are
Class ID,
Time [Day Schedule Time],
Start [When Schedule Starts],
End [When will Schedule Ends],
Repeat [Daily, Weekly, Monthly]
Mean while I try this, But it doesn't work for Weekly, Monthly and Yearly
SELECT *
FROM `schedule`
WHERE '2015-04-09 06:17:17' <= startdt
AND '2015-04-10 06:17:17' >= enddt
AND TYPE = 'daily'
AND (
(
'03:07:00' NOT
BETWEEN TIME
AND endtime
)
AND (
'04:14:00' NOT
BETWEEN TIME
AND endtime
)
AND (
TIME NOT
BETWEEN '04:07:00'
AND '04:07:00'
)
)
LIMIT 0 , 30
Can you clarify this please? When you create a new schedule are you creating all the repeat entries, and you're trying to check there will be no conflicts before adding another repeat schedule?
I assume Start equates to a date, and your classes are fixed time slots ie 1 hour? So your unpacked data from schedule might look like this? (Simplified with some normalisation)
CalendarEntries ScheduleID ClassroomID Date StartTime (Fixed duration assumed - If not, add an end time)
The above being populated once a schedule is ok'd, and would hold all classrooms, dates and times?
If you were going to create a new schedule you might generate all your proposed new entries into a temporary table with the same structure then:
...
IF EXISTS (
SELECT TOP 1 *
FROM #PlannedEntries A
INNER JOIN CalendarEntries B
ON A.Date = B.Date
AND A.ClassroomID = B.ClassroomID
AND (
A.StartTime BETWEEN B.StartTime AND DATEADD(MIN,@LessonDuration,B.StartTime)
OR
DATEADD(MIN,@LessonDuration,A.StartTime) BETWEEN B.StartTime AND DATEADD(MIN,@LessonDuration,B.StartTime)
OR
(A.StartTime < B.StartTime AND DATEADD(MIN,@LessonDuration,A.StartTime) > DATEADD(MIN,@LessonDuration,B.StartTime)
)
PRINT 'Schedule Conflict'
ELSE
BEGIN
PRINT 'Schedule Added'
INSERT INTO CalendarEntries SELECT * FROM #PlannedEntries
END
You'll want to consider the subjects and resources ie staff too presumably. Add some clarification and I'll see if I can point you in the right direction when I'm back on ;)
Note - this is messy. I'd seperate start and end time and you can simplify the above. It's also far simpler if you have a "slot" system rather than time based if it's lessons. There are also better ways to structure it - let me know if this is the sort of problem you're up against and I'll refine it.