canvas-data/mssql - assignment groups of master courses

assignment-groups.sql

SELECT
	canvas_course_id,
	course_name,
	ISNULL(formative,0) formative,
	ISNULL(summative,0) summative,
	ISNULL(other,0) other
FROM (	
	SELECT 
		canvas_course_id,
		course_name,
		assignment_id,
		CASE
			WHEN assignment_group_name IN ('Formative','Summative') THEN LOWER(assignment_group_name)
			ELSE 'other'
		END AS group_type
	FROM (
		SELECT
			course_dim.canvas_id AS canvas_course_id,
			course_dim.name AS course_name,
			assignment_dim.id AS assignment_id,
			assignment_group_dim.name AS assignment_group_name
		FROM CanvasLMS.dbo.assignment_dim
			LEFT JOIN CanvasLMS.dbo.assignment_group_dim ON (assignment_group_dim.id = assignment_dim.assignment_group_id)
			LEFT JOIN CanvasLMS.dbo.assignment_group_fact ON (assignment_group_fact.assignment_group_id = assignment_group_dim.id)
			LEFT JOIN CanvasLMS.dbo.course_dim ON (assignment_dim.course_id = course_dim.id)
			LEFT JOIN canvaslms.dbo.account_dim ON (course_dim.account_id = account_dim.id)
		-- sub account of master shells
		WHERE account_dim.canvas_id = 114238
	)x
)y
PIVOT (
    COUNT(assignment_id)
    FOR group_type IN ([formative],[summative],[other])
) AS pvt