I have a table named Table1, with three fields Project ID, EmployeeID, Starting Date. Need a select statement to list all columns of the table, and an additional column to show the number of project by employee since a year. See below figure.

Existing table

It should be enough to get the count(distinct EmployeeID) where StartingDate > 1/1/2016. But, I can't figure out the query. Could anyone please help?


1 Answer 1


If the project id is distinct this is easy to achieve with two window functions. Maybe not the best performance but just flows out of my fingers. Feel free to improve:

Setting up

 ProjectID int,
 EmployeeID int,
 StartingDate DateTime

INSERT INTO #Table1 (ProjectID, EmployeeID, StartingDate)
VALUES (1, 1001, '1/1/2005'),
       (2, 1003, '1/1/2019'), 
       (3, 1007, '1/1/2012'),
       (4, 1001, '1/1/2017'),
       (5, 1002, '1/1/2018'), 
       (6, 1003, '1/1/2015'), 
       (7, 1001, '1/1/2016'), 
       (8, 1002, '1/1/2018'), 
       (9, 1004, '1/1/2018'), 
       (10, 10001, '1/1/2018');

The query;

WITH cte_Count
  SELECT t.ProjectID,
         COUNT(*) OVER (PARTITION BY t.EmployeeID ORDER BY t.StartingDate) AS project_count
  FROM #Table1 t
  WHERE t.StartingDate > '1/1/2016'
SELECT t.ProjectID,
       MAX(project_count) OVER (PARTITION BY t.EmployeeID) AS project_count_since_2016
FROM #Table1 t
LEFT JOIN cte_Count c ON c.ProjectID = t.ProjectID

The first part just filters the projects that are not in the range you want. The second joins this information with the source table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.