Simple SQL Query

I need some help with a very simple SQL query. I've not written any queries in a very long time and need to knock off some rust.

I have a table with a client id field and a servicedate field. Often times there can be multiple servicedates for a specific client. I simply want to return the first (oldest) servicedate for the unique client. Please provide quick snipet.

Any help is appreciated.
kbiosAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark ElySenior Coldfusion DeveloperCommented:
Select Top 1 Max(serviceDate) AS ServiceDate, clientID
FROM NameOfTable
Group By clientID
0
chaauCommented:
It will be MIN(), not MAX() in Mark's answer.
Alternatively you can use this:
SELECT TOP 1 serviceDate, clientid 
from table1
order by serviceDate

Open in new window

0
Lokesh B RDeveloperCommented:
Select TOP 1 Min(serviceDate) AS ServiceDate,
clientID FROM Table Name
Group By clientID
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

PortletPaulEE Topic AdvisorCommented:
I don't know why TOP 1 is being suggested.

If you want a list of the oldest service date for EACH client, use a GROUP BY clause with the already recommended MIN() function, but omit the TOP 1.


SELECT  clientid , MIN(serviceDate) as Oldest_servicedate
from table1
order by clientid

IF you do only want just a singe value for a single client then:

select  MIN(serviceDate) as Oldest_servicedate
where clientid = 1
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lokesh B RDeveloperCommented:
Ahhh You are right Paul.

This should work for all Clients

Select Min(serviceDate) AS ServiceDate, clientID FROM Table Name Group By clientID

Open in new window


For Perticular CLient.

Select Min(serviceDate) AS ServiceDate, clientID FROM Table Name WHERE clientID=100

Open in new window

0
kbiosAuthor Commented:
Thanks for the help. I'm tracking with the MIN() logic. However, I'm getting mixed results. While testing I noticed that sometimes the most recent date was returned.

client 1: 10/15/01 7/11/03 2/27/04               program returns 2/27/04 ** incorrect **
client 2: 2/17/06 9/27/06 10/30/14               program returns 2/17/06 (correct)

Please take a look at the code snipet. Do you see anything that may explain the inconsistency?


Select Min(CONVERT(VARCHAR, AdmissionDate, 101)) AS AdminDate, ClientNo, Name
FROM Episodes
INNER JOIN People ON People.PersonNo = Episodes.clientno

Group By Name, ClientNo
ORDER BY Name
0
kbiosAuthor Commented:
I removed the CONVERT logic in the date and the MIN() logic works fine now. Before I was getting the newest date and sometimes a date from within the range.

The date format in the table is yyyy-mm-dd hh:mm:ss.000 (all of the times are set to 0). I was using the CONVERT to cleanup the records that I'm dumping. Is there an easy way to use the CONVERT logic AFTER the MIN() logic?
0
Mark ElySenior Coldfusion DeveloperCommented:
KBios if you convert it will have to be within the MIN().  However you are using the wrong standard number.  You should use 112 instead of 101.  112 = yyyymmdd ; whereas 101 = mm/dd/yyyy.

Therefore  your final code should look like this:
Select Min(CONVERT(VARCHAR, AdmissionDate, 112)) AS AdminDate, ClientNo, Name
FROM Episodes
INNER JOIN People ON People.PersonNo = Episodes.clientno

Group By Name, ClientNo
ORDER BY Name


Because you are converting to VARCHAR this number will be a character min instead of a number min which will get you the date's formated the way you want.
0
kbiosAuthor Commented:
Thank you ALL for your help. I apologize for splitting the question into 2 parts. From now on I will create a separate question.
0
PortletPaulEE Topic AdvisorCommented:
you should not use the convert function inside the MIN()

use it outside of the MIN() instead

convert (varchar (10), Min (date _ field) , 101 )

or whatever style number you prefer.


please also note that date/time information is NOT stored the way we read it. in fact date/time data is stored as sets of integers. these are automatically displayed in a default style but the display is not a reflection of how it is stored.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.