SQL Server order by closest value to zero

Question

I have some duplicate values in a table and I'm trying to use Row_Number to filter them out. I want to order the rows using datediff and order the results based on the closest value to zero but I'm struggling to account for negative values.

Below is a sample of the data and my current Row_Number field (rn) column:

PersonID    SurveyDate  DischargeDate   DaysToSurvey    rn
93638       10/02/2015  30/03/2015      -48             1
93638       27/03/2015  30/03/2015      -3              2
250575      23/10/2014  29/10/2014      -6              1
250575      19/11/2014  24/11/2014      -5              2
203312      23/01/2015  26/01/2015      -3              1
203312      26/01/2015  26/01/2015      0               2
387737      19/02/2015  26/02/2015      -7              1
387737      26/02/2015  26/02/2015      0               2
751915      02/04/2015  04/04/2015      -2              1
751915      10/04/2015  25/03/2015      16              2
712364      24/01/2015  30/01/2015      -6              1
712364      26/01/2015  30/01/2015      -4              2

My select statement for the above is:

select 
    PersonID, SurveyDate, DischargeDate, 
    datediff(dd,dischargedate,surveydate) days, 
    ROW_NUMBER () over (partition by PersonID 
                        order by datediff(dd, dischargedate, surveydate) asc) as rn
from 
    Table 1
order by 
    PersonID, rn

What I want to do is change the sort order so it displays like this:

PersonID    SurveyDate  DischargeDate   DaysToSurvey    rn
93638       27/03/2015  30/03/2015      -3              1
93638       10/02/2015  30/03/2015      -48             2
250575      19/11/2014  24/11/2014      -5              1
250575      23/10/2014  29/10/2014      -6              2

So the DaysToSurvey value that is closest to the DischargeDate is ranked as rn 1.

Is this possible?

Answer 1

You're close. Just add ABS() to calculate absolute values of the differences:

ROW_NUMBER () OVER (
  PARTITION BY PersonID 
  ORDER BY abs(datediff(dd, dischargedate, surveydate)) asc
) AS rn