How to use SQL to search for Overlapping Primary Classes in a Flex Scheduling Environment
District-wide:
SELECT STU.[SC] ,STU.[ID] ,DAY.[DT] ,COUNT(*) AS [Count] FROM STU INNER JOIN CAR ON STU.[SC] = CAR.[SC] AND STU.[SN] = CAR.[SN] INNER JOIN MST ON CAR.[SC] = MST.[SC] AND CAR.[SE] = MST.[SE] INNER JOIN DAY ON STU.[SC] = DAY.[SC] WHERE 1=1 AND STU.[DEL] = 0 AND CAR.[DEL] = 0 AND MST.[DEL] = 0 AND DAY.[DT] BETWEEN CAR.[DS] AND CAR.[DE] AND STU.[SC] IN (SELECT [SC] FROM OPT WHERE OPT.[DEL] = 0 AND OPT.[NM] = 'TrackingPrimaryClasses' AND OPT.[CD] = 'True') AND MST.[PC] = 1 GROUP BY STU.[SC] ,STU.[ID] ,DAY.[DT] HAVING COUNT(*) > 1
Specific School:
- DECLARE @SchoolCode SMALLINT = xx
- xx = the school code
/* Set to specific School Code */ DECLARE @SchoolCode SMALLINT = '27' SELECT STU.[SC] ,STU.[ID] ,DAY.[DT] ,COUNT(*) [Total] FROM STU INNER JOIN CAR ON STU.[SC] = CAR.[SC] AND STU.[SN] = CAR.[SN] INNER JOIN MST ON CAR.[SC] = MST.[SC] AND CAR.[SE] = MST.[SE] INNER JOIN DAY ON STU.[SC] = DAY.[SC] WHERE 1=1 AND STU.[DEL] = 0 AND CAR.[DEL] = 0 AND MST.[DEL] = 0 AND DAY.[DT] BETWEEN CAR.[DS] AND CAR.[DE] AND STU.[SC] IN (SELECT [SC] FROM OPT WHERE OPT.[DEL] = 0 AND OPT.[NM] = 'TrackingPrimaryClasses' AND OPT.[CD] = 'True') AND MST.[PC] = 1 AND STU.[SC] = @SchoolCode GROUP BY STU.[SC] ,STU.[ID] ,DAY.[DT] HAVING COUNT(*) > 1