DECLARE  CornerCursor   CURSOR FAST_FORWARD FOR
SELECT 
StudentNumber,
SchoolCode,
CurTransEligibility,
GradeLevel,
GeoCode,
HomeZone,
BilingualCode,
SpedCode
FROM 
--  [Tables for getting the student data]
 
OPEN CornerCursor
 
FETCH NEXT FROM CornerCursor INTO 
@StudentNumber,
@SchoolCode,
@CurTransEligibility,
@GradeLevel,
@GeoCode,
@HomeZone,
@BilingualCode,
@SpedCode
 
WHILE @@FETCH_STATUS = 0
BEGIN
-- For the grade level, see if the student is ineligible 
              -- for transportation [too close to school].
IF EXISTS(SELECT Sch FROM TransWalkingDistance 
                       WHERE SchCode = @SchoolCode 
                           AND GeoCode = @GeoCode 
                           AND GradeLevel = @GradeLevel)
BEGIN
UPDATE 
      EnrollmentRecord
SET
      TransCode = '',
                                 LastUpdateUserID= 'CScrpW' -- ineligible
WHERE 
      StudentNo = @StudentNo 
                                  AND SchCode = @SchoolCode
 
GOTO FetchNext
END
 
 
              IF ((rtrim(@BilingualCode) = '' 
                   OR RTRIM(isnull(@BilingualCode,''))  = 'S')
                   AND (@SpedCode = '' 
                           OR SUBSTRING(@SpedCode, 1, 1) IN ('S', 'R') 
                           OR @SpedCode IN ('V1', 'V2', 'P1', 'P2'))) 
BEGIN
IF @GradeLevel <> 'H' 
AND NOT EXISTS
(SELECT School FROM BufferZoneLookup 
      WHERE School = @School  
                                               AND GradeLevel = @GradeLevel 
      AND (GeoCode = @GeoCode 
                                               OR HomeZone IN (@HomeZone, 'C','H')))
BEGIN
-- Code here to run a similar update to the update 
                                         -- statement above, but setting the transportation
-- code to 'C0RR' and the LastUpdateUser to 'CScrpZ'
END
 
GOTO FetchNext
END
 
-- Two additional update statements here, each with different 
              -- conditions.  There's a default one as well if none of the above 
              -- updates were used.
 
FetchNext:
FETCH NEXT FROM CornerCursor INTO 
@StudentNumber,
@SchoolCode,
@CurTransEligibility,
@GradeLevel,
@GeoCode,
@HomeZone,
@BilingualCode,
@SpedCode
END
END
CLOSE CornerCursor
DEALLOCATE CornerCursor

-- This query will populate a table with different bit fields 
-- representing the if/else conditions used throughout the cursor.  
-- So essentially this is centralizing disperse if/else conditions into 
-- one record for each student that can be then used by the next query.
SELECT
studentdata.StudentNumber,
studentdata.SchoolCode,
studentdata.TransCode,
studentdata.GradeLevel,
studentdata.BilingualCode,
studentdata.SpedCode,
studentdata.GeoCode,
studentdata.HomeZone,
(CASE WHEN studentdata.sch IN('4261','1010','1020','1340')
THEN 1 ELSE 0 END) AS 'CanNotBeOutOfZone', -- Citywide
              (CASE WHEN isnull(SchZone.sch, '') <> '' THEN 1 ELSE 0 END) 
                                                            AS 'InZoneOrBufferZone',
(CASE WHEN isnull(TransWalkingDistance.sch, '') <> '' THEN 1 ELSE 0 END)
                                                            AS 'IsInWalkingDistance',
(CASE WHEN studentdata.grade IN ('K0', 'K1') 
                     AND studentdata.isExtendedDay = 0 THEN 1 ELSE 0 END) 
                                                            AS 'IsHalfDayKindergarten',
-- Bilingual non-spanish students are always transportation eligible.
(CASE WHEN NOT isnull(studentdata.BilingualCode, '') = '' 
                  AND NOT (isnull(studentdata.BilingualCode,'')) = 'S' THEN 1 ELSE 0 END)
                                                            AS 'BilingualIneligibleForC0RR',
-- A few additional case statement options omitted [SpedIneligibleForC0RR].
INTO #StudentsToProcess
FROM
--  [List of tables] similar to the one in the cursor definition.   
 
-- Use the bit fields to set what the transportation code would 
-- be for the student.  The case statement to set PropsedTransCodeBasedOnData 
-- runs in the same precedence order as the original cursor.  In other words, 
-- order is important to keep the results the same.
SELECT  studentdata.studentno, studentdata.transcode AS 'curTransCode',
-- in the walkeligible range
(CASE WHEN IsInWalkingDistance = 1 THEN ''
-- Set out of zone.  High school grades and some other 
              -- categories cannot be out of zone.
WHEN BilingualIneligibleForC0RR = 0
    AND NOT sys.grade IN('09','10','11','12') 
    AND InZoneOrBufferZone = 0 
    AND CanNotBeOutOfZone = 0 THEN 'C0RR'
WHEN (IsHalfDayKindergarten = 1 
                                   AND isnull(sys.transcode, '') = '') THEN 'C5*R'
WHEN (IsHalfDayKindergarten = 1 
                                   AND isnull(sys.transcode, '') <> '') THEN sys.transcode
ELSE 'C5RR' END) AS 'PropsedTransCodeBasedOnData'
INTO #ProposedTranscodeChanges
FROM 
-- [List of tables] 
 
-- Run the update - set trans eligibility codes [c5rr, c0rr, etc].  
-- Each update used a different user ID tag, so set this appropriately.
UPDATE studentdata SET transcode = propsedTransCodeBasedOnData,
LastUpdateUser = (CASE isnull(propsedTransCodeBasedOnData, '') 
              WHEN 'c5rr' THEN 'CScrpC'
WHEN 'c0rr' THEN 'CScrpZ' 
              WHEN '' THEN 'CScrpW' 
              WHEN 'C5*R' THEN 'CScrpK' 
              ELSE 'CScrpX' END)
FROM 
-- [List of tables]