Select... max()

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!

post the script that causes the error

Hi, please share the script to short out the problem that you are facing.

insert into db2005.dbo.external_data
			(ssn, last_name, first_name, award_year_token,
			 longstr1_field_id, longstr1_value)
(select s.student_ssn,
		s.last_name,
		case when len(s.first_name) <= 11
			 then s.first_name
			 else null end as first_name,
		x.ay_no_esign as award_year_token,
		'002606',          -- user_long.value_06
		x.esign
from db2005.dbo.student s
join 
	 (
		select s.student_ssn,
				s.student_token,
				case when ul.value_06 is null and len(s.pin) between 1 and 7
					 then s.pin
					 else ul.value_06 end as esign,
				sub.ay_no_esign,
				max(ul.award_year_token) as ay_with_esign
		from db2005.dbo.student s
		join db2005.dbo.user_long ul on s.student_token = ul.student_token
		join 
			(select s.student_ssn,
					max(say.award_year_token) as ay_no_esign
			 from db2005.dbo.student s
			 join db2005.dbo.stu_award_year say on s.student_token = say.student_token
			 join db2005.dbo.user_long ul on say.stu_award_year_token = ul.stu_award_year_token
			 where s.pin is not null 
			 and ul.value_06 is null
			 and say.award_year_token > 1990
			 group by s.student_ssn
			 ) sub
			 on s.student_ssn = sub.student_ssn
		group by s.student_ssn,
				s.student_token,
				case when ul.value_06 is null and len(s.pin) between 1 and 7
					 then s.pin
					 else ul.value_06 end,
				sub.ay_no_esign
	 ) x
	 on s.student_token = x.student_token
where x.ay_no_esign >= x.ay_with_esign 
      and x.esign is not null and s.student_ssn  not in(select ssn from db2005.dbo.external_data)
      and ay_with_esign not IN (select award_year_token  from db2005.dbo.external_data )
      )

Note: "s.pin" is the ESIGN_Number.