Blackboard Learn WIP sql query - raw course content consumption data (ignores folder views)

activity_accumulator_by_course_pk1.sql

-- 2 unioned ACTIVITY_ACCUMULATOR queries, 1st [bb_bb60_stats] then [bb_bb60]
--DECLARE @Term varchar
DECLARE @TermName nvarchar(100)
DECLARE @CoursePK1 INT
DECLARE @StatsMax datetime
DECLARE @TimeMin datetime
DECLARE @TimeMax datetime

SET @TermName = 'Fall 2014' -- term name
SET @CoursePK1 =  111111 -- {single course pk1}
SET @StatsMax = (SELECT MAX(TIMESTAMP) FROM bb_bb60_stats.dbo.ACTIVITY_ACCUMULATOR)
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 )

 SELECT 
	aa.[TIMESTAMP]
    ,cc.[Title] 
    -- ,cc.[main_data] -- if you want more 
    , aa.[EVENT_TYPE]
    -- ,aa.[DATA] -- if you want more 
	,u.[USER_ID] ,u.[FIRSTNAME] ,u.[LASTNAME]  ,u.[EMAIL]
	, cc.[CNTHNDLR_HANDLE]
	, '---' as 'blank column'
	,u.[STUDENT_ID] 
	,aa.[PK1] as activity_pk1 ,aa.[COURSE_PK1],aa.[GROUP_PK1],aa.[FORUM_PK1],aa.[INTERNAL_HANDLE],aa.[CONTENT_PK1]
    ,aa.[SESSION_ID]
  FROM [bb_bb60_stats].[dbo].[ACTIVITY_ACCUMULATOR] aa
  INNER JOIN bb_bb60.dbo.USERS u on aa.USER_PK1 = u.PK1
  INNER JOIN bb_bb60.dbo.COURSE_CONTENTS cc on aa.CONTENT_PK1 = cc.PK1
 WHERE  
	EMAIL not like 'instructor@inst.edu'
    and  CNTHNDLR_HANDLE not like '%x-bb-folder'
    and COURSE_PK1 = @CoursePK1 and (TIMESTAMP >= @TimeMin and TIMESTAMP <= @StatsMax) 
-- if you want two seperate result sets, uncomment the below line and 
-- union
-- order by TIMESTAMP;
UNION
  SELECT 
	aa.[TIMESTAMP]
    ,cc.[Title] 
    -- ,cc.[main_data]  -- if you want more info
    , aa.[EVENT_TYPE]
    -- ,aa.[DATA] -- if you want more info
	,u.[USER_ID] ,u.[FIRSTNAME] ,u.[LASTNAME]  ,u.[EMAIL]
	, cc.[CNTHNDLR_HANDLE]
	, '---' as 'blank column'
	,u.[STUDENT_ID] 
	,aa.[PK1] as activity_pk1 ,aa.[COURSE_PK1],aa.[GROUP_PK1],aa.[FORUM_PK1],aa.[INTERNAL_HANDLE],aa.[CONTENT_PK1]
    ,aa.[SESSION_ID]
  FROM [bb_bb60].[dbo].[ACTIVITY_ACCUMULATOR] aa
  INNER JOIN bb_bb60.dbo.USERS u on aa.USER_PK1 = u.PK1
  INNER JOIN bb_bb60.dbo.COURSE_CONTENTS cc on aa.CONTENT_PK1 = cc.PK1
    WHERE EMAIL not like 'instructor@inst.edu'
    and  CNTHNDLR_HANDLE not like 'resource/x-bb-folder'
    and
    COURSE_PK1 = @CoursePK1 
    and (TIMESTAMP >= @StatsMax and TIMESTAMP <= @TimeMax) 
     
  order by TIMESTAMP;