WITH rc AS(
SELECT stu.dcid, stu.id studentid, stu.student_number, stu.lastfirst, stu.schoolid, schools.name as school, cc.dateenrolled, cc.dateleft
    FROM PS.students stu
    INNER JOIN cc on cc.studentid = stu.id
    INNER JOIN schools on schools.school_number = stu.schoolid
    WHERE cc.schoolid = 703 
   ~[if.is.a.school] and stu.schoolid=~(curschoolid)[/if]
),
attendance_dates as (
  SELECT distinct attendance.studentid, attendance.att_date
  FROM attendance
  INNER JOIN rc on rc.studentid = attendance.studentid
  WHERE attendance.att_date between rc.dateenrolled AND rc.dateleft and attendance.yearid = ~(curyearid)
 AND attendance.att_date <= sysdate
  AND to_char(attendance.att_date, 'MM/DD/YYYY') NOT IN ('01/22/2025', '01/06/2025', '12/23/2024')
),
rca as (
SELECT a.studentid, a.att_date, a.att_mode_code, a.schoolid, a.attendance_codeid, ac.att_code, a.att_comment, ac.description, CC.COURSE_NUMBER, c.course_name
  FROM PS.attendance a
  INNER JOIN rc on rc.studentid = a.studentid
  INNER JOIN attendance_code ac on ac.id = a.attendance_codeid
    INNER JOIN cc on CC.id = a.ccid
    INNER JOIN sections on sections.id = cc.sectionid
        INNER JOIN terms on terms.id = sections.termid AND terms.yearid = ~(curyearid)
INNER JOIN courses c on c.course_number = sections.course_number
  WHERE a.schoolid = 703 AND a.yearid = ~(curyearid)
),
school_att as (
SELECT a.studentid, a.att_date, a.att_mode_code, a.schoolid, a.attendance_codeid, ac.att_code, a.att_comment,ac.description, CC.COURSE_NUMBER, c.course_name
  FROM PS.attendance a
  INNER JOIN rc on rc.studentid = a.studentid
  INNER JOIN attendance_code ac on ac.id = a.attendance_codeid
    INNER JOIN cc on CC.id = a.ccid
    INNER JOIN sections on sections.id = cc.sectionid
    INNER JOIN terms on terms.id = sections.termid AND terms.yearid = ~(curyearid)
INNER JOIN courses c on c.course_number = sections.course_number
  WHERE a.schoolid = rc.schoolid AND a.yearid = ~(curyearid)
)

SELECT distinct  
    rc.school, 
    rc.student_number, 

    CHR (60) || 'a href=/admin/attendance/view/meeting.html?frn=001' || TO_CHAR (rc.dcid) || ' target=_blank' || CHR (62) || rc.lastfirst || CHR (60) || '/a' || CHR (62) AS "Student",

    to_char(rc.dateenrolled, 'MM/DD/YYYY') "Date Enrolled",
    sa.course_name,
    to_char(ad.att_date, 'MM/DD/YYYY') as "attendance date",  
    NVL(rca.att_code, 'Present') as "RC Code", 

    sa.att_code as "School Code", 
    to_char(rca.att_comment) as "RC Comment", 
    to_char(sa.att_comment) as "School Comment"
FROM rc
INNER JOIN attendance_dates ad on ad.studentid = rc.studentid
LEFT JOIN rca on rca.studentid = ad.studentid and rca.att_date = ad.att_date
LEFT JOIN school_att sa on sa.studentid = ad.studentid AND sa.att_date = ad.att_date
WHERE NVL(rca.att_code, 'NULL') <>  NVL(sa.att_code, 'NULL')
ORDER BY rc.student_number