query to return records from last date only

[timroop]

I have a view returning a list of checks and related employee information. The important fields here are CheckDate, CheckAmt, EmployeeID, and EmployeeTermDate. I need to return the last check for employees that have been terminated (EmployeeTermDate not NULL). However the view returns all checks for terminated employees. I only need the last check processed (max(CheckDate)).

The where clause is something like:

where EmployeeTermDate is not NULL and CheckDate = "max checkdate for a given employee"

I'm not certain how to get the last check date for each individual employee.

Thank you.

Tim


Tim Roop
"If you can do something about it, why waste time getting upset? If you can't do anything about it, why bother getting upset on top of it? -Shantideva

 

[markros]

This is a very common problem. I can write several different solutions for you, but it may be beneficial if you can learn various techniques of solving this problem from these blogs:
Including an Aggregated Column's Related Values
Including an Aggregated Column's Related Values - Part 2


PluralSight Learning Library

 

[bborissov]

SELECT YourTable.*
FROM YourTable
INNER JOIN (SELECT EmplId, MAX(Date) AS Date
                   FROM YourTable
            GROUP BY EmplId) Tbl1
     ON YourTbale.EmplId = Tbl1.EmplId AND
        YourTbale.Date   = Tbl1.Date

Keep in mind that this code use datetime fields as is, and the TIME portion is involved in the MAX() function.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Rhys666]

Or you could use the RANK() function depending on your version of Sql Server...?

SELECT
	tmp.EmpID
	,tmp.FIELD_NAME_2
	,tmp.FIELD_NAME_3	/* etc. etc. */
FROM
(
	SELECT
		x.EmpID
		,tmp.FIELD_NAME_2
		,tmp.FIELD_NAME_3	/* etc. etc. */
		,RANK () OVER
		(
			PARTITION BY
				x.EmpID
			ORDER BY
				x.Date	DESC
		) AS 'Ranking'
	FROM
		SCHEMA.TABLE_NAME x 
)tmp
WHERE
	tmp.Ranking = 1

Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"
Terry Pratchett