Geek asks and answers

T-SQL - getting the latest date and last future date

Assume the entry table 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 get the latest date of the meeting by man. So I need a query that will give the following set of results.

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)

Thank!

+3
source share
2 answers

Assuming that when you say “most recent,” you mean “closest,” since “the stored date is the least number of days from the current date, and we don’t care whether it is before or after the current date,” then should do this (trivial debugging may 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

row_number() SQL 2005 . "" , .

, :

  • , (, ..) .
  • (, 2 2 ),

.

+9

( , - . :)

, , , :

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
+2

More articles:


All Articles