Record for Max(DateField) - 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 2008 Forums

Transact-SQL (2008)

Record for Max(DateField)

Author

Topic

wldodds
Starting Member

20 Posts

Posted - 2012-09-09 : 10:34:48

I have a table which has multiple columns within it. Every time an event occurs for an employee a record is inserted into the table with an effective date.

I'm trying to retrieve the record for employee's where I specify a date. For example, the table is called HR2EMP02 and it has columns employeeid and effectivedate.

When the employee is created an initial record is created and then if a change is made to the employee a record is then inserted with an effectivedate.

sample data:

EmployeeId, EffectiveDate
1234, 6/1/2012
1234, 7/28/2012
1234, 8/19/2012
4567, 7/3/2012
4567, 8/11/2012
4567, 9/8/2012

When I run the query for as of 7/5/2012 I would like to return the following records:
1234, 6/1/2012
4567, 7/3/2012

I tried this but obviously only the top portion of this query works the exists part doesn't and I'm stumped:

select *
from HR2EMP02 T1
where T1.EFFECTIVEDATE_I <= '6/20/2012'
and T1.EFFECTIVEDATE_I exists (select * from HR2EMP02 T2 where T2.EFFECTIVEDATE_I <= '6/20/2012')

Any help is greatly appreciated!

Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-09 : 12:00:42

[code]
DECLARE @Date datetime
SET @Date = '2012-06-20'

SELECT t.*
FROM table t
INNER JOIN (SELECT EmployeeId,MAX(EffectiveDate) AS MaxDate
FROM table
WHERE EffectiveDate < =@Date
GROUP BY EmployeeId)t1
ON t1.EmployeeId = t.EmployeeId
AND t1.MaxDate = t.EffectiveDate
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

wldodds
Starting Member

20 Posts

Posted - 2012-09-09 : 20:17:16

Thank you that worked perfectly. I didn't think of an Inner Join I'm so used to always going with a left outer to pull all records. Greatly appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-09 : 22:43:07

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources