More actions
Assignment Due Status displays the status of whether a student has submitted a particular assignment after the assignment has been published in a Canvas course.
| Metadata | |||||||||
|---|---|---|---|---|---|---|---|---|---|
| Data Custodian | |||||||||
| Center for Academic Technologies | |||||||||
| Data Source | |||||||||
| |||||||||
| Logical Transformation / Calculation | |||||||||
CASE
WHEN a.submission_types IN (
'none',
'not_graded'
) THEN
'OnTime'
WHEN coalesce(assgn_ovrd.due_at, a.due_at) IS NULL THEN
'OnTime'
WHEN coalesce(assgn_ovrd.due_at, a.due_at) IS NOT NULL
AND s.submitted_at IS NULL
AND coalesce(assgn_ovrd.due_at, a.due_at) > current_date() THEN
'OnTime'
WHEN coalesce(assgn_ovrd.due_at, a.due_at) IS NOT NULL
AND s.submitted_at IS NOT NULL
AND s.submitted_at <= coalesce(assgn_ovrd.due_at, a.due_at) THEN
'OnTime'
WHEN coalesce(assgn_ovrd.due_at, a.due_at) IS NOT NULL
AND s.submitted_at IS NOT NULL
AND s.submitted_at > coalesce(assgn_ovrd.due_at, a.due_at) THEN
'Late'
WHEN coalesce(assgn_ovrd.due_at, a.due_at) IS NOT NULL
AND s.submitted_at IS NULL
AND coalesce(assgn_ovrd.due_at, a.due_at) < current_date() THEN
'Missing'
END AS status
FROM WC_LMS_DTL_ASSGN_LKP f
LEFT JOIN course_dim c ON f.course_id = c.id
LEFT JOIN user_dim u ON f.user_id = u.id
LEFT JOIN assignment_dim a ON f.assignment_id = a.id
LEFT JOIN enrollment_dim e ON f.enrollment_id = e.id
LEFT JOIN enrollment_term_dim et ON f.enrollment_term_id = et.id
LEFT JOIN submission_dim s ON f.submission_id = s.id
LEFT JOIN quiz_dim q ON f.quiz_id = q.id
LEFT JOIN quiz_submission_dim qs ON f.quiz_submission_id = qs.id
LEFT JOIN WC_LMS_USER_SCHOLAR_LKP sch ON sch.user_id = u.id
--LEFT JOIN CV_COURSE_CLASS_LKP cls ON cls.course_code = substr(c.name, 0, instr(c.name, ' ', 1, 2) - 1)
--AND cls.class_num = rtrim(ltrim(substr(c.name, - 6)))
LEFT JOIN WC_LMS_ASSIGN_DUE_LKP assgn_ovrd ON a.id = assgn_ovrd.assignment_id
WHERE 1 = 1 AND c.workflow_state <> 'deleted' AND u.workflow_state = 'registered' AND a.workflow_state = 'published' AND c.start_at IS NOT NULL AND c.account_id = 22630000000000001 AND c.sis_source_id IS NOT NULL AND e.workflow_state = 'active' AND e.type = 'StudentEnrollment'AND s.workflow_state <> 'deleted' | |||||||||
| Dataset Folder and Field Name | |||||||||
| |||||||||
Examples
- Missing (no submission after due date)
- On Time (submitted by due date)
- Late (submitted after due date)