Ektron Metrics – User Count By User Group

Part of the Ektron Metrics Script, this query will help with metrics to give you an idea of how often your users login.

SET NOCOUNT ON

DECLARE @month INT
	,@year INT
	,@start_date DATETIME
	,@end_date DATETIME

SET @month = (
		SELECT CASE
				WHEN MONTH(GETDATE()) = '1'
					THEN '12'
				ELSE MONTH(GETDATE()) - 1
				END
		)
--SELECT @month AS Month_To_Report
SET @year = (
		SELECT CASE
				WHEN MONTH(GETDATE()) = '1'
					THEN YEAR(GETDATE()) - 1
				ELSE YEAR(GETDATE())
				END
		)
--SELECT @YEAR AS Year_To_Report
SET @start_date = CAST(CAST(@month AS VARCHAR) + '/01/' + CAST(@year AS VARCHAR) AS DATETIME)
-- @start_date AS Start_Date
SET @end_date = (
		SELECT DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, @start_date) + 1, 0))
		)

--SELECT @end_date AS End_Date
--SELECT (CONVERT(VARCHAR(8), @start_date, 1) + ' - ' + CONVERT(VARCHAR(8), @end_date, 1)) AS 'Date Range'
SELECT DISTINCT ug.usergroup_name AS "Ektron User Group"
	,(
		SELECT COUNT(*)
		FROM user_to_group_tbl utgt1
		INNER JOIN usergroups ON utgt.usergroup_id = usergroups.usergroup_id
		INNER JOIN users u ON u.user_id = utgt1.user_id
		WHERE utgt.usergroup_id = utgt1.usergroup_id
			AND (
				u.date_created <= @end_date
				OR u.date_created IS NULL
				)
		) AS "Total Users"
	,(
		SELECT COUNT(*)
		FROM user_to_group_tbl utgt1
		INNER JOIN usergroups ON utgt.usergroup_id = usergroups.usergroup_id
		INNER JOIN users u ON u.user_id = utgt1.user_id
		WHERE utgt.usergroup_id = utgt1.usergroup_id
			AND (
				u.date_created <= @end_date
				OR u.date_created IS NULL
				)
			AND u.[user_deleted] = 0
		) AS "Account Status - Active"
	,(
		SELECT COUNT(*)
		FROM user_to_group_tbl utgt1
		INNER JOIN usergroups ON utgt.usergroup_id = usergroups.usergroup_id
		INNER JOIN users u ON u.user_id = utgt1.user_id
		WHERE utgt.usergroup_id = utgt1.usergroup_id
			AND (
				u.date_created >= @start_date
				OR u.date_created IS NULL
				)
			AND (
				u.date_created <= @end_date
				OR u.date_created IS NULL
				)
			AND u.[user_deleted] = 0
		) AS "Registered Witin The Month"
	,(
		SELECT COUNT(*)
		FROM user_to_group_tbl utgt1
		INNER JOIN usergroups ON utgt.usergroup_id = usergroups.usergroup_id
		INNER JOIN users u ON u.user_id = utgt1.user_id
		WHERE utgt.usergroup_id = utgt1.usergroup_id
			AND (
				u.date_created <= @end_date
				OR u.date_created IS NULL
				)
			AND u.[user_deleted] = 0
			AND convert(VARCHAR, LAST_LOGIN_DATE, 112) <= GETDATE() - 120
		) AS "Login Status - <= 120"
	,(
		SELECT COUNT(*)
		FROM user_to_group_tbl utgt1
		INNER JOIN usergroups ON utgt.usergroup_id = usergroups.usergroup_id
		INNER JOIN users u ON u.user_id = utgt1.user_id
		WHERE utgt.usergroup_id = utgt1.usergroup_id
			AND (
				u.date_created <= @end_date
				OR u.date_created IS NULL
				)
			AND u.[user_deleted] = 0
			AND convert(VARCHAR, LAST_LOGIN_DATE, 112) <= GETDATE() - 90
		) AS "Login Status - <= 90"
	,(
		SELECT COUNT(*)
		FROM user_to_group_tbl utgt1
		INNER JOIN usergroups ON utgt.usergroup_id = usergroups.usergroup_id
		INNER JOIN users u ON u.user_id = utgt1.user_id
		WHERE utgt.usergroup_id = utgt1.usergroup_id
			AND (
				u.date_created <= @end_date
				OR u.date_created IS NULL
				)
			AND u.[user_deleted] = 0
			AND convert(VARCHAR, LAST_LOGIN_DATE, 112) <= GETDATE() - 30
		) AS "Login Status - <= 30"
FROM user_to_group_tbl utgt
INNER JOIN usergroups ug ON utgt.usergroup_id = ug.usergroup_id
ORDER BY "Ektron User Group"