SQL help with join and date - SQL Server Forums

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)

SQL help with join and date

Author

Topic

jrockfl
Posting Yak Master

223 Posts

Posted - 2006-06-15 : 17:12:17

I have an application that needs the get the initials and email that the request was last routed to.
The header table stores all the requests, the routing table shows who that request was routed to and EmailAddressesses contains all the employees.

I want to be able to return the last person the requests where routed to send them an email.
Right now it's returning all the routings for each request.

This is what I have so far...
SELECT Max(Routing.RoutedTo) AS RoutedTo, Header.ID, Max(Routing.Date) AS MaxOfDate, EmailAddresses.Email
FROM (Header INNER JOIN Routing ON Header.ID = Routing.ID) INNER JOIN EmailAddresses ON Routing.RoutedTo = EmailAddresses.Initials
WHERE (((Header.Completed)=0) AND ((Routing.Date)<DateAdd('d',-1,Now())))
GROUP BY Header.ID, EmailAddresses.Email;

HeaderID
1

ID RoutedTo
1 BBB 06/12/2006
1 JAC 06/13/2006

In this example, it would just return the last row, not both

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-15 : 18:24:14

How would you know what the last one was?

What is the criteria for "last"?

CODO ERGO SUM

jrockfl
Posting Yak Master

223 Posts

Posted - 2006-06-15 : 18:32:29

The most recent date. The one from 06/13, it has been routed to this person last.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-15 : 18:40:40

Is this a Microsoft SQL Server database?

I noticed you are using a function called NOW(), and that is not a SQL Server function.

CODO ERGO SUM

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-16 : 02:27:49

If you use MS Access then post your question at Access Forum
Also try this


Select top 1 * from
(
      yourQuery
) T order by [datecol] DESC

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources