I need to speed this query up. No matter what I try, I cannot filter out the UserId's that are in the role 'V2_Corporate'
There are multiple roles assigned to the user, I need to know the logins in the EventLog table where user is not in the 'V2_Corporate' role...
Who can help me speed this query up as it currently takes 28 secs to run...
SELECT UL.LogUserID as [UserId], U.FirstName, COUNT(*) AS LoginCount
FROM dbo.EventLog UL
Inner Join dbo.Users U on U.UserId = UL.LogUserID
WHERE ((LogCreateDate >= '9/1/2013')
AND (LogCreateDate <= '9/30/2013'))
AND (LogTypeKey = 'LOGIN_SUCCESS')
AND (UL.LogUserID NOT IN
FROM dbo.UserRoles UR
INNER JOIN dbo.Roles R ON UR.RoleID = R.RoleID
WHERE (R.RoleName LIKE 'V2_Corporate%')))
GROUP BY UL.LogUserID, U.FirstName
ORDER BY logincount DESC