Solved

T-SQL Returning nearest date

Posted on 2009-07-07
11
1,075 Views
Last Modified: 2012-06-27
Hi. I'm trying to retrieve the unique ID's closest to today's date. So only ID's 1,2,3 that have the newest dates. See table below. Thanks.
----------------------------------------------
myDate                               ID
----------------------------------------------
4/02/2009 12:00:00 AM      1
1/05/2009 12:00:00 AM      1
3/07/2009 12:00:00 AM      3
6/07/2009 12:00:00 AM      3
8/07/2009 12:00:00 AM      1
8/07/2009 12:00:00 AM      2
0
Comment
Question by:helpdeskis
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +2
11 Comments
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24799682
Assuming all dates are in the past, this should work.

select id, max(myDate) from tbl
group by id;
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24799686
How are you defining nearest?  Within what interval?  In other words, has to be within a month ago or in the future or within a month either way or etc...
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24799688
If not, just add a where clause:

select id, max(myDate) from tbl
where myDate < getdate()
group by id;
0
Certified OpenStack Administrator Course

We just refreshed our COA course based on the Newton exam.  With 14 labs, this course goes over the different OpenStack services that are part of the certification: Dashboard, Identity Service, Image Service, Networking, Compute, Object Storage, Block Storage, and Orchestration.

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24799689
Or possibly you meant max (newest) date for each ID like MrJoltCola showed.  That would make sense, especially if your date format above is dd/MM/yyyy versus MM/dd/yyyy as I thought it was.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 24799716
here are how you can find the dates closest to the current date...


select case when datediff(d, mindate, convert(varchar(10), getdate(), 101)) > datediff(d, maxdate, convert(varchar(10), getdate(), 101)) then maxdate else mindate end
from (
select as mindate = max(mydate) from tablename
where mydate < convert(varchar(10), getdate(), 101),
select as maxdate= min(mydate) from tablename
where mydate > convert(varchar(10), getdate(), 101)
) a
0
 
LVL 25

Expert Comment

by:imitchie
ID: 24800175
Since you have posted in sql server 2005, here you go.
As you can see, I have put some comments in case you need to vary it slightly to suit your needs.
select ID, myDate
from (
	select rn=ROW_NUMBER() over (order by myDate desc)
	where myDate <= GETDATE() -- closest (on or before) current date(time)
) X
where rn = 1 -- only one record for each unique ID

Open in new window

0
 
LVL 25

Accepted Solution

by:
imitchie earned 500 total points
ID: 24800185
Apologies, I forgot to add the columns into the inner select.  You can add other columns just as easily.
select ID, myDate
from (
        select rn=ROW_NUMBER(), ID, myDate over (order by myDate desc)
        where myDate <= GETDATE() -- closest (on or before) current date(time)
) X
where rn = 1 -- only one record for each unique ID

Open in new window

0
 

Author Closing Comment

by:helpdeskis
ID: 31600951
Thanks,that gets the job done.
0
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24800643
Hi, I am not objecting, but I am curious why this solution was chosen over the other valid ones?

0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 24803251
I would agree.  That is a whole lot of code to just get ID and max date per ID which seems like what you wanted.  You would use the accepted approach if you had additional columns of data necessary that could not be aggregated.  If all you have is the column or columns that you are grouping by and columns that need aggregation like max(date) then http:#24799688 is much simpler (KISS principle).
0
 

Author Comment

by:helpdeskis
ID: 24809704
I didn't want max date per ID, I wanted max date of only ID's 1,2,3. So if I added additional ID's like 4 & 5, then return only ID's 1,2,3,4,5. As you can see, there are mutliple ID's with different dates.
0

Featured Post

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question