-
Missing Assignments
~[tlist_sql;
SELECT
c.course_name,
pga.name assignment,
to_char(pga.POINTSPOSSIBLE) earnposs,
pgc.name category,
pga.datedue
FROM CC
INNER JOIN PGAssignments PGA on cc.sectionid = pga.sectionid and pga.datedue >= (SELECT min(date1) from termbins where schoolid=~(curschoolid) and yearid = ~(curyearid) and upper(storecode) = upper('~(current.final.grade.name)')) and pga.datedue <= (SELECT max(date2) from termbins where schoolid=~(curschoolid) and yearid = ~(curyearid) and upper(storecode) = upper('~(current.final.grade.name)'))
INNER JOIN SYNC_SECTIONENROLLMENTMAP sync_cc on cc.dcid = sync_cc.ccdcid and CC.studentid = ~(curstudid) AND CC.dateenrolled <= trunc(sysdate) and cc.dateleft >= trunc(sysdate)
INNER JOIN SECTIONS sec on CC.sectionid = sec.id
INNER JOIN COURSES c on sec.course_number = c.course_number
INNER JOIN PGCategories PGC on pga.pgcategoriesid = pgc.id
LEFT OUTER JOIN SYNC_PGASSIGNMENTSMAP sync_pga on pga.dcid = sync_pga.pgassignmentsdcid
LEFT OUTER JOIN PSM_ASSIGNMENTSCORE PSMAS ON sync_pga.sectionassignmentid = psmas.sectionassignmentid and sync_cc.sectionenrollmentid = psmas.sectionenrollmentid
LEFT OUTER JOIN SECTIONSCORESID SSI ON sec.id = ssi.sectionid and cc.studentid = ssi.studentid
LEFT OUTER JOIN SECTIONSCORESASSIGNMENTS SSA ON pga.id = ssa.assignment and ssi.dcid = ssa.fdcid
WHERE
NVL(psmas.ISMISSING,0) = 1
AND
CASE WHEN PSMAS.EXEMPT != 1 THEN 1
WHEN PSMAS.EXEMPT = 1 and (PSMAS.SCORE is null or PSMAS.ACTUALSCOREENTERED is null) THEN 2 ELSE 1 END = 1
AND
CASE
WHEN PGA.PUBLISHSTATE = 1 THEN PGA.PUBLISHONSPECIFICDATE
WHEN PGA.PUBLISHSTATE = 2 THEN (PGA.DATEDUE - PGA.PUBLISHDAYSBEFOREDUE)
WHEN PGA.PUBLISHSTATE = 3 THEN PGA.DATEDUE
ELSE PGA.DATEDUE END <= SYSDATE
order by datedue desc, pga.name;nonemessage=- You have no assignments marked missing.
]
-
~(c.course_name;t)
~(assignment;t)
Points: ~(earnposs;t)
Cat: ~(category;t) Due: ~(duedate;d)
[/tlist_sql]