Assignment Due Status

From CSUMB Data Glossary
Jump to navigation Jump to search

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
Where Term Appears
CSUMB Data Warehouse Dashboards:
Other Reports:N/A
Data Custodian
Center for Academic Technologies
Data Source
Source System:LMS - Canvas
Source Table Name:N/A
Source Field Name:N/A
Census Process:N/A
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'
OBIEE Folder and Column
Folder Heading:N/A
Column Heading:N/A

Examples

  • Missing
  • On Time
  • Late

See Also