TSQL SELECT statement to list a column calculated based on the value of a field 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.

It should be enough to get the count(distinct EmployeeID) where StartingDate &gt; 1/1/2016. But, I can't figure out the query. Could anyone please help?
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

CREATE TABLE #Table1
(
 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
AS
(
  SELECT t.ProjectID,
         COUNT(*) OVER (PARTITION BY t.EmployeeID ORDER BY t.StartingDate) AS project_count
  FROM #Table1 t
  WHERE t.StartingDate &gt; '1/1/2016'
)
SELECT t.ProjectID,
       t.EmployeeID,
       t.StartingDate,
       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.