Return only the row where the date in one column is closest to the date in another column?

user_16131288 0
Viewed 97

I'm working on a query but it's returning some duplicate values and I need to return only the row where the date in one column is closest to the date in another column.

My query looks something like this:

SELECT p.Id, r.ReferralDate, s.SupervisionDate
FROM person p
INNER JOIN referral r on r.PersonId = p.Id 
INNER JOIN supervision s on s.PersonId = p.Id

Which returns something like this:

Id Supervision Date Referral Date
123 2015-09-30 2015-08-30
123 2020-02-30 2015-08-30
123 2020-06-30 2015-08-30
456 2010-06-30 2010-07-30
456 2005-06-30 2010-07-30

How can I write a query that returns the Supervision Date that is closest to the Referral Date? So that the final output looks like this:

Id Supervision Date Referral Date
123 2015-09-30 2015-08-30
456 2010-06-30 2010-07-30
tsql sql-server sql
3 Answers

here is one way:

select p.Id, r.ReferralDate, s.SupervisionDate from (
 select p.Id, r.ReferralDate, s.SupervisionDate , row_number() over (partition by id order by abs(datediff(day , ReferralDate,SupervisionDate))) rn
 from person p
 join referral r on r.PersonId = p.Id 
 join supervision s on s.PersonId = p.Id
) t
where rn = 1
user_13674540

another way:

select ID, SUP from 
( SELECT 
   p.Id 'ID', 
   r.ReferralDate 'REF', 
   s.SupervisionDate 'SUP',
   min(julianday(s.SupervisionDate) - julianday(r.ReferralDate) ) 'diff'

FROM person p
INNER JOIN referral r on r.PersonId = p.Id 
INNER JOIN supervision s on s.PersonId = p.Id

GROUP BY p.ID
)

This will return ID and SupervisionDate as reqested.

user_18180590

You can use two ways in this scenario to select the record that you need.

  1. I prefer this way as it is very effective solution. We get the row order by date difference and select the mini date difference. first way you can select what columns to show. This is also more generic solution.

    SELECT  A.Id
 , A.ReferalDate
 , A.Supervision 

FROM 
(

 SELECT 
   p.Id
   , r.ReferalDate
   , s.Supervision   
   ,ROW_NUMBER() OVER (PARTITION BY p.Id ORDER BY  DATEDIFF(DD,r.ReferalDate,s.Supervision) ASC) as [row_num]
  FROM person p
     INNER JOIN referral r 
        on r.pid = p.Id 
     INNER JOIN supervision s 
       on s.pid = p.Id
 ) AS A 
WHERE A.row_num = 1

Result

  1. this way we order by date difference and get the top 1 record. If you add this in to CTE or Derived table, you can still return only the columns you need. This query is scenario specific as we do not consider partitions.

    SELECT top 1
    p.Id
   , r.ReferalDate
   , s.Supervision   
   ,DATEDIFF(DD,r.ReferalDate,s.Supervision) as [date dif]
FROM person p
INNER JOIN referral r 
    on r.pid = p.Id 
INNER JOIN supervision s 
    on s.pid = p.Id
ORDER BY [date dif] ASC

Both ways return the same result as our order by is placed on date difference.

Result

user_55675090
Related