Help filtering for most recent record in a join

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)

Help filtering for most recent record in a join

Author

Topic

sbt1
Yak Posting Veteran

89 Posts

Posted - 2005-04-22 : 08:43:39

I'm using this query:

SELECT Tasks.TaskID,Tasks.Customer,Tasks.SystemType,TaskDates.Status,Tasks.TaskDescription,Tasks.CallCloseComments, DATEDIFF(d, TaskDates.DateValue,Tasks.DateEntered)
FROM Tasks
INNER JOIN TaskDates
ON Tasks.TaskID=TaskDates.TaskID

To compute a date difference.

Works fine, however since there may be multiple TaskDates records linked to a single Tasks record (the TaskID fields match), I would like to filter and only return results for the TaskDates record with the latest date.

That way, I'm getting the time span between when the call was entered (Tasks) and the call was completed (TaskDates with the latest date).

Any ideas?

I tried using a SELECT TOP 1 in the join, but that didn't work.

sbt1
Yak Posting Veteran

89 Posts

Posted - 2005-04-22 : 09:01:40

Thanks Jim, however that doesn't solve the problem - I'm still getting all TaskDates records returned that match a given Task record.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-04-22 : 09:09:42

quote:
Thanks Jim, however that doesn't solve the problem - I'm still getting all TaskDates records returned that match a given Task record.


SELECT Tasks.TaskID,Tasks.Customer,Tasks.SystemType,TaskDates.Status,Tasks.TaskDescription,Tasks.CallCloseComments, DATEDIFF(d, TaskDates.DateValue,Tasks.DateEntered)
FROM Tasks 
INNER JOIN (SELECT TaskID, MAX(DateValue) AS DateValue FROM TaskDates GROUP BY TaskID) TaskDates 
ON Tasks.TaskID=TaskDates.TaskID

Andy

Beauty is in the eyes of the beerholder

sbt1
Yak Posting Veteran

89 Posts

Posted - 2005-04-22 : 09:31:52

Hey, that works! Thanks... only one small problem, I keep getting an error that "Status" is a bad field name... though TaskDates.Status is a real field. Is it because it's references in the SELECT prior to the join with TaskDates?

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-04-22 : 09:39:43

Not quite
The select statement in the join will only have the 2 fields available to the "main" select ie TaskID and DateValue.

If you need status from the TaskDates table add it to the select statement in the join
This may lead to duplicates though if it is not aggregated or filtered (eg WHERE Status = 'X')

Andy

Beauty is in the eyes of the beerholder

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources