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