November 12, 2005 at 3:34 pm
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.
November 12, 2005 at 4:25 pm
You're most likely looking for a group by statement along the lines of:
select
PERSONKEYIDS.FullName AS 'Sales Manager Name',
Timesheets.SMID,
LastDate = max(Timesheets.[date])
from
AGENT_TEAM_FACT
left join PERSONKEYIDS ON AGENT_TEAM_FACT.ParentKeyID = PERSONKEYIDS.PersonKeyId
INNER JOIN Timesheets on PERSONKEYIDS.PersonKeyId = Timesheets.SMID
group by
PERSONKEYIDS.FullName AS 'Sales Manager Name',
Timesheets.SMID
Order by
PERSONKEYIDS.FullName
Joe
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply