Find last entered records for each person.

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

SQL Server Development (2000)

Find last entered records for each person.

Author

Topic

p.shaw3@ukonline.co.uk
Posting Yak Master

103 Posts

Posted - 2005-11-13 : 12:45:04

If I use this query:
select distinct PERSONKEYIDS.FullName AS 'Sales Manager Name',
Timesheets.SMID, Timesheets.[date]
from AGENT_TEAM_FACT left join PERSONKEYIDS
ON AGENT_TEAM_FACT.ParentKeyID = PERSONKEYIDS.PersonKeyId
INNER JOIN Timesheets on PERSONKEYIDS.PersonKeyId = Timesheets.SMID
Order by 'Sales Manager Name'

I get these results

Sales Manager Name SMID Date
david hall 10334 11/11/2005
david hall 10334 08/11/2005
ian lowe 12446 11/11/2005
debbi tomms 12211 10/11/2005
debbi tomms 12211 09/11/2005
debbi tomms 12211 06/11/2005
steve vine 13429 07/11/2005

Whereas if I add the following line to the query:

select distinct PERSONKEYIDS.FullName AS 'Sales Manager Name',
Timesheets.SMID, Timesheets.[date]
from AGENT_TEAM_FACT left join PERSONKEYIDS
ON AGENT_TEAM_FACT.ParentKeyID = PERSONKEYIDS.PersonKeyId
INNER JOIN Timesheets on PERSONKEYIDS.PersonKeyId = Timesheets.SMID
where convert(DateTime,[date],103) = (Select max(convert(DateTime,[Date],103 )) from Timesheets)
Order by 'Sales Manager Name'

I of course get this:

Sales Manager Name SMID Date
david hall 10334 11/11/2005
ian lowe 12446 11/11/2005

Although I can see that both these result sets are correct in relation to the query, These are not what I want. I need a result set that returns the details of the Sales Managers for the most recent date they submitted a Timesheet only. So, for example the result set should look like this:

Sales Manager Name SMID Date
david hall 10334 11/11/2005
ian lowe 12446 11/11/2005
debbi tomms 12211 10/11/2005
steve vine 13429 07/11/2005

returning the details for the Sales Managers for the last date on which they submitted a Timesheet ONLY. I hope this makes sense.

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2005-11-13 : 13:30:22

ah, one of the classics. do you have any idea how many times people have asked this question?
you wanna do a join on a virtual table that has max(Timesheets.[date]) as one of it's columns

here's just one example:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=36643

type this into google for more: max date recent site:www.sqlteam.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources