casuals.sql

WITH CASUAL_BLOB AS
(
  SELECT
    SE.EMPLOYMENT_TYPE_ID,
    CONTACT.FIRSTNAME || ' ' || CONTACT.SURNAME AS "ORIGINAL_TEACHER",
    TO_CHAR((CURRENT DATE), 'Month DD') AS "CASUAL_DATE",
    REPLACEMENT,
    PERIOD,
    START_TIME,
    END_TIME

  FROM TABLE(EDUMATE.GET_SCHEDULES_ON_DATE(CURRENT DATE)) SCHEDS

  INNER JOIN STAFF ON STAFF.STAFF_ID = SCHEDS.STAFF_ID
  INNER JOIN CONTACT ON CONTACT.CONTACT_ID = STAFF.CONTACT_ID
  INNER JOIN STAFF_EMPLOYMENT SE ON SE.STAFF_ID = SCHEDS.REPLACEMENT_STAFF_ID

  WHERE REPLACEMENT_STAFF_ID IS NOT NULL
  
  UNION ALL
  
  SELECT
    SE.EMPLOYMENT_TYPE_ID,
    CONTACT.FIRSTNAME || ' ' || CONTACT.SURNAME AS "ORIGINAL_TEACHER",
    TO_CHAR((CURRENT DATE - 1 DAYS), 'Month DD') AS "CASUAL_DATE",
    REPLACEMENT,
    PERIOD,
    START_TIME,
    END_TIME

  FROM TABLE(EDUMATE.GET_SCHEDULES_ON_DATE(CURRENT DATE - 1 DAYS)) SCHEDS

  INNER JOIN STAFF ON STAFF.STAFF_ID = SCHEDS.STAFF_ID
  INNER JOIN CONTACT ON CONTACT.CONTACT_ID = STAFF.CONTACT_ID
  INNER JOIN STAFF_EMPLOYMENT SE ON SE.STAFF_ID = SCHEDS.REPLACEMENT_STAFF_ID

  WHERE REPLACEMENT_STAFF_ID IS NOT NULL
  
  UNION ALL
  
  SELECT
    SE.EMPLOYMENT_TYPE_ID,
    CONTACT.FIRSTNAME || ' ' || CONTACT.SURNAME AS "ORIGINAL_TEACHER",
    TO_CHAR((CURRENT DATE - 2 DAYS), 'Month DD') AS "CASUAL_DATE",
    REPLACEMENT,
    PERIOD,
    START_TIME,
    END_TIME

  FROM TABLE(EDUMATE.GET_SCHEDULES_ON_DATE(CURRENT DATE - 2 DAYS)) SCHEDS

  INNER JOIN STAFF ON STAFF.STAFF_ID = SCHEDS.STAFF_ID
  INNER JOIN CONTACT ON CONTACT.CONTACT_ID = STAFF.CONTACT_ID
  INNER JOIN STAFF_EMPLOYMENT SE ON SE.STAFF_ID = SCHEDS.REPLACEMENT_STAFF_ID

  WHERE REPLACEMENT_STAFF_ID IS NOT NULL
  
  UNION ALL
  
  SELECT
    SE.EMPLOYMENT_TYPE_ID,
    CONTACT.FIRSTNAME || ' ' || CONTACT.SURNAME AS "ORIGINAL_TEACHER",
    TO_CHAR((CURRENT DATE - 3 DAYS), 'Month DD') AS "CASUAL_DATE",
    REPLACEMENT,
    PERIOD,
    START_TIME,
    END_TIME

  FROM TABLE(EDUMATE.GET_SCHEDULES_ON_DATE(CURRENT DATE - 3 DAYS)) SCHEDS

  INNER JOIN STAFF ON STAFF.STAFF_ID = SCHEDS.STAFF_ID
  INNER JOIN CONTACT ON CONTACT.CONTACT_ID = STAFF.CONTACT_ID
  INNER JOIN STAFF_EMPLOYMENT SE ON SE.STAFF_ID = SCHEDS.REPLACEMENT_STAFF_ID

  WHERE REPLACEMENT_STAFF_ID IS NOT NULL
  
  UNION ALL
  
  SELECT
    SE.EMPLOYMENT_TYPE_ID,
    CONTACT.FIRSTNAME || ' ' || CONTACT.SURNAME AS "ORIGINAL_TEACHER",
    TO_CHAR((CURRENT DATE - 4 DAYS), 'Month DD') AS "CASUAL_DATE",
    REPLACEMENT,
    PERIOD,
    START_TIME,
    END_TIME

  FROM TABLE(EDUMATE.GET_SCHEDULES_ON_DATE(CURRENT DATE - 4 DAYS)) SCHEDS

  INNER JOIN STAFF ON STAFF.STAFF_ID = SCHEDS.STAFF_ID
  INNER JOIN CONTACT ON CONTACT.CONTACT_ID = STAFF.CONTACT_ID
  INNER JOIN STAFF_EMPLOYMENT SE ON SE.STAFF_ID = SCHEDS.REPLACEMENT_STAFF_ID

  WHERE REPLACEMENT_STAFF_ID IS NOT NULL

  UNION ALL
  
  SELECT
    SE.EMPLOYMENT_TYPE_ID,
    CONTACT.FIRSTNAME || ' ' || CONTACT.SURNAME AS "ORIGINAL_TEACHER",
    TO_CHAR((CURRENT DATE - 5 DAYS), 'Month DD') AS "CASUAL_DATE",
    REPLACEMENT,
    PERIOD,
    START_TIME,
    END_TIME

  FROM TABLE(EDUMATE.GET_SCHEDULES_ON_DATE(CURRENT DATE - 5 DAYS)) SCHEDS

  INNER JOIN STAFF ON STAFF.STAFF_ID = SCHEDS.STAFF_ID
  INNER JOIN CONTACT ON CONTACT.CONTACT_ID = STAFF.CONTACT_ID
  INNER JOIN STAFF_EMPLOYMENT SE ON SE.STAFF_ID = SCHEDS.REPLACEMENT_STAFF_ID

  WHERE REPLACEMENT_STAFF_ID IS NOT NULL
  
  UNION ALL
  
  SELECT
    SE.EMPLOYMENT_TYPE_ID,
    CONTACT.FIRSTNAME || ' ' || CONTACT.SURNAME AS "ORIGINAL_TEACHER",
    TO_CHAR((CURRENT DATE - 6 DAYS), 'Month DD') AS "CASUAL_DATE",
    REPLACEMENT,
    PERIOD,
    START_TIME,
    END_TIME

  FROM TABLE(EDUMATE.GET_SCHEDULES_ON_DATE(CURRENT DATE - 6 DAYS)) SCHEDS

  INNER JOIN STAFF ON STAFF.STAFF_ID = SCHEDS.STAFF_ID
  INNER JOIN CONTACT ON CONTACT.CONTACT_ID = STAFF.CONTACT_ID
  INNER JOIN STAFF_EMPLOYMENT SE ON SE.STAFF_ID = SCHEDS.REPLACEMENT_STAFF_ID

  WHERE REPLACEMENT_STAFF_ID IS NOT NULL
  
  UNION ALL
  
  SELECT
    SE.EMPLOYMENT_TYPE_ID,
    CONTACT.FIRSTNAME || ' ' || CONTACT.SURNAME AS "ORIGINAL_TEACHER",
    TO_CHAR((CURRENT DATE - 7 DAYS), 'Month DD') AS "CASUAL_DATE",
    REPLACEMENT,
    PERIOD,
    START_TIME,
    END_TIME

  FROM TABLE(EDUMATE.GET_SCHEDULES_ON_DATE(CURRENT DATE - 7 DAYS)) SCHEDS

  INNER JOIN STAFF ON STAFF.STAFF_ID = SCHEDS.STAFF_ID
  INNER JOIN CONTACT ON CONTACT.CONTACT_ID = STAFF.CONTACT_ID
  INNER JOIN STAFF_EMPLOYMENT SE ON SE.STAFF_ID = SCHEDS.REPLACEMENT_STAFF_ID

  WHERE REPLACEMENT_STAFF_ID IS NOT NULL
  
  UNION ALL
  
  SELECT
    SE.EMPLOYMENT_TYPE_ID,
    CONTACT.FIRSTNAME || ' ' || CONTACT.SURNAME AS "ORIGINAL_TEACHER",
    TO_CHAR((CURRENT DATE - 8 DAYS), 'Month DD') AS "CASUAL_DATE",
    REPLACEMENT,
    PERIOD,
    START_TIME,
    END_TIME

  FROM TABLE(EDUMATE.GET_SCHEDULES_ON_DATE(CURRENT DATE - 8 DAYS)) SCHEDS

  INNER JOIN STAFF ON STAFF.STAFF_ID = SCHEDS.STAFF_ID
  INNER JOIN CONTACT ON CONTACT.CONTACT_ID = STAFF.CONTACT_ID
  INNER JOIN STAFF_EMPLOYMENT SE ON SE.STAFF_ID = SCHEDS.REPLACEMENT_STAFF_ID

  WHERE REPLACEMENT_STAFF_ID IS NOT NULL

  UNION ALL
  
  SELECT
    SE.EMPLOYMENT_TYPE_ID,
    CONTACT.FIRSTNAME || ' ' || CONTACT.SURNAME AS "ORIGINAL_TEACHER",
    TO_CHAR((CURRENT DATE - 9 DAYS), 'Month DD') AS "CASUAL_DATE",
    REPLACEMENT,
    PERIOD,
    START_TIME,
    END_TIME

  FROM TABLE(EDUMATE.GET_SCHEDULES_ON_DATE(CURRENT DATE - 9 DAYS)) SCHEDS

  INNER JOIN STAFF ON STAFF.STAFF_ID = SCHEDS.STAFF_ID
  INNER JOIN CONTACT ON CONTACT.CONTACT_ID = STAFF.CONTACT_ID
  INNER JOIN STAFF_EMPLOYMENT SE ON SE.STAFF_ID = SCHEDS.REPLACEMENT_STAFF_ID

  WHERE REPLACEMENT_STAFF_ID IS NOT NULL

  UNION ALL
  
  SELECT
    SE.EMPLOYMENT_TYPE_ID,
    CONTACT.FIRSTNAME || ' ' || CONTACT.SURNAME AS "ORIGINAL_TEACHER",
    TO_CHAR((CURRENT DATE - 10 DAYS), 'Month DD') AS "CASUAL_DATE",
    REPLACEMENT,
    PERIOD,
    START_TIME,
    END_TIME

  FROM TABLE(EDUMATE.GET_SCHEDULES_ON_DATE(CURRENT DATE - 10 DAYS)) SCHEDS

  INNER JOIN STAFF ON STAFF.STAFF_ID = SCHEDS.STAFF_ID
  INNER JOIN CONTACT ON CONTACT.CONTACT_ID = STAFF.CONTACT_ID
  INNER JOIN STAFF_EMPLOYMENT SE ON SE.STAFF_ID = SCHEDS.REPLACEMENT_STAFF_ID

  WHERE REPLACEMENT_STAFF_ID IS NOT NULL

  UNION ALL
  
  SELECT
    SE.EMPLOYMENT_TYPE_ID,
    CONTACT.FIRSTNAME || ' ' || CONTACT.SURNAME AS "ORIGINAL_TEACHER",
    TO_CHAR((CURRENT DATE - 11 DAYS), 'Month DD') AS "CASUAL_DATE",
    REPLACEMENT,
    PERIOD,
    START_TIME,
    END_TIME

  FROM TABLE(EDUMATE.GET_SCHEDULES_ON_DATE(CURRENT DATE - 11 DAYS)) SCHEDS

  INNER JOIN STAFF ON STAFF.STAFF_ID = SCHEDS.STAFF_ID
  INNER JOIN CONTACT ON CONTACT.CONTACT_ID = STAFF.CONTACT_ID
  INNER JOIN STAFF_EMPLOYMENT SE ON SE.STAFF_ID = SCHEDS.REPLACEMENT_STAFF_ID

  WHERE REPLACEMENT_STAFF_ID IS NOT NULL

  UNION ALL
  
  SELECT
    SE.EMPLOYMENT_TYPE_ID,
    CONTACT.FIRSTNAME || ' ' || CONTACT.SURNAME AS "ORIGINAL_TEACHER",
    TO_CHAR((CURRENT DATE - 12 DAYS), 'Month DD') AS "CASUAL_DATE",
    REPLACEMENT,
    PERIOD,
    START_TIME,
    END_TIME

  FROM TABLE(EDUMATE.GET_SCHEDULES_ON_DATE(CURRENT DATE - 12 DAYS)) SCHEDS

  INNER JOIN STAFF ON STAFF.STAFF_ID = SCHEDS.STAFF_ID
  INNER JOIN CONTACT ON CONTACT.CONTACT_ID = STAFF.CONTACT_ID
  INNER JOIN STAFF_EMPLOYMENT SE ON SE.STAFF_ID = SCHEDS.REPLACEMENT_STAFF_ID

  WHERE REPLACEMENT_STAFF_ID IS NOT NULL

  UNION ALL
  
  SELECT
    SE.EMPLOYMENT_TYPE_ID,
    CONTACT.FIRSTNAME || ' ' || CONTACT.SURNAME AS "ORIGINAL_TEACHER",
    TO_CHAR((CURRENT DATE - 13 DAYS), 'Month DD') AS "CASUAL_DATE",
    REPLACEMENT,
    PERIOD,
    START_TIME,
    END_TIME

  FROM TABLE(EDUMATE.GET_SCHEDULES_ON_DATE(CURRENT DATE - 13 DAYS)) SCHEDS

  INNER JOIN STAFF ON STAFF.STAFF_ID = SCHEDS.STAFF_ID
  INNER JOIN CONTACT ON CONTACT.CONTACT_ID = STAFF.CONTACT_ID
  INNER JOIN STAFF_EMPLOYMENT SE ON SE.STAFF_ID = SCHEDS.REPLACEMENT_STAFF_ID

  WHERE REPLACEMENT_STAFF_ID IS NOT NULL

  UNION ALL
  
  SELECT
    SE.EMPLOYMENT_TYPE_ID,
    CONTACT.FIRSTNAME || ' ' || CONTACT.SURNAME AS "ORIGINAL_TEACHER",
    TO_CHAR((CURRENT DATE - 14 DAYS), 'Month DD') AS "CASUAL_DATE",
    REPLACEMENT,
    PERIOD,
    START_TIME,
    END_TIME

  FROM TABLE(EDUMATE.GET_SCHEDULES_ON_DATE(CURRENT DATE - 14 DAYS)) SCHEDS

  INNER JOIN STAFF ON STAFF.STAFF_ID = SCHEDS.STAFF_ID
  INNER JOIN CONTACT ON CONTACT.CONTACT_ID = STAFF.CONTACT_ID
  INNER JOIN STAFF_EMPLOYMENT SE ON SE.STAFF_ID = SCHEDS.REPLACEMENT_STAFF_ID

  WHERE REPLACEMENT_STAFF_ID IS NOT NULL
)

SELECT
  ROWNUMBER() OVER (PARTITION BY REPLACEMENT),
  REPLACEMENT,
  CASUAL_DATE,
  START_TIME,
  END_TIME,
  ORIGINAL_TEACHER

FROM CASUAL_BLOB

WHERE EMPLOYMENT_TYPE_ID IN (3,4)

ORDER BY CASUAL_DATE ASC, REPLACEMENT, START_TIME