SQLTeam.com | Weblogs | Forums

Find the closest date with associated column

Hi,
I'm looking to find the closest dt from trdt and get the associated tnumber and the origidno. my query retrieve incorrect information. Thank you in advance.

--sample data:
declare @Transcreate table
(
idno varchar(50), origidno varchar(50), recoveridno  varchar(50), trdt datetime, ctype varchar(10), op_desc varchar(20))
insert @Transcreate values ('ABSBC9240887','ABSBC9240887','NULL','2018-09-05 20:55:44.000','HK','Create')
insert @Transcreate values ('CCCC69243304','CCCC69243304','NULL','2018-09-04 21:53:56.000','HK','Create')	
insert @Transcreate values ('DDDD83949942','DDDD83949942','NULL','2018-08-31 11:02:55.000','HK','Create')	
insert @Transcreate values ('RRRR63070725','RRRR63070725','NULL','2018-09-05 16:56:25.000','HK','Create')	


declare @Transdetails table
(
idno varchar(50), origidno varchar(50), recoveridno  varchar(50), dt datetime,  op_desc varchar(20), tnumber varchar(50))
insert @Transdetails values ('ABSBC9240887','ABCDE5B12AG12338890','ABSBC9240887','2018-04-12 03:32:42.000','Create','ABCDE5B12AG123388903198.8073')
insert @Transdetails values ('ABSBC9240887','TABCDE5B12AG12338890','ABSBC9240887','2018-09-03 14:54:42.000','Create','TABCDE5B12AG12338890')

insert @Transdetails values ('CCCC69243304','ABCDE5B12AG12333304','CCCC69243304','2018-08-24 06:38:31.000','Create','ABCDE5B12AG123333043331.9464')
insert @Transdetails values ('CCCC69243304','TABCDE5B12AG12333304','CCCC69243304','2018-02-16 19:48:04.000','Create','TABCDE5B12AG12333304')

insert @Transdetails values ('DDDD83949942','EFGHE5B12AG12339942','DDDD83949942','2018-08-24 07:56:40.000','Create','EFGHE5B12AG123399423331.5089')
insert @Transdetails values ('DDDD83949942','TEFGHE5B12AG12339942','DDDD83949942','2018-04-14 07:10:05.000','Create','TEFGHE5B12AG12339942')

insert @Transdetails values ('RRRR63070725','DCFDE5B12AG12335166','RRRR63070725','2018-08-29 00:01:00.000','Create','DCFDE5B12AG1233516663334.2273')
insert @Transdetails values ('RRRR63070725','TDCFDE5B12AG12335166','RRRR63070725','2018-03-06 16:47:06.000','Create','TDCFDE5B12AG12335166')


--Query
select  t1.idno, t1.origidno, t1.recoveridno, t1.dt, t1.ctype, t1.op_desc, t2.origidno as new_orig_idno, t2.tnumber
from @Transcreate t1
		left join  @Transdetails t2
		on t2.idno = t1.origidno
		and t1.dt>= t2.dt




--Desired Result
idno-------------origidno---------------recoveridno--trdt---------------------ctype-----op_desc---new_orig_idno------------tnumber
------------------------------------------------------------------------------------------------
ABSBC9240887-----ABCDE5B12AG12338890-----NULL-----2018-09-05 20:55:44.000---HK-----Create----TABCDE5B12AG12338890--TABCDE5B12AG12338890
CCCC69243304-----ABCDE5B12AG12333304-----NULL-----2018-09-04 21:53:56.000---HK-----Create----ABCDE5B12AG12333304---ABCDE5B12AG12333304
DDDD83949942-----EFGHE5B12AG12339942-----NULL-----2018-08-31 11:02:55.000---HK-----Create----EFGHE5B12AG12339942--EFGHE5B12AG123399423331.5089
RRRR63070725-----DCFDE5B12AG12335166-----NULL-----2018-09-05 16:56:25.000---HK-----Create----DCFDE5B12AG12335166--DCFDE5B12AG1233516663334.2273

Hi

Please find my solution

Create Data Script
drop table #Transcreate 
go
create table  #Transcreate
(
idno varchar(50), origidno varchar(50), recoveridno  varchar(50), trdt datetime, ctype varchar(10), op_desc varchar(20))
go 

insert #Transcreate values ('ABSBC9240887','ABSBC9240887','NULL','2018-09-05 20:55:44.000','HK','Create')
insert #Transcreate values ('CCCC69243304','CCCC69243304','NULL','2018-09-04 21:53:56.000','HK','Create')	
insert #Transcreate values ('DDDD83949942','DDDD83949942','NULL','2018-08-31 11:02:55.000','HK','Create')	
insert #Transcreate values ('RRRR63070725','RRRR63070725','NULL','2018-09-05 16:56:25.000','HK','Create')
go	

drop table #Transdetails 
go 
create table #Transdetails
(
idno varchar(50), origidno varchar(50), recoveridno  varchar(50), dt datetime,  op_desc varchar(20), tnumber varchar(50))
go

insert #Transdetails values ('ABSBC9240887','ABCDE5B12AG12338890','ABSBC9240887','2018-04-12 03:32:42.000','Create','ABCDE5B12AG123388903198.8073')
insert #Transdetails values ('ABSBC9240887','TABCDE5B12AG12338890','ABSBC9240887','2018-09-03 14:54:42.000','Create','TABCDE5B12AG12338890')
insert #Transdetails values ('CCCC69243304','ABCDE5B12AG12333304','CCCC69243304','2018-08-24 06:38:31.000','Create','ABCDE5B12AG123333043331.9464')
insert #Transdetails values ('CCCC69243304','TABCDE5B12AG12333304','CCCC69243304','2018-02-16 19:48:04.000','Create','TABCDE5B12AG12333304')
insert #Transdetails values ('DDDD83949942','EFGHE5B12AG12339942','DDDD83949942','2018-08-24 07:56:40.000','Create','EFGHE5B12AG123399423331.5089')
insert #Transdetails values ('DDDD83949942','TEFGHE5B12AG12339942','DDDD83949942','2018-04-14 07:10:05.000','Create','TEFGHE5B12AG12339942')
insert #Transdetails values ('RRRR63070725','DCFDE5B12AG12335166','RRRR63070725','2018-08-29 00:01:00.000','Create','DCFDE5B12AG1233516663334.2273')
insert #Transdetails values ('RRRR63070725','TDCFDE5B12AG12335166','RRRR63070725','2018-03-06 16:47:06.000','Create','TDCFDE5B12AG12335166')
go
SQL
SELECT a.idno, 
       a.origidno, 
       a.recoveridno, 
       a.trdt, 
       a.dt, 
       a.ctype, 
       a.op_desc, 
       a.origidno AS new_orig_idno, 
       a.tnumber 
FROM   (SELECT Row_number() 
                 OVER( 
                   partition BY t1.origidno 
                   ORDER BY Datediff(ss, t2.dt, t1.trdt)) AS rn, 
               t1.idno, 
               t1.origidno, 
               t1.recoveridno, 
               t1.trdt, 
               t2.dt, 
               t1.ctype, 
               t1.op_desc, 
               t2.origidno                                AS new_orig_idno, 
               t2.tnumber, 
               Datediff(ss, t2.dt, t1.trdt)               AS minseconds 
        FROM   #transcreate t1 
               LEFT JOIN #transdetails t2 
                      ON t2.idno = t1.origidno 
                         AND t1.trdt > t2.dt) a 
WHERE  rn = 1
Result

Thank you very much.