How can I determined which users with a specific RoleID that's not been active within a time interval? And which home department do they belong to?

Question

This script below will tell me how many non active users with a certain RoleID there's been within a timeframe. When I run the script below. It will also tell me which home department each user belongs to.

FROM [dbo].[tbl_Users] u
WHERE NOT EXISTS (SELECT 1
                  FROM [dbo].[CaseTable] ct
                  WHERE ct.UserID = u.UserID AND
                        ct.CreationDate between '2019-01-01' and '2019-12-31' 
             ) AND
     EXISTS (SELECT 1
             FROM tbl_UsersBelongsTo ubt
             WHERE ubt.RoleID = 6 AND ubt.userId = u.userId          
            ); 

This second script is a INNER JOIN where I can determine which DefaultDepartmentId (HomeDepartment) it belongs to to. For instance DefaultDepartmentID = 1 is named Testdepartment in table tbl_Departments. The relationship here between the two tables is DepartmentID.

FROM tbl_Users
INNER JOIN tbl_Departments ON tbl_Users.DefaultDepartmentID=tbl_Departments.DepartmentID

After running this script. I can figure which DefaultDepartmentID it belongs to, for instance that DefaultDepartmentId=3 is named Testdepartment2

So my question is, how can I Integrate these to queries together? Thanks in advance.

Answer 1

Do the JOIN :

SELECT u.*, d.*
FROM [dbo].[tbl_Users] u INNER JOIN
     tbl_Departments d
     ON d.DepartmentID = u.DefaultDepartmentID
WHERE . . ;