Help in SQL Query! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Help in SQL Query!

Author

Topic

padsp
Starting Member

21 Posts

Posted - 2006-12-12 : 11:51:21

Hi

I have table called EMPLOYEE has the following data:

Table: EST
empID estID
246 539
214 539
178 539
233 538
244 539

Table: EMPLOYEE
empID estID updDT
246 539 2004-04-22
214 539 2001-01-17
178 539 2001-01-30
178 539 2004-04-22
233 538 2001-01-17
244 539 2001-01-30
244 539 2004-04-30

From the above data structure based on estID(let's say 539), I need to list employees with latest updDT, like this:

Expected output:(for estID=539)
246 539 2004-04-22
214 539 2001-01-17
178 539 2004-04-22
244 539 2004-04-30

The idea is each EMP due for next update every 3 years. So I would like to list lastdate over 2yrs and less than 3yrs.

I have tried something like this:
SELECT DISTINCT b.empID,b.updDT
FROM EST a,EMPLOYEE b
WHERE a.estID=539 and a.empID=b.empID and
b.updDT < dateadd(year,-2,'12/12/2006')

But I'm still getting EMP's 178,244 repeated twice.

Please help me

Thanks
Bob

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-12-12 : 13:04:19

does this work?:


select empid, estid, max(updDT) updDT
from	employee
where	updDT > dateadd(year,-3,getdate())
group by empid, estid

Be One with the Optimizer
TG

padsp
Starting Member

21 Posts

Posted - 2006-12-12 : 15:32:52

Hi
Thank you for your reply. This helped me to proceed further.

again thnx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources