New Error for me

[MMund]

Trying to run the following query:

select @ytd2 = sum(a.cur)
from py_ehearn a
join py_ehmast b on b.ehmast_id = a.ehmast_id
join py_earn_rule_id c on c.earn_rule_id = a.earn_rule_id
where c.cf_disearn = 1 and (b.paydate between @hiredate and dateadd(year,@hiredate,1)) and b.ee_id = @ee_id

and get the following error:
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

This is T-SQL, btw.

Not understanding this: I have code very similar to this in other places and do not get this error. Ideas, anyone?

TIA

 

[SQLDenis]

run this and you will see multiple rows


select sum(a.cur)
from py_ehearn a
join py_ehmast b on b.ehmast_id = a.ehmast_id
join py_earn_rule_id c on c.earn_rule_id = a.earn_rule_id
where c.cf_disearn = 1 and (b.paydate between @hiredate and dateadd(year,@hiredate,1)) and b.ee_id = @ee_id

you need to group by or your join is fubared

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[gmmastros]

dateadd(year,@hiredate,1)

Should be...

dateadd(year,1,@hiredate)

Other than that, I don't really see anything wrong. Is there more to this query that you are not showing?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom