Hi, everyone. Our system automatically generates an "ESIGN Number" for each student. The script used to do this works very well most of the time, but occasionally, something similar to what you see below occurs.
| student_ssn | alternate_id | last_name | first_name | award_year_token | Access_Code | ESIGN_Number |
|-------------|--------------|-----------|------------|------------------|-------------|--------------|
| 999887777 | 10001002 | TEST | STUDENT | 2012 | 1110 | P12345 |
| 999887777 | 10001002 | TEST | STUDENT | 2014 | 1110 | X54321 |
| 999887777 | 10001002 | TEST | STUDENT | 2015 | 1110 | NULL |
In the example above, the script should generate an ESIGN Number where "award_year_token" = 2015. That script looks at the most previous year and if an ESIGN Number already exists, it copies that number. The idea is that the student has one ESIGN Number across each year.
Now on to the issue -- sometimes, something goes awry and a new ESIGN Number is created when one already exists. What script can I write to show me all those students whose most recent year ESIGN Number (excluding any years with a NULL ESIGN Number value), does not match the one immediately previous to it.
I guess the results of that query would look like this:
| student_ssn | alternate_id | last_name | first_name | LATEST_YEAR | LATEST_ESIGN | PREVIOUS_YEAR | PREVIOUS_ESIGN |
|-------------|--------------|-----------|------------|-------------|--------------|---------------|----------------|
| 999887777 | 10001002 | TEST | STUDENT | 2014 | X54321 | 2012 | P12345 |
I'd like to see how many students have this issue.
Thanks for any advice!