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
SQL
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
SQL