TB No DE Activity Report

gistfile1.sql

DECLARE @DaysBackToCheck INT
SET @DaysBackToCheck = 7
-- List of users with recent activity
WITH RecentUsers AS (
	SELECT u.UserID, u.UserName, u.Name, MAX(l.Created) AS LastTBUseDate 
	FROM LOGS l
	INNER JOIN LOG_TYPES lt ON l.LogTypeID = lt.LogTypeID
	INNER JOIN USERS u ON u.UserID = l.TimekeeperID
	WHERE lt.ParentLogTypeID = 1
	AND l.Created > DATEADD(day, -@DaysBackToCheck , GETUTCDATE())
	AND u.IsEnabled = 1 AND u.IsDeleted = 0
	GROUP BY u.UserID, u.UserName, u.Name
)
-- List of users with no DE Activity
SELECT UserName, Name, (
	SELECT COUNT(*) FROM EVENTS
	WHERE EventTime > DATEADD(day, -@DaysBackToCheck , GETUTCDATE())
	AND EventTypeID NOT IN (2,4,5,6,7,8,17,20,996,997,998,999)
	AND UserID = TimekeeperID
	GROUP BY UserID
	) AS DEEventCount 
FROM RecentUsers
GROUP BY UserName, Name, UserID
HAVING (
	SELECT COUNT(*) FROM EVENTS
	WHERE EventTime > DATEADD(day, -@DaysBackToCheck , GETUTCDATE())
	AND EventTypeID NOT IN (2,4,5,6,7,8,17,20,996,997,998,999)
	AND UserID = TimekeeperID
	GROUP BY UserID
	) IS NULL
ORDER BY Name, UserName