How to remove transcript records for a specific grade level, keeping records listed in Off Grade Courses. For example, remove Middle School Grades from High School Transcripts.

1. Backup HIS

• SELECT * INTO HIS_{ticketNumber} FROM HIS

  
  

2. Identify transcript records that need to be removed (this will vary depending on different factors such as the grade, year, school, etc...)

• The example below joins STU, HIS, and OFG and grabs the middle school transcript records that need to be removed excluding the Off Grade records, only for specific grade levels and school sites:
  

  SELECT O.CN, H.*
  FROM (SELECT * FROM HIS WHERE GR IN (7, 8) AND DEL = 0) H
  LEFT JOIN (SELECT * FROM OGC WHERE SC IN (500, 520, 560, 570, 580, 590) AND DEL = 0) O ON H.CN = O.CN
  INNER JOIN (SELECT * FROM STU WHERE GR IN (9, 10, 11, 12) AND DEL = 0) S ON H.PID = S.ID
  WHERE O.CN IS NULL

  
  

3. Delete the transcript records that need to be removed

• The example below DEL-Tags the middle school transcript records that need to be removed excluding the Off Grade records, only for specific grade levels and school sites: 

  UPDATE H
  SET DEL = 1
  FROM (SELECT * FROM HIS WHERE GR IN (7, 8) AND DEL = 0) H
  LEFT JOIN (SELECT * FROM OGC WHERE SC IN (500, 520, 560, 570, 580, 590) AND DEL = 0) O ON H.CN = O.CN
  INNER JOIN (SELECT * FROM STU WHERE GR IN (9, 10, 11, 12) AND DEL = 0) S ON H.PID = S.ID
  WHERE O.CN IS NULL