How to use SQL to search for Overlapping Primary Teachers in a Flex Scheduling Environment


District-wide: 

SELECT DISTINCT
     SSE.[SC] AS [SchoolCode]
    ,SSE.[SE] AS [SectionNumber]
    ,SSE.[SC] AS [SC?]
    ,SSE.[SE] AS [SE?]
    ,'At least 2 or more Primary Section Staff Assignments overlap' AS [Description]
FROM SSE
    INNER JOIN DAY ON SSE.[SC] = DAY.[SC]
        AND CONVERT(DATE, DAY.[DT]) BETWEEN
            CONVERT(DATE, (
                CASE
                    WHEN SSE.[SD] IS NULL THEN DAY.[DT]
                    ELSE SSE.[SD]
                END
            ))
            AND CONVERT(DATE, (
                CASE
                    WHEN SSE.[ED] IS NULL THEN DAY.[DT]
                    ELSE SSE.[ED]
                END
            ))

WHERE
    1=1
    AND SSE.[DEL] = 0
    AND DAY.[DEL] = 0
    AND (
           SSE.[PR]  = 1
        OR SSE.[PPR] = 1
    )

GROUP BY
    SSE.[SC]
    ,SSE.[SE]
    ,DAY.[DT]

HAVING
    COUNT(DISTINCT SSE.[ID]) > 1



Specific School:

  • DECLARE @SchoolCode SMALLINT = xx 
    • xx = the school code
DECLARE @SchoolCode SMALLINT = 11 


SELECT DISTINCT
     SSE.[SC] AS [SchoolCode]
    ,SSE.[SE] AS [SectionNumber]
    ,SSE.[SC] AS [SC?]
    ,SSE.[SE] AS [SE?]
    ,'At least 2 or more Primary Section Staff Assignments overlap' AS [Description]
FROM SSE
    INNER JOIN DAY ON SSE.[SC] = DAY.[SC]
        AND CONVERT(DATE, DAY.[DT]) BETWEEN
            CONVERT(DATE, (
                CASE
                    WHEN SSE.[SD] IS NULL THEN DAY.[DT]
                    ELSE SSE.[SD]
                END
            ))
            AND CONVERT(DATE, (
                CASE
                    WHEN SSE.[ED] IS NULL THEN DAY.[DT]
                    ELSE SSE.[ED]
                END
            ))

WHERE
    1=1
    AND SSE.[DEL] = 0
    AND DAY.[DEL] = 0
    AND (
           SSE.[PR]  = 1
        OR SSE.[PPR] = 1
    )
    AND SSE.[SC] IN (@SchoolCode)

GROUP BY
    SSE.[SC]
    ,SSE.[SE]
    ,DAY.[DT]

HAVING
    COUNT(DISTINCT SSE.[ID]) > 1