Tags

In Michigan, and a few other states, K12 school systems are required to report attendance on the Count Day . This is an important event, which weighs on funding and success of the institutions. As part of the requirements, you need to provide evidence of online attendance in the Learn system.

How about using Blackboard Data to find student submissions for a specific assignment as the count day indicator? You can look for a specific assignment and submissions on that day.

Let’s start by exploring the submission table:


select s.* from cdm_lms.submission s

We have a few important pieces of data here including a link to person_course table, which identifies both a person and a course, where the assignment was submitted.

Let’s connect a few tables together to get the submissions, course info, and people information.
Two lines — mean a comment. You can narrow down your query to a specific semester or person for data sampling or validation.

select
p.first_name, p.last_name, p.email,
c.course_number, ci.name, s.submitted_time from
cdm_lms.course_item ci, cdm_lms.submission s, cdm_lms.person_course pc, cdm_lms.course c, cdm_lms.person p
where
s.person_course_id = pc.id and s.course_item_id = ci.id and pc.person_id=p.id
and pc.course_id=c.id
--and c.stage:batch_uid like '2019.fall.%' --and p.last_name = 'Machajewski'

Notice, that we can have multiple submissions to the same assignment, so we use the max() function to group them.

select
 p.first_name, p.last_name, p.email,
 c.course_number, ci.name, max(s.submitted_time)
from
 cdm_lms.course_item ci, cdm_lms.submission s,
 cdm_lms.person_course pc, cdm_lms.course c, cdm_lms.person p
where
 s.person_course_id = pc.id
 and s.course_item_id = ci.id
 and pc.person_id=p.id
 and pc.course_id=c.id

 group by
 p.first_name, p.last_name, p.email, c.course_number, ci.name

What if we want to add a list of students who have not submitted anything yet. We are going to list all students enrolled, then exclude anyone with a submission.

select
p.first_name, p.last_name, p.email, c.course_number from
cdm_lms.person_course pc, cdm_lms.course c, cdm_lms.person p where
pc.person_id=p.id
and pc.course_id=c.id
and pc.id not in (select person_course_id from cdm_lms.submission s wh
--and c.stage:batch_uid like '2019.fall.%' 
-- and p.last_name = 'Machajewski'

Now, let’s put both of these queries together so we can see on one list students who submitted assignments and those who did not. In order to do so we need to have an equal list of columns in the select list, then use the UNION expression (ex: ” as name, ” as date). The columns have to be of the same type, so we will convert date to text type with ::text.

select
p.first_name, p.last_name, p.email,
c.course_number, ci.name, max(s.submitted_time)::text as date
from
cdm_lms.course_item ci, cdm_lms.submission s, cdm_lms.person_course pc, cdm_lms.course c, cdm_lms.person p where
s.person_course_id = pc.id
and s.course_item_id = ci.id
and pc.person_id=p.id
and pc.course_id=c.id
group by
p.first_name, p.last_name, p.email, c.course_number, ci.name union
select
p.first_name, p.last_name, p.email, c.course_number, '' as name, '' as date
from
cdm_lms.person_course pc, cdm_lms.course c, cdm_lms.person p where
pc.person_id=p.id
and pc.course_id=c.id
and pc.id not in
 (select person_course_id from
cdm_lms.submission s where course_id = c.id)

Here you can add limitation to a specific semester, specific day, and assignment name.