Create a Variable to store how many employees have been employed with the company for more than 3 years

Question

This is what I have so far.

DECLARE @EmployeeLengthAfterHire INT
SET @EmployeeLengthAfterHire = (
    SELECT  EmpID,DATEDIFF(YY,HireDate,GETDATE()) AS lengthofEmployYY
    FROM HospitalStaff
    GROUP BY HireDate, EmpID
    HAVING DATEDIFF(YY,HireDate,GETDATE()) > 3
)

SELECT  @EmployeeLengthAfterHire

https://stackoverflow.com/questions/3974683/how-to-set-variable-from-a-sql-query — chandu komati, Aug 07 '20 at 07:36
[Learn to ask smart questions](http://www.catb.org/esr/faqs/smart-questions.html). You obviously encountered an error but neither mentioned that very important fact nor included the error message. And suggest you use the complete datepart rather than the abbreviation. It makes your code so much more readable - especially when you use some whitespace (rather than cramming everything together). — SMor, Aug 07 '20 at 12:28

score 1 · Accepted Answer · answered Aug 07 '20 at 05:31

1

I can see that you are trying to assign 2 value (EmpID and lengthofEmployYY) in your declared parameter which is not supported. As per my understanding, you can try this below script-

DECLARE @EmployeeLengthAfterHire INT
SET @EmployeeLengthAfterHire = (
    SELECT  COUNT(*)
    FROM HospitalStaff
    GROUP BY HireDate, EmpID
    HAVING DATEDIFF(YY,HireDate,GETDATE()) > 3
)

SELECT  @EmployeeLengthAfterHire

answered Aug 07 '20 at 05:31

mkRabbani

16,295
2
15
24

1

So for a scalar variable I need to assign one parameter in the select statement? Thus producing a single row and column? – Bali Aug 07 '20 at 06:01
@Bali . . . You accepted this answer and it returns errors (just like your original query): https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=8ece282bd33066b815b9b70f883c221b. – Gordon Linoff Aug 07 '20 at 12:21
@gordon i didn't cross checked his query as no sample data is here. Best option you guide me pointing the error and how i should change my query. But you are approching like op did it worng by accepting my answer where as op already mentioned that the guideline did the job for him/her. :( – mkRabbani Aug 07 '20 at 13:56

score 0 · Answer 2 · answered Aug 07 '20 at 12:19

Create a Variable to store how many employees have been employed with the company for more than 3 years

If you want to set a variable to a count, you have two problems:

Your subquery has more than one column.
Your subquery probably returns more than one row.

You can use:

SET @EmployeeLengthAfterHire = (
     SELECT COUNT(*)
     FROM HospitalStaff
     WHERE DATEDIFF(YEAR, HireDate, GETDATE()) > 3
    )

That said, your query is not calculating more than 3 years. It is calculating the number of year boundaries between two dates. So, 2019-12-31 and 2020-01-01 would be "1" year apart by this logic.

I would recommend:

SET @EmployeeLengthAfterHire = (
     SELECT COUNT(*)
     FROM HospitalStaff
     WHERE HireDate < DATEADD(YEAR, -3, HireDate)
    );

I'm not sure why you named the column "EmployeeLengthAfterHire".

Create a Variable to store how many employees have been employed with the company for more than 3 years

2 Answers2