Blackboard Learn WIP sql query - last academically relevant activity by term for set of students

last academically relevant activity by term.sql

use bb_bb60_stats

-- TODO - can this be re-written (e.g. against uses first) so that
-- -- students with no data will still get a listing
-- -- only the most recent item is reported (most recent of every type of activity is currently reported

-- TODO parameterize studentID list 
--DECLARE @IDlist TABLE(studID nvarchar(10) NOT NULL)
--INSERT INTO @IDlist 
--	SELECT
--       {list of student ids}
--	AS studID

--- variables related to terms
DECLARE @TermName nvarchar(100)
SET @TermName = 'Summer II 2014'

-- dates are automatically generated from the term
DECLARE @TimeMin datetime
DECLARE @TimeMax datetime
DECLARE @TERMPK int
SET @TimeMin = (SELECT [start_date] FROM [bb_bb60].[dbo].[term] WHERE name = @TermName)
SET @TimeMax = (SELECT [end_date] FROM [bb_bb60].[dbo].[term] WHERE name = @TermName )
SET @TermPK = (SELECT pk1 FROM [bb_bb60].[dbo].[term] WHERE name = @TermName )

-- create temp table of students being audited 
declare @userData TABLE(
PK1 int NOT NULL 
, USER_ID nvarchar(50) NOT NULL
, BATCH_UID nvarchar(256) NOT NULL
, EMAIL varchar(100) NULL
, STUDENT_ID nvarchar(100) NOT NULL
, firstname nvarchar(100) NOT NULL
, lastname nvarchar(100) NOT NULL
)
-- POPULATE table var WITH STUDENTS FROM LIST
-- todo this should be parameterized and made to a stored procedure
INSERT INTO @userData
	SELECT PK1, USER_ID, BATCH_UID, EMAIL, STUDENT_ID, firstname, lastname
	FROM bb_bb60.dbo.USERS
	WHERE
	STUDENT_ID IN ('111111','222222','333333')

-- ok, lets get this going	
SELECT  u.PK1 ,
	u.firstname ,
        u.lastname ,
	u.user_id,    
        CASE WHEN t.course_item_type_pk1 = 'resource/x-bb-asmt-test-link'
             THEN 'Test'
             WHEN t.course_item_type_pk1 = 'resource/x-bb-assignment'
             THEN 'Assignment'
             WHEN t.course_item_type_pk1 = 'resource/x-bb-journallink'
             THEN 'Journal'
             WHEN t.course_item_type_pk1 = 'resource/x-bb-forumlink'
             THEN 'Discussion Board'
             WHEN t.course_item_type_pk1 = 'resource/x-bb-bloglink'
             THEN 'Blog'
             WHEN t.course_item_type_pk1 = NULL
             THEN '-- no activity --'
        END AS course_item_type_pk1 ,
        c.course_id , 
        c.course_name,
        c.batch_UID course_batch_uid ,
        MAX(s.submission_datetime) AS Latest_Submission
        
FROM    ods_submission s 
	INNER JOIN @userData u ON s.ls_user_pk1 = u.pk1
        INNER JOIN ods_course_tool t ON s.course_tool_pk1 = t.pk1
        INNER JOIN ods_ls_course c ON s.ls_course_pk1 = c.pk1
WHERE   
	-- IN subqury gets all course pks from term specified by @TermPK
	s.ls_course_pk1 IN ( SELECT [pk1] FROM [bb_bb60_stats].[dbo].[ods_ls_course] WHERE term_pk1 = @TERMPK )
        AND 
        submission_datetime BETWEEN @TimeMin AND @TimeMax -- dates input
GROUP BY u.pk1, u.firstname ,
        u.lastname ,
	u.user_id,
        c.course_id ,
        c.course_name,
        c.batch_uid ,
        t.course_item_type_pk1
ORDER BY u.PK1

-- uncomment to confirm all needed students are in @userData
--SELECT * FROM @userData ORDER BY PK1