Conflict in FinalExams for the same class

Question

I'm writing SP to insert into FinalExams Table. two exams should not conflict for the same class i mean if the first exam start at 9:30 and finish at 10:30; no exam should be between this period, so if i tried to insert exam at 10:15 the insert statement should not execute

here is my code

ALTER PROC [dbo].[InsertFinalExams] --1,1,3,'12/10/2013','11:00','11:30',1
(
@pClassID int,
@pSectionID int,
@pCourseID int,
@pExamDate datetime,
@pStartHoure datetime,
@pFinishHoure datetime,
@pCreatedBy int
)
AS 
--check if the exam period is valid 
--means if the exam start at hour it cant be finish at any time before the same hour
IF  DATEPART(hh,@pStartHoure) >=   DATEPART(HH,@pFinishHoure ) AND DATEPART(MI,@pStartHoure) >=   DATEPART(MI,@pFinishHoure ) 
    BEGIN
        PRINT('Please enter valid period')
        RETURN
    END
--To check if there is a class has a section and a course
IF NOT EXISTS (SELECT 'TRUE'
           FROM SectionsClasses 
           WHERE ClassID=@pClassID 
           AND SectionID=@pSectionID)
  OR
    NOT EXISTS (SELECT 'TRUE'
            FROM Courses
            WHERE CourseID=@pCourseID )

    BEGIN
        PRINT ('Please choose valid class and section and Course')
        RETURN
    END
--check if there is no exam at the same time
IF EXISTS (SELECT 'TRUE' 
            FROM FinalExams 
            WHERE ClassID=@pClassID
                  AND SectionID=@pSectionID 
                  AND ExamDate=@pExamDate
                  OR @pStartHoure between DATEADD(minute,-1, StartHoure) AND DATEADD(minute,-1,  FinishHoure)   
                  OR  @pFinishHoure between DATEADD(minute,-1, StartHoure) AND  DATEADD(minute,-1, FinishHoure)
                --  OR StartHoure > @pStartHoure
                  --AND FinishHoure < @pFinishHoure
                  )
    BEGIN 
        PRINT('Please choose another time for this exam')
        RETURN
    END
ELSE
        INSERT INTO FinalExams
        Values
            (
            @pClassID,
            @pSectionID,
            @pCourseID,
            @pExamDate,
            @pStartHoure,
            @pFinishHoure,
            @pCreatedBy,
            getdate(),
            null,
            null
            )

i think i have something wrong in WHERE clause

Answer 1

You want to check for a date range overlapping, take a look at this great answer : Determine Whether Two Date Ranges Overlap

Try the following :

--check if there is no exam at the same time
IF EXISTS (SELECT 'TRUE' 
            FROM FinalExams 
            WHERE ClassID=@pClassID
                  AND SectionID=@pSectionID 
                  AND ExamDate=@pExamDate
                  AND @pStartHoure <= FinishHoure 
                  AND @pFinishHoure >= StartHoure)