Change your DESC to ASC for your ROW_NUMBER to get them in the correct order.

Why do you want to get rid of the records that are gone? Is it because of the cAbsenceCode? Or some other reason?

If it is because the cAbsenceCode is U, try this: -

SELECT ROW_NUMBER() OVER (PARTITION BY x.cStudentId ORDER BY dtEnrollmentDate ASC) AS DaySeq,

[cStudentId], [iSchoolCode], [dtEnrollmentDate], [cGradeCode], [cAbsenceCode], [dtEntryDate]

FROM (SELECT [cStudentId], [iSchoolCode], [dtEnrollmentDate],

[cGradeCode], [cAbsenceCode], [dtEntryDate]

FROM #x1

WHERE [cAbsenceCode] <> 'U') x;

Which produces: -

DaySeq cStudentId iSchoolCode dtEnrollmentDate cGradeCode cAbsenceCode dtEntryDate

-------------------- ---------- ----------- ----------------------- ---------- ------------ -----------------------

1 004095871 356 2012-09-21 00:00:00.000 09 P 2012-10-24 00:00:00.000

2 004095871 356 2012-09-22 00:00:00.000 09 P 2012-10-24 00:00:00.000

3 004095871 356 2012-09-23 00:00:00.000 09 S 2012-10-24 00:00:00.000

4 004095871 356 2012-09-24 00:00:00.000 09 S 2012-10-24 00:00:00.000

5 004095871 356 2012-09-25 00:00:00.000 09 P 2012-10-24 00:00:00.000

6 004095871 356 2012-09-26 00:00:00.000 09 P 2012-10-24 00:00:00.000

If it is for some other reason, you'll need to explain