Most recent date 1

[Arob]

I have a table which contains records of customers. Customers can and will have more than one record in the table. Each record also contains a short date mm/dd/yy of when services are conducted for this customer. I am trying to write a query which would only give me the record for the most recent date of service for a customer and I am having no luck. Can anyone help with the criteria needed to get the desired results

Thanks

 

[AlexCuse]

What you want to do is join to a subquery that only contains the customer identifier and the max of serviceDate.

This assumes each customer has a unique customer ID (CID)

select a.CID, a.OtherCol, a.AnOtherCol, a.ServiceDate
from SomeTable a
inner join
(
select CID, max(ServiceDate) as ServiceDate
from SomeTable
group by CID
) b
on a.CID = b.CID
and a.ServiceDate = b.ServiceDate

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[Bandenjamin]

I'm having a similar issue as this one but I can't seem to figure out what I'm doing wrong. Here is the code of the initial query. I want to get the same results as Arob above. Any help is appreciated. Here's the SQL

SELECT [Call Review Table].Analyst, [Call Review Table].ReviewDate, [Call Review Table].Reviewer, Analysts.Terminated
FROM Analysts INNER JOIN [Call Review Table] ON Analysts.AnalystName = [Call Review Table].Analyst
GROUP BY [Call Review Table].Analyst, [Call Review Table].ReviewDate, [Call Review Table].Reviewer, Analysts.Terminated
HAVING (((Analysts.Terminated)=False));

The "service date" I am using for this would be the "ReviewDate" field.

Thanks

Dan

 

[lespaul]

maybe:

SELECT [Call Review Table].Analyst, B.ReviewDate, [Call Review Table].Reviewer, Analysts.Terminated
FROM [Call Review Table] 
INNER JOIN Analysts ON Analysts.AnalystName = [Call Review Table].Analyst
INNER JOIN (SELECT Analyst, Max(ReviewDate) FROM [Call Review Table] GROUP BY Analyst) As B ON [Call Review Table].Analyst = B.Analyst AND [Call Review Table].ReviewDate = B.ReviewDate
WHERE Analysts.Terminated=False
GROUP BY [Call Review Table].Analyst, B.ReviewDate, [Call Review Table].Reviewer, Analysts.Terminated;

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[PHV]

I'd try this:
SELECT C.Analyst, C.ReviewDate, C.Reviewer, A.Terminated
FROM (Analysts AS A
INNER JOIN [Call Review Table] AS C ON A.AnalystName = C.Analyst)
INNER JOIN (
SELECT R.Analyst, Max(R.ReviewDate) AS LastReview FROM Analysts AS B INNER JOIN [Call Review Table] AS R
ON B.AnalystName = R.Analyst WHERE B.Terminated = False GROUP BY R.Analyst
) AS L ON C.Analyst = L.Analyst AND C.ReviewDate = L.LastReview

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Bandenjamin]

A million thanks PHV that worked perfect. Thanks for your help to leslie. Not sure what was wrong but was getting a missing operator error on that one.