Content Audit

SELECT COUNT(*) AS "# Of Templates"
FROM templates_tbl

SELECT DISTINCT template_id AS "Template ID"
	,count(*) AS "Usage"
FROM content
GROUP BY template_id
ORDER BY "Template ID"

SELECT [content].content_id
	,[content].content_title
	,templates_tbl.template_filename
FROM [content]
INNER JOIN templates_tbl ON [content].template_id = templates_tbl.template_id
WHERE templates_tbl.template_filename LIKE '%templates%'
ORDER BY templates_tbl.template_filename
	,[content].content_id
	,[content].content_title

SELECT count(*) AS "Total Content Items"
FROM content

SELECT CASE content_type
		WHEN - 1
			THEN CAST(content_type AS VARCHAR(5)) + ' - AllTypes'
		WHEN 1
			THEN CAST(content_type AS VARCHAR(5)) + ' - Content'
		WHEN 2
			THEN CAST(content_type AS VARCHAR(5)) + ' - Forms'
		WHEN 3
			THEN CAST(content_type AS VARCHAR(5)) + ' - Archive Content'
		WHEN 4
			THEN CAST(content_type AS VARCHAR(5)) + ' - Archive Forms'
		WHEN 7
			THEN CAST(content_type AS VARCHAR(5)) + ' - Library Item'
		WHEN 8
			THEN CAST(content_type AS VARCHAR(5)) + ' - Assets'
		WHEN 9
			THEN CAST(content_type AS VARCHAR(5)) + ' - Archive Assets'
		WHEN 12
			THEN CAST(content_type AS VARCHAR(5)) + ' - Archive Media'
		WHEN 99
			THEN CAST(content_type AS VARCHAR(5)) + ' - Non Library Content'
		WHEN 101
			THEN CAST(content_type AS VARCHAR(5)) + ' - Assets'
		WHEN 102
			THEN CAST(content_type AS VARCHAR(5)) + ' - Assets'
		WHEN 103
			THEN CAST(content_type AS VARCHAR(5)) + ' - Assets'
		WHEN 104
			THEN CAST(content_type AS VARCHAR(5)) + ' - Multimedia'
		WHEN 1111
			THEN CAST(content_type AS VARCHAR(5)) + ' - Discussion Topic'
		WHEN 3333
			THEN CAST(content_type AS VARCHAR(5)) + ' - Catalog Entry'
		END AS 'Content Type'
	,COUNT(*) AS 'Total Content Written'
FROM content c
GROUP BY c.content_type
ORDER BY 'Content Type'

SELECT c.user_id AS 'Content Author ID'
    ,(first_name + ' ' + last_name) AS 'Content Author'
    --,c.content_type AS 'Content Type'
    ,CASE content_type
        WHEN - 1
            THEN CAST(content_type AS VARCHAR(5)) + ' - AllTypes'
        WHEN 1
            THEN CAST(content_type AS VARCHAR(5)) + ' - Content'
        WHEN 2
            THEN CAST(content_type AS VARCHAR(5)) + ' - Forms'
        WHEN 3
            THEN CAST(content_type AS VARCHAR(5)) + ' - Archive Content'
        WHEN 4
            THEN CAST(content_type AS VARCHAR(5)) + ' - Archive Forms'
        WHEN 7
            THEN CAST(content_type AS VARCHAR(5)) + ' - Library Item'
        WHEN 8
            THEN CAST(content_type AS VARCHAR(5)) + ' - Assets'
        WHEN 9
            THEN CAST(content_type AS VARCHAR(5)) + ' - Archive Assets'
        WHEN 12
            THEN CAST(content_type AS VARCHAR(5)) + ' - Archive Media'
        WHEN 99
            THEN CAST(content_type AS VARCHAR(5)) + ' - Non Library Content'
        WHEN 101
            THEN CAST(content_type AS VARCHAR(5)) + ' - Assets'
        WHEN 102
            THEN CAST(content_type AS VARCHAR(5)) + ' - Assets'
        WHEN 103
            THEN CAST(content_type AS VARCHAR(5)) + ' - Assets'
        WHEN 104
            THEN CAST(content_type AS VARCHAR(5)) + ' - Multimedia'
        WHEN 1111
            THEN CAST(content_type AS VARCHAR(5)) + ' - Discussion Topic'
        WHEN 3333
            THEN CAST(content_type AS VARCHAR(5)) + ' - Catalog Entry'
        END AS 'Content Type'
    ,COUNT(*) AS 'Total Content Written'
FROM content c
INNER JOIN users u ON u.user_id = c.user_id
GROUP BY c.content_type
    ,c.user_id
    ,u.first_name
    ,u.last_name
ORDER BY 'Content Author'
    ,'Content Author ID'