Find the closest date with associated column

Transact-SQL
, ,
1

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
2

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

image
image.png2874×325 15.1 KB

3

Thank you very much.