Get Closest date from multiple id

,
1

Hi,

I 'm having problem with my query. I'm trying to match the records of @sampleTrans with dt and csno from @sampleReceipt for a particular idno. I'm giving a different result.
Please find sample data

Thank you in advance

DEclare @sampleReceipt table
(
csno varchar(50), idno varchar(50), rdt datetime, descript varchar(35))

insert @sampleReceipt values ('ABCDE5B12AG123383814.0611','ABCDE5B12AG12338','2018-04-18 07:34:34.000','receive')
insert @sampleReceipt values ('ABCDE5B12AG123383064.3556','ABCDE5B12AG12338','2018-01-02 10:59:11.000','receive')
insert @sampleReceipt values ('ABCDE5B12AG123383064.3559','ABCDE5B12AG12338','2018-08-14 10:59:11.000','receive')

insert @sampleReceipt values ('ABCDE5B12AG333352814.2771','ABCDE5B12AG33335','2018-06-02 02:56:07.000','receive')
insert @sampleReceipt values ('12345678901234612813.6785','1234567890123461','2018-05-16 18:55:11.000','receive')
insert @sampleReceipt values ('11111118901234612813.6785','1111111890123461','2018-07-16 19:55:11.000','receive')
insert @sampleReceipt values ('22222228901234612813.6785','2222222890123461','2018-07-19 06:59:10.000','receive')

Declare  @sampleTrans table
(id varchar(50), idno nvarchar (50), recover varchar(50),  dt datetime, roid varchar(35), linestatus int, lineid varchar(35), opdesc varchar(35) ,descript varchar(35))

insert @sampleTrans values ('SAL00CABCDE5B12AG12338','ABCDE5B12AG12338','58922','2018-02-15 10:48:03.000','SAL00C',1,'H_LineD','created','created')
insert @sampleTrans values ('SAL00CABCDE5B12AG12338','ABCDE5B12AG12338','58922','2018-03-20 23:20:03.000','SAL00C',1,'H_LineD','complete','good')
insert @sampleTrans values ('SAL00AABCDE5B12AG12338','ABCDE5B12AG12338',''	   ,'2018-06-15 23:48:03.000','SAL00A',1,'H_Dis','complete','good')
insert @sampleTrans values ('SAL00AABCDE5B12AG12338','ABCDE5B12AG12338',''	   ,'2018-06-13 14:59:24.000','SAL00A',1,'H_Dis','created','created')
insert @sampleTrans values ('SAL00BABCDE5B12AG12338','ABCDE5B12AG12338','58922','2018-06-30 15:02:25.000','SAL00B',1,'H_kt','created','created')
insert @sampleTrans values ('SAL00BABCDE5B12AG12338','ABCDE5B12AG12338','58922','2018-07-05 13:20:41.000','SAL00B',1,'H_kt','complete','good')
insert @sampleTrans values ('BOI002ABCDE5B12AG33335','ABCDE5B12AG33335','80539','2018-07-10 10:33:01.000','BOI002',3,'H_Dis','complete','bad')
insert @sampleTrans values ('BOI002ABCDE5B12AG33335','ABCDE5B12AG33335','80539','2018-06-26 08:42:01.000','BOI002',3,'H_Dis','created','created')
insert @sampleTrans values ('BAL0451234567890123461','1234567890123461','','2018-06-13 21:37:33.000','BAL045',1,'H_Dis','created','created')
insert @sampleTrans values ('BAL0451234567890123461','1234567890123461','','2018-06-15 23:43:01.000','BAL045',1,'H_Dis','complete','good')
insert @sampleTrans values ('BAL0561234567890123461','1234567890123461','70418','2018-07-05 12:25:31.000','BAL056',1,'H_kt','complete','good')
insert @sampleTrans values ('BAL0561234567890123461','1234567890123461','70418','2018-06-30 15:03:28.000','BAL056',1,'H_kt','created','created')
insert @sampleTrans values ('BAL0591111111890123461','1111111890123461','70419','2018-07-25 15:03:28.000','BAL059',1,'H_Line','created','created')

