SELECT count(ct.Viewed) as Not_viewed,e.Emp_Last AS Last_Name, e.Emp_First AS First_Name
FROM #Request.prefix_db_agency#.communication_to ct
JOIN #Request.prefix_db_agency#.Communication c on c.Comm_Id = ct.comm_id
JOIN #Request.prefix_db_lookup#.pEmployee e on e.Emp_Id = ct.Employee_Id
WHERE ct.Viewed = 'N' AND e.Emp_Id= c.Employee_Id
AND c.status = 0 AND e.status = 0
AND c.Comm_Date >= '2022-01-01'
GROUP BY e.Emp_Id
ORDER BY e.Emp_Last, e.Emp_First