Arena Active Users (In Last Hour)

WITH cte AS
(
SELECT MAX(Date) AS last_active, person_id
FROM (SELECT MAX(dbo.core_person.date_modified) AS Date, dbo.secu_login.person_id, 'core_person' AS [Table]
FROM dbo.core_person INNER JOIN
dbo.secu_login ON dbo.core_person.modified_by = dbo.secu_login.login_id INNER JOIN
dbo.secu_person_role ON dbo.secu_login.person_id = dbo.secu_person_role.person_id
GROUP BY dbo.secu_login.person_id
UNION ALL
SELECT MAX(dbo.core_person_history.date_modified) AS Expr2, secu_login_2.person_id, 'core_person_history' AS Expr1
FROM dbo.core_person_history INNER JOIN
dbo.secu_login AS secu_login_2 ON dbo.core_person_history.modified_by = secu_login_2.login_id INNER JOIN
dbo.secu_person_role AS secu_person_role_3 ON secu_login_2.person_id = secu_person_role_3.person_id
GROUP BY secu_login_2.person_id
UNION ALL
SELECT date_created, person_id, 'Role Added' AS Expr1
FROM dbo.secu_person_role AS secu_person_role_1
WHERE (role_id = 85)
UNION ALL
SELECT MAX(view_datetime) AS Expr2, source_person_id, 'core_person_viewed_by' AS Expr1
FROM dbo.core_person_viewed_by
GROUP BY source_person_id
UNION ALL
SELECT last_login_date, person_id, 'Last Login' AS Expr1
FROM dbo.secu_login AS secu_login_3
UNION ALL
SELECT MAX(dbo.smgp_member.date_modified) AS Expr2, secu_login_1.person_id, 'smgp_member' AS Expr1
FROM dbo.smgp_member INNER JOIN
dbo.secu_login AS secu_login_1 ON dbo.smgp_member.modified_by = secu_login_1.login_id
GROUP BY secu_login_1.person_id) AS A
WHERE (Date > DATEADD(MINUTE, - 60, GETDATE()))
GROUP BY person_id
)

SELECT c.person_id,
case when core_person.nick_name > '' then core_person.nick_name + ' ' + core_person.last_name
else core_person.first_name + ' ' + core_person.last_name end as name, c.last_active
FROM cte c INNER JOIN
core_person ON c.person_id = core_person.person_id
order by last_name