select t1.id, t1.idno, t1.recover, t1.dt, t1.roid, t1.opdesc, t1.lineid , t2.csno, t2.rdt
from @sampleTrans t1
left join @sampleReceipt t2
on t1.idno= t2.idno
and t1.dt >= t2.rdt
order by t1.idno, t1.dt

Expected Result
id----------------------idno--------------recover----dt-----------------------roid----opdesc---lineid----rodt---------------------csno
BAL0591111111890123461--1111111890123461--70419------2018-07-25 15:03:28.000--BAL059--created--	H_Line---2018-07-16 19:55:11.000--BAL0591111111890123461
BAL0451234567890123461--1234567890123461--NULL-------2018-06-13 21:37:33.000--BAL045--created--	H_Dis----2018-05-16 18:55:11.000--BAL0561234567890123461
BAL0451234567890123461--1234567890123461--NULL-------2018-06-15 23:43:01.000--BAL045--complete--H_Dis----2018-05-16 18:55:11.000--BAL0561234567890123461
BAL0561234567890123461--1234567890123461--70418------2018-06-30 15:03:28.000--BAL056--created--	H_kt-----2018-05-16 18:55:11.000--BAL0561234567890123461
BAL0561234567890123461--1234567890123461--70418------2018-07-05 12:25:31.000--BAL056--complete--H_kt-----2018-05-16 18:55:11.000--BAL0561234567890123461
SAL00CABCDE5B12AG12338--ABCDE5B12AG12338--58922------2018-02-15 10:48:03.000--SAL00C--created--	H_LineD--2018-01-02 10:59:11.000--ABCDE5B12AG123383814.0611
SAL00CABCDE5B12AG12338--ABCDE5B12AG12338--58922------2018-03-20 23:20:03.000--SAL00C--complete--H_LineD--2018-01-02 10:59:11.000--ABCDE5B12AG123383814.0611
SAL00AABCDE5B12AG12338--ABCDE5B12AG12338--NULL-------2018-06-13 14:59:24.000--SAL00A--created--	H_Dis----2018-04-18 07:34:34.000--ABCDE5B12AG123383814.0611
SAL00AABCDE5B12AG12338--ABCDE5B12AG12338--NULL-------2018-06-15 23:48:03.000--SAL00A--complete--H_Dis----2018-04-18 07:34:34.000--ABCDE5B12AG123383814.0611
SAL00BABCDE5B12AG12338--ABCDE5B12AG12338--58922------2018-06-30 15:02:25.000--SAL00B--created--	H_kt-----2018-04-18 07:34:34.000--ABCDE5B12AG123383814.0611
SAL00BABCDE5B12AG12338--ABCDE5B12AG12338--58922------2018-07-05 13:20:41.000--SAL00B--complete--H_kt-----2018-04-18 07:34:34.000--ABCDE5B12AG123383814.0611
BOI002ABCDE5B12AG33335--ABCDE5B12AG33335--80539------2018-06-26 08:42:01.000--BOI002--created--	H_Dis----2018-06-02 02:56:07.000--ABCDE5B12AG333352814.2771
BOI002ABCDE5B12AG33335--ABCDE5B12AG33335--80539------2018-07-10 10:33:01.000--BOI002--complete--H_Dis----2018-06-02 02:56:07.000--ABCDE5B12AG333352814.2771
2

Have this code and its working. I'm worried because I have millions of records. is there any alternative to simplify this code.

   SELECT r.*, p.id, p.idno as idnum, p.recover , p.dt, p.lineid, p.opdesc,
     DENSE_RANK() OVER(PARTITION BY r.idno,rdt ORDER BY roid DESC) 
     AS Seq
     FROM (SELECT *,LEAD(rdt,1,'20491231') OVER (PARTITION BY idno 
    ORDER BY rdt) AS NxtDate
    FROM @sampleReceipt) r
    JOIN @sampleTrans p
    ON p.idno = r.idno
   AND p.dt BETWEEN r.rdt AND NxtDate