list of managers with total employees and how many of those employees have had their annual review this month

Question

I'm using sql-server. I have 2 tables an employee table and a comments table. The employee table lists the employee and their manager. When a manager gives an employee their annual review, they put a comment into the comments table of a certain type.

I want a list of managers along with a total count of employees for that manager and then a third column that lists a count of employees for which the manager has put a comment of a certain type into the comments table after a certain date.

This gets me a list of managers and total number of employees:

select manager, count(*) as total_employees
 from employee
 where 
  is_active='Y'
  and manager is not null
  group by manager

manager	total_employees
jones	7
smith	3
johnson	15
nelson	14

That works. Now, I need to find each employee in the comments table and count how many employees have a comment of a certain type. What I'm looking for is

manager	total_employees	completed
jones	7	3
smith	3	3
johnson	15	10
nelson	14	0

select manager, count(employee.id) as total_employees, count(comments) as completed
  from employee
  join comments on comments.parent_row_id=employee.id
    where
  is_active='Y'
  and manager is not null
  and comments.comment_type_id='REVIEW'
  and comments.entered_date > '11/01/2022'
  group by manager

The above query gives me:

manager	total_employees	completed
jones	5	5
johnson	1	1

If there's no comment in the comments table, it leaves the manager out entirely and the total_employees is now a count of employees with comments instead of total count of employees for the manager.

Answer 1

First let's set up some example DDL and DML. It's really helpful to have this when asking questions like this, so the folks who might be able to answer can reproduce it easily:

DECLARE @employees TABLE (EmployeeID INT IDENTITY, Name NVARCHAR(50), ManagerID INT)
DECLARE @comments TABLE (CommentID INT IDENTITY, EmployeeID INT, DateTimeUTC DATETIME, Comment NVARCHAR(50), CommentType NVARCHAR(10))

INSERT INTO @employees (Name, ManagerID) VALUES
('Manager Pike', NULL),('Manager Nachayev', NULL),('Manager Forrest', NULL),
('Employee James', 1),('Employee Spock', 1),('Employee Scottie', 1),
('Employee Jean-Luc', 2),('Employee William', 2),('Employee Geordie', 2),
('Employee Jonathon', 3),('Employee Tpol', 3),('Employee Trip', 3)

INSERT INTO @comments (EmployeeID, DateTimeUTC, Comment, CommentType) VALUES
(4, GETUTCDATE(), 'Too friendly with aliens', 'REVIEW'),
(5, GETUTCDATE(), 'Perfectly logical', 'REVIEW'),
(6, GETUTCDATE(), 'Needs more power', 'REVIEW'),
(7, GETUTCDATE(), 'Not the same since the borg', 'REVIEW'),
(11, GETUTCDATE(), 'Missing in Action', 'REVIEW')

Here we have some managers and their employees defined in the table, and some of them have completed the reviews type comments already.

To see who still has outstanding reviews we could query the data like this:

SELECT m.Name, COUNT(e.EmployeeID) AS TotalEmployees, COUNT(c.CommentID) AS Completed
  FROM @employees e
    INNER JOIN @employees m
      ON e.ManagerID = m.EmployeeID
    LEFT OUTER JOIN @comments c
      ON e.EmployeeID = c.EmployeeID
      AND c.CommentType = 'REVIEW'
GROUP BY m.Name

We're starting at the employee table, finding the rows for their managers, and then fetching the comments if they exist. We're using a LEFT OUTER JOIN because we want the employee rows even if there aren't any comments.

Name                TotalEmployees  Completed
---------------------------------------------
Manager Forrest     3               1
Manager Nachayev    3               1
Manager Pike        3               3

Looks like Pike is on top of his game, but the other two have some work to do.