This SQL will return a table with columns Department and Head Count

The source table are Engagement and Department.

• Engagement stores the employment period for an employee in a department.
• Department is a list of department names.

-- We will be using GETDATE() more than once therefore store GETDATE() in a variable.
DECLASE @date DATETIME = GETDATE()


SELECT d.Name   AS Department,
       COUNT(*) AS [Head Count]
FROM   Engagement e
JOIN   Department d
  ON   e.DepartmentId = d.id
WHERE  (e.DateStart <= @date OR e.DateStart is null)
AND    (e.DateEnd > @date OR e.DateEnd is null)
GROUP BY d.Name
ORDER BY [Head Count]