3

Assume the table of records below

ID    Name       AppointmentDate
--    --------   ---------------
1     Bob         1/1/2010
1     Bob         5/1/2010
2     Henry       5/1/2010
2     Henry       8/1/2011
3     John        8/1/2011
3     John       12/1/2011

I want to retrieve the most recent appointment date by person. So I need a query that will give the following result set.

1   Bob    5/1/2010 (5/1/2010 is most recent)
2   Henry  8/1/2011 (8/1/2011 is most recent)
3   John   8/1/2011 (has 2 future dates but 8/1/2011 is most recent)

Thanks!

biggo78
  • 43
  • 2
  • 7
  • possible duplicate of [this](http://stackoverflow.com/questions/189213/sql-selecting-rows-by-most-recent-date) – Hasan Fahim Jun 14 '11 at 16:04
  • If you post code, XML or data samples, **please** highlight those lines in the text editor and click on the "code samples" button ( `{ }` ) on the editor toolbar to nicely format and syntax highlight it! – marc_s Jun 14 '11 at 16:04
  • probably you can use max(AppointmentDate) group by Name. – Rahul Jun 14 '11 at 16:04
  • You're not making it very clear what you want .... please rephrase / extend your question! Also: "Henry" has a `8/1/2011` appointment in your table - yet in your output, you give him a `8/1/2010` (2010 instead of 2011) appointment date - just a typo?? – marc_s Jun 14 '11 at 16:06
  • @Rahul: that won't work on "John", though.... – marc_s Jun 14 '11 at 16:06
  • @marc_s ... yes max(appointdate) wil always give the latest date ... as per his question for "John" it should be "12/1/2011" but not sure why is he mentioning it as "8/1/2011". I am not clear either. – Rahul Jun 14 '11 at 16:09
  • I think he wants the most recent **PAST** date if one exists, and if all dates are in the future the one closest to the current date. – JNK Jun 14 '11 at 16:19
  • max(appointmentdate) will work for bob and henry; i'm having trouble with John who has 2 future dates. – biggo78 Jun 14 '11 at 16:26

2 Answers2

9

Assuming that where you say "most recent" you mean "closest", as in "stored date is the fewest days away from the current date and we don't care if it's before or after the current date", then this should do it (trivial debugging might be required):

SELECT ID, Name, AppointmentDate
 from (select
           ID
          ,Name
          ,AppointmentDate
          ,row_number() over (partition by ID order by abs(datediff(dd, AppointmentDate, getdate()))) Ranking
         from MyTable) xx
 where Ranking = 1

This usese the row_number() function from SQL 2005 and up. The subquery "orders" the data as per the specifications, and the main query picks the best fit.

Note also that:

  • The search is based on the current date
  • We're only calculating difference in days, time (hours, minutes, etc.) is ignored
  • If two days are equidistant (say, 2 before and 2 after), we pick one randomly

All of which could be adjusted based on your final requirements.

Philip Kelley
  • 39,426
  • 11
  • 57
  • 92
  • ah perfect! Your assumption is correct in that the search is based on current date. Thanks for your help! – biggo78 Jun 14 '11 at 17:19
2

(Phillip beat me to the punch, and windowing functions are an excellent choice. Here's an alternative approach:)

Assuming I correctly understand your requirement as getting the date closest to the present date, whether in the past or future, consider this query:

SELECT t.Name, t.AppointmentDate
FROM
(
    SELECT Name, AppointmentDate, ABS(DATEDIFF(d, GETDATE(), AppointmentDate)) AS Distance
    FROM Table
) t
JOIN
(
    SELECT Name, MIN(ABS(DATEDIFF(d, GETDATE(), AppointmentDate))) AS MinDistance
    FROM Table
    GROUP BY Name
) d ON t.Name = d.Name AND t.Distance = d.MinDistance
Dan J
  • 16,319
  • 7
  • 50
  • 82