Community HelpMarissa Dimino
I need an SQL query to get hits for a specific module on Blackboard.
I'm using MS sql server 2012 management studio
Can you clarify what you mean by "hits on a specific module"? Modules are usually displayed as one element of a module page, and I don't think there is any individual module display tracking going on.
We have a community module that is open to all University students. I need to get the total number of students who visited the modules since beginning of April 2016.
I'm sorry, by module, i meant to say a course.
We have a community course that is open to all University students. I need to get the total number of students who visited the modules since beginning of April 2016.
Untested but should do the trick:
select count(pk1) from users where exists (
select 1 from activity_accumulator
where user_pk1 = users.pk1
and course_pk1 = yourCoursePk1
and timestamp >= to_date('01-APR-2016','DD-MON-YYYY')
Thank you Cliff, I edited your script a bit and its working
select count(pk1) from users
where exists (
select 1 from activity_accumulator, course_main
Where user_pk1 = users.pk1 /* This line line gives you unique hits and if you remove it, you get all hit meaning a a student who went into the course six times will contribute to six hits */
and course_id = 'O201602325'
and timestamp >= '2016-04-15 00:00:000' and timestamp <= '2016-04-30 00:00:000');
course_pk1 = '87' /* course_pk1 didn't work so i used the course_id instead*/
Now i need to prove that its working. How do i list all the student who visited the module in the same script
That line gives you unique hits, but it's within an exists statement. An exists statement returns true as long as there's at least one result for it. The count(pk1) statement shows the number of users who visited the module at least once.
If you want to see who those users specifically were, you can change count(pk1) to user_id.
I reverted back to your original script. its works
select user_id from users where exists (
select 1 user_pk1 from activity_accumulator
where user_pk1 = users.pk1
and course_pk1 = '2410'
and timestamp >= '2016-05-01 00:00:000')
I can see all the people who visited the module. It gave me a list of 10587 users. That's 10587 counts.
If its unique hits, why its not listing some users more than once as they have visited more than once.
The exists statement just says "is there at least one result for this?" In other words, the script is saying, " Give me a list of all the users where there's at least one entry in the activity accumulator table for this course in this date range.
I need to see if i can make it count all the unique sessions then. I can use that as the hits per course because every login has a session_id.
select DISTINCT session_id, user_id from activity_accumulator, users
Where (course_pk1 = '8479' and event_type = 'Course_Access'
and timestamp >= '2016-04-15 00:00:000'
and user_pk1 = users.pk1
and institution_roles_pk1 ='1')
Order by user_id ASC;
This has given me all the sessions. I got the total count of sessions and the students link to the different sessions. Those who visited the course more than once are list as every session is link to a student.
institution_roles_pk1 ='1'. This was just to pull session by students
Now i can use this give a report with supporting evidence
Let me know what you think.
Retrieving data ...