match near date based on another date

• mxy

  SSCarpal Tunnel

  Points: 4036

  More actions

  January 28, 2022 at 4:25 am

  Go to Answer

  #3978151

  hello,  i am stuck and need some help to generate output based on the below scenario

  if object_id(N'tempdb..#t1') is not null
  
  DROP TABLE #t1
  
  if object_id(N'tempdb..#t2') is not null
  
  DROP TABLE #t2
  
  create table #t1
  
  (
  
  userid int,
  
  maildate date
  
   
  
  )
  
   
  
  insert into #t1
  
  select
  
  1, '2021-05-04'
  
  union select 1, '2021-04-13'
  
  union select 1, '2020-11-03'
  
  union select 1, '2020-10-30'
  
   
  
  select * from #t1
  
   
  
   
  
  create table #t2
  
  (
  
  userid int,
  
  addr varchar(50),
  
  addr_update_dt date
  
   
  
  )
  
   
  
  insert into #t2
  
  select 1,'teaneck ,NJ' , '2021-08-27'
  
  union select 1,'teaneck ,NJ' , '2021-06-12'
  
  union select 1,'teaneck ,NJ' , '2021-04-13'
  
  union select 1,'teaneck ,NJ' , '2021-02-13'
  
  union select 1,'teaneck ,NJ' , '2020-10-06'
  
  union select 1,'teaneck ,NJ' , '2020-09-26'
  
  union select 1,'teaneck ,NJ' , '2020-09-26'
  
  UNION SELECT 1, 'CARY,NC', '2015-06-23'
  
   
  
  --expected output for mail date provide the nearst match date
  
  --userid , mail_date, adr_update_dt
  
  select 1 as useid, '2021-05-04' as mail_date,'2021-04-13' as adr_update_dt
  
  union select 1, '2021-04-13','2021-02-13'
  
  union select 1, '2020-11-03','2020-10-06'
  
  union select 1, '2020-10-30','2020-09-26'
  
   
  
  

   

  • This topic was modified 2 years, 10 months ago by  mxy.

• Ken McKelvey

  SSCoach

  Points: 18842

  More actions

  January 28, 2022 at 10:45 am

  Answer

  #3978261

  SELECT T1.userid, T1.maildate, X.addr_update_dt
  FROM #t1 T1
     CROSS APPLY
     (
        SELECT TOP (1) T2.addr_update_dt
        FROM #t2 T2
        WHERE T2.userid = T1.userid
           AND T2.addr_update_dt < T1.maildate
        ORDER BY T2.addr_update_dt DESC
     ) X
  ORDER BY maildate DESC;

  • This reply was modified 2 years, 10 months ago by  Ken McKelvey.