title: Find employee tenure for a company
body: <p>I have written the following query to get the employees tenure yearwise.</p>
  
  <p>Ie. grouped by "less than 1 year", "1-2 years", "2-3 years" and "greater than 3 years".</p>
  
  <p>To get this, I compare with employee staffed <code>end_date</code>.</p>
  
  <p>But I am not able to get the correct result when comparing with staffed <code>end_date</code>.</p>
  
  <p>I have pasted the complete code below, but the count I am getting is not correct.</p>
  
  <p>Some employee who worked for more than 2 years is falling under &lt;1 year column.</p>
  
  <pre><code>    DECLARE @Project_Id Varchar(10)='ITS-004275';
  
  With Cte_Dates(Period,End_date,Start_date,Project_Id)
  As
  (
  SELECT '&lt;1 Year' AS Period, GETDATE() AS End_Date,DATEADD(YY,-1,GETDATE()) AS Start_date,@Project_Id AS Project_Id
  UNION
  SELECT '1-2 Years', DATEADD(YY,-1,GETDATE()),DATEADD(YY,-2,GETDATE()),@Project_Id
  UNION
  SELECT '2-3 Years', DATEADD(YY,-2,GETDATE()),DATEADD(YY,-3,GETDATE()),@Project_Id
  UNION
  SELECT '&gt;3 Years', DATEADD(YY,-3,GETDATE()),'',@Project_Id
  ),
  
  --select * from Cte_Dates
  --ORDER BY Start_date DESC
  
  
  Cte_Staffing(PROJECT_ID,EMP_ID,END_DATE) AS
  (
  SELECT FK_Project_ID,EMP_ID,MAX(End_Date)AS END_DATE FROM DP_Project_Staffing
  WHERE FK_Project_ID=@Project_Id
  GROUP BY FK_Project_ID,Emp_ID
  )
  
  
  
  SELECT D.PROJECT_ID,D.Start_date,D.End_date,COUNT(S.EMP_ID) AS Count,D.Period
  FROM Cte_Staffing S
  RIGHT JOIN Cte_Dates D
  ON D.Project_Id=S.PROJECT_ID
  AND S.END_DATE&lt;D.End_date AND S.END_DATE&gt;D.Start_date
  GROUP BY D.PROJECT_ID,D.Start_date,D.End_date,D.Period
  </code></pre>