Select records based on last date entered?

Question

Select records based on last date entered?

Patrick Shaw

Valued Member

Points: 65
More actions
November 12, 2005 at 9:05 am

#62018

Can someone help please?
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.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

Your SQL attempts are very close.

As all SQL statements have "input" of a set and the "output" is always a set, this set can be used just as a table. This is the

principal of closure.

Here is a SQL statement to get the last timesheet date by person:

select Timesheets.SMID

, MAX(Timesheets.[date])

from Timesheets

group by Timesheets.SMID

Then join to the remainder of your SQL by naming the set and the columns:

select PERSONKEYIDS.FullName AS 'Sales Manager Name'

, Timesheets.SMID

, Timesheets.[date]

from AGENT_TEAM_FACT

join PERSONKEYIDS

ON AGENT_TEAM_FACT.ParentKeyID = PERSONKEYIDS.PersonKeyId

JOIN Timesheets

on PERSONKEYIDS.PersonKeyId = Timesheets.SMID

-- Last Timesheet Timesheets.[date] for each Person

join (select Timesheets.SMID

, MAX(Timesheets.[date])

from Timesheets

group by Timesheets.SMID)

as Timesheets_Latest (SMID, Latest_Date)

on Timesheets_Latest.SMID = Timesheets.SMID

and Timesheets_Latest.Latest_Date = Timesheets.[date]

Order by 'Sales Manager Name'

SQL = Scarcely Qualifies as a Language

Patrick Shaw Valued Member Points: 65 More actions · Answer 2

Thanks very much. I will not be able to test this out until tomorrow evening but I will let you know what the results are.

BTW. In case you were wondering, the convert function on the Date field in my query is because the datatype is set to varchar. It's a long story as to why and I didn't design the Database!!