Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1080
  • Last Modified:

T-SQL Returning nearest date

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
helpdeskis
Asked:
helpdeskis
  • 3
  • 3
  • 2
  • +2
1 Solution
 
mrjoltcolaCommented:
Assuming all dates are in the past, this should work.

select id, max(myDate) from tbl
group by id;
0
 
Kevin CrossChief Technology OfficerCommented:
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
 
mrjoltcolaCommented:
If not, just add a where clause:

select id, max(myDate) from tbl
where myDate < getdate()
group by id;
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Kevin CrossChief Technology OfficerCommented:
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
 
chapmandewCommented:
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
 
imitchieCommented:
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
 
imitchieCommented:
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
 
helpdeskisAuthor Commented:
Thanks,that gets the job done.
0
 
mrjoltcolaCommented:
Hi, I am not objecting, but I am curious why this solution was chosen over the other valid ones?

0
 
Kevin CrossChief Technology OfficerCommented:
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
 
helpdeskisAuthor Commented:
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

  • 3
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now