WITH attSummary AS(
  SELECT DISTINCT
	  students.lastfirst,
	  students.id,
	  attendance.att_date
  FROM attendance
  JOIN attendance_code ON attendance_code.id = attendance.attendance_codeid
  JOIN students on attendance.studentid = students.id
  JOIN terms ON terms.schoolid = attendance.schoolid
	  AND terms.isyearrec = 1
	  AND sysdate between terms.firstday AND terms.lastday
	  AND attendance.att_date BETWEEN terms.firstday AND terms.lastday
  WHERE attendance_code.att_code IN ('AU','TR')
), studentTotals AS(
SELECT
	lastfirst,
	id,
	count(*) totalDays
FROM attSummary
GROUP BY lastfirst, id
)
SELECT lastfirst, id, totalDays
FROM studentTotals
WHERE totalDays >= 7