Ektron File Extension Asset Inventory

Following the logic in a previous post, File Extension, this query will list Ektron asset file extensions and count them.

SELECT (
		CASE
			WHEN handle LIKE '%.%'
				THEN reverse(left(reverse(handle), charindex('.', reverse(handle)) - 1))
			ELSE ''
			END
		) AS FilePath
	,COUNT(*) AS 'Total Assets'
FROM AssetDataTable
GROUP BY (
		CASE
			WHEN handle LIKE '%.%'
				THEN reverse(left(reverse(handle), charindex('.', reverse(handle)) - 1))
			ELSE ''
			END
		)
ORDER BY FilePath

SELECT (
		CASE
			WHEN handle LIKE '%.%'
				THEN reverse(left(reverse(handle), charindex('.', reverse(handle)) - 1))
			ELSE ''
			END
		) AS FilePath
	,CASE
		WHEN [content_status] = 'A'
			THEN 'Approved'
		WHEN [content_status] = 'O'
			THEN 'Checked Out'
		WHEN [content_status] = 'I'
			THEN 'Checked In'
		WHEN [content_status] = 'S'
			THEN 'Submitted for Approval'
		WHEN [content_status] = 'M'
			THEN 'Marked for Deletion'
		WHEN [content_status] = 'P'
			THEN 'Pending Go Live Date'
		WHEN [content_status] = 'T'
			THEN 'Awaiting Completion of Associated Tasks'
		WHEN [content_status] = 'D'
			THEN 'Pending Deletion'
		END AS ContentStatus
	,COUNT(*) AS 'Total Assets'
FROM AssetDataTable
INNER JOIN content c ON c.asset_id = AssetDataTable.id
GROUP BY (
		CASE
			WHEN handle LIKE '%.%'
				THEN reverse(left(reverse(handle), charindex('.', reverse(handle)) - 1))
			ELSE ''
			END
		)
	,[content_status]
ORDER BY FilePath
	,ContentStatus