CODEWITHSUNDEEP
sqlvariablescountdatediff
user3745347
user3745347

Reputation: 15

Create a variable to store a count for DATEDIFF greater than 3

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.

Upvotes: 0

Views: 2221

Answers (2)

hsd
hsd

Reputation: 56

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

Upvotes: 1

lc.
lc.

Reputation: 116528

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.

Upvotes: 0

Related Questions