0

This may be really simple and I'm probably overthinking it. I have to create a variable that stores the number of employees who have been employed with the company for more than 3 years.

Here's what I came up with since the original table does not have the years stored, only a column with the hire date. 

DECLARE @YearsEmployed int,
 @Plus3 int,
 @EmpCount int
 SET @YearsEmployed =   (SELECT DATEDIFF(YY,HireDate,GetDate())
    FROM employees)
 SET @Plus3 = @YearsEmployed > 3
 SET    @EmpCount = COUNT(@Plus3)

 SELECT @EmpCount

I've already gotten the "Incorrect syntax near '>'" error and I'm at a loss on how to proceed. Any assistance would be greatly appreciated.

lc.
  • 113,939
  • 20
  • 158
  • 187
user3745347
  • 15
  • 1
  • 4

2 Answers2

1

The below query will give you count employees who have been employed with the company for more than 3 years:

       DECLARE @EmpCount int

       SET @EmpCount =   (SELECT count(DATEDIFF(YY,HireDate,GetDate()))
       FROM employees where DATEDIFF(YY,HireDate,GetDate()) > 3)

       SELECT @EmpCount

COUNT and the condition to pick employees who have worked for more than 3 years can be included within the query and stored in a variable rather using multiple variables and then getting count of those.

Thanks

hsd
  • 56
  • 4
0

Seems like a simple query to me:

select count(1)
from employees
where HireDate < dateadd(year, -3, getdate())

Note I have opted for dateadd with the current date, rather than a datediff expression on the column value since the latter is not SARGable.

As far as the code you have written the error comes from trying to assign the result of a boolean expression (@YearsEmployed > 3) to a variable. Furthermore, the query is SET @YearsEmployed = ... will fail if there is more than one row in employees. Additionally, I don't believe COUNT() can be used outside of a query - and even if it can, it's not going to do anything useful.

lc.
  • 113,939
  • 20
  • 158
  • 187