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?
 
PortletPaulfreelancerCommented:
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
 
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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Lokesh B RDeveloperCommented:
Select TOP 1 Min(serviceDate) AS ServiceDate,
clientID FROM Table Name
Group By clientID
0
 
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
 
PortletPaulfreelancerCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.