This is a sample of the student records that are being sorted by Year To Date Absent. We would like to display the students one time only with the max number of YTD Absent, YTDTardy, and YTDUnexcusedAbsent. We are now getting the same student listed multiple times.
SELECT Distinct
StuId,
StuName,
MAX(YTDAbsent),
Grade,
MAX(YTDTardy),
TruantFlag,
MAX(YTDUnexcusedAbsent),
SecNum,
[Date]
FROM OldAttn
GROUP BY StuId, StuName, Grade, TruantFlag, SecNum, [Date]
ORDER BY StuName
StuId StuName YTDAbsent Grade YTDTardy TruantFlag YTDUnexcusedAbsent Date
6xxxx24 StudentA 21 10 1 16 2/12/2009 12:29
6xxxx24 StudentA 22 10 1 17 2/18/2009 12:50
6xxxx24 StudentA 22 10 1 17 2/23/2009 13:40
6xxxx24 StudentA 23 10 1 18 2/27/2009 12:06
6xxxx24 StudentA 24 10 1 19 3/6/2009 13:48
6xxxx24 StudentA 26 10 1 21 3/19/2009 13:59
6xxxx24 StudentA 26 10 1 21 3/23/2009 12:12
6xxxx24 StudentA 27 10 1 22 3/25/2009 12:37
6xxxx24 StudentA 27 10 1 22 3/30/2009 13:00
6xxxx24 StudentA 28 10 1 23 3/31/2009 11:54
6xxxx24 StudentA 29 10 1 24 4/1/2009 12:53
6xxxx24 StudentA 30 10 1 25 4/2/2009 12:32
6xxxx24 StudentA 31 10 1 26 4/3/2009 11:27
6xxxx24 StudentA 32 10 1 27 4/15/2009 14:04
6xxxx24 StudentA 33 10 1 28 4/16/2009 13:27
5xxxx17 StudentB 9 12 18 2 2/3/2009 13:39
5xxxx17 StudentB 10 12 18 3 2/12/2009 12:29
5xxxx17 StudentB 10 12 19 3 2/17/2009 13:30
5xxxx17 StudentB 10 12 21 3 2/23/2009 13:40
5xxxx17 StudentB 11 12 22 4 2/25/2009 13:42
5xxxx17 StudentB 11 12 23 4 2/27/2009 12:06
5xxxx17 StudentB 11 12 24 4 3/2/2009 13:39
5xxxx17 StudentB 12 12 24 5 3/13/2009 12:01
5xxxx17 StudentB 14 12 24 7 3/17/2009 14:11
5xxxx17 StudentB 14 12 26 3 3/19/2009 13:59
5xxxx17 StudentB 14 12 27 3 3/25/2009 12:37
5xxxx17 StudentB 14 12 28 3 3/31/2009 11:54
5xxxx17 StudentB 15 12 24 4 3/18/2009 13:41
5xxxx17 StudentB 15 12 28 4 4/1/2009 12:53
5xxxx17 StudentB 15 12 29 4 4/2/2009 12:32
5xxxx17 StudentB 16 12 30 5 4/16/2009 13:27