Pull nearest date records

Villanuev · November 16, 2015, 7:06am

Hi,
I have a requirements to get the nearest date. I have 2 table t1 and t2. i need to use the date from t1 to get the nearest date from t2 table. I makea query but could not get the exact result. any help is very much appreciated. thank you.

please see below sample data and query

Create table #t1
(cs_no nvarchar(35), model nvarchar(35), audit_date datetime)
insert into #t1 (cs_no,model, audit_date)values('9000000000019761824','TOPR3000430PPH664SIL','2015-01-03 19:23:54.980')
insert into #t1 (cs_no,model, audit_date)values('990002595125962','COR3003110VS980','2015-05-09 14:56:41.447')
insert into #t1 (cs_no,model, audit_date)values('990002595125962','	COR3003110VS980','2015-06-29 12:09:17.160')


Create table #t2
(cs_no nvarchar(35), es_num nvarchar(35), prodid nvarchar(35), audit_date datetime, ctype varchar(36))
insert into #t2(cs_no, es_num , prodid , audit_date, ctype) values('9000000000019761824','354403060637509','PRC005271772','2015-03-20 16:43:07.000','PROD')
insert into #t2(cs_no, es_num , prodid , audit_date, ctype) values('9000000000019761824','354403060637509','PRC005065526','2015-02-07 09:50:08.000','PROD')
insert into #t2(cs_no, es_num , prodid , audit_date, ctype) values('990002595125962','990002595125962','PRC005294334','2015-03-26 16:54:22.000','PROD')
insert into #t2(cs_no, es_num , prodid , audit_date, ctype) values('990002595125962','990002595125962','PRC005506808','2015-05-17 00:59:55.000','PROD')	
insert into #t2(cs_no, es_num , prodid , audit_date, ctype) values('990002595125962','990002595125962','PRC004948361','2015-01-19 04:34:56.000','PROD')
insert into #t2(cs_no, es_num , prodid , audit_date, ctype) values('990002595125962','990002595125962','PRC005786594','2015-07-08 21:29:35.000','PROD')	


select a.*,x.audit_date, x.prodid, x.ctype 
from #t1 a
outer apply 
(select top  1 audit_date, prodid, ctype from  #t2 b where b.audit_date >=a.audit_date and b.cs_no=a.cs_no )x

cs_no================model=================audit_date===============audit_date==============prodid=======ctype
9000000000019761824--TOPR3000430PPH664SIL--2015-01-03 19:23:54.980--2015-02-07 09:50:08.000--PRC005065526--PROD
990002595125962------COR3003110VS980-------2015-05-09 14:56:41.447--2015-05-17 00:59:55.000--PRC005506808--PROD
990002595125962------COR3003110VS980-------2015-06-29 12:09:17.160--2015-07-08 21:29:35.000--PRC005786594--PROD

Mangal · November 16, 2015, 7:08am

What is the expected result?

Villanuev · November 16, 2015, 8:11am

thanks for the reply.

desired result:

cs_no================model=================audit_date===============audit_date==============prodid=======ctype
9000000000019761824--TOPR3000430PPH664SIL--2015-01-03 19:23:54.980--2015-02-07 09:50:08.000--PRC005065526--PROD
990002595125962------COR3003110VS980-------2015-05-09 14:56:41.447--2015-05-17 00:59:55.000--PRC005506808--PROD
990002595125962------COR3003110VS980-------2015-06-29 12:09:17.160--2015-07-08 21:29:35.000--PRC005786594--PROD

Mangal · November 16, 2015, 8:15am

Probably you just need ORDER BY inside the cross apply:

select a.*,x.audit_date, x.prodid, x.ctype 
from #t1 a
outer apply 
(select top  1 audit_date, prodid, ctype from  #t2 b where b.audit_date >=a.audit_date and b.cs_no=a.cs_no 
order by b.audit_date)x

Villanuev · November 19, 2015, 3:03am

Thank you. Just made a different scenario or twist the requirements getting the nearest receipt date before the fg_enddedate but the desired result is not accurate. below is the ddl an sample desired result.

I'm trying to use Ctrl-K but asking for insert hyperlink

create table #test
(esn nvarchar(35), esn_no nvarchar(35), prodnum nvarchar(35), createddate datetime, audit_date datetime)
insert into #test(esn,esn_no,prodnum,createddate,audit_date) values('358533052347000','358533052347000','PRC004900476','2015-01-05 09:06:39.000','2015-01-05 23:41:27.000')
insert into #test(esn,esn_no,prodnum,createddate,audit_date) values('T9000000000019757027','358535056542684','PRC004900192','2015-01-05 07:56:04.000','2015-01-05 19:48:29.000')
insert into #test(esn,esn_no,prodnum,createddate,audit_date) values('T9000000000019757037','358820050596088','PRC004900196','2015-01-05 08:10:50.000','2015-01-05 19:47:42.000')
insert into #test(esn,esn_no,prodnum,createddate,audit_date) values('990001218015915','990001218015915','PRC005851019','2015-07-20 17:24:14.000','2015-07-24 07:43:25.000')
insert into #test(esn,esn_no,prodnum,createddate,audit_date) values('990001218015915','990001218015915','PRC005374384','2015-04-17 05:01:51.000','2015-04-18 20:28:39.000')
insert into #test(esn,esn_no,prodnum,createddate,audit_date) values('990001218015915','990001218015915','PRC006223013','2015-09-28 00:29:46.000','2015-10-01 20:51:28.000')

create table #test2
(esn nvarchar(35), receiptdate datetime,optype int)

insert into #test2(esn, receiptdate ,optype )values ('990001218015915','2015-03-06 17:39:26.373',5)
insert into #test2(esn, receiptdate ,optype )values ('990001218015915','2015-06-16 11:23:42.523',5)
insert into #test2(esn, receiptdate ,optype )values ('990001218015915','2015-09-26 19:20:47.153',5)
insert into #test2(esn, receiptdate ,optype )values ('T9000000000019757027','2014-12-29 15:03:53.980',5)
insert into #test2(esn, receiptdate ,optype )values ('T9000000000019757037','2014-12-29 15:04:00.507',5)
insert into #test2(esn, receiptdate ,optype )values ('T9000000000019757042','2014-12-29 15:04:03.693',5)

select *
from
(
select distinct
s.esn as tlcsno
,s.esn_no
,x.receiptdate
,s.prodnum
,s.createddate
,s.audit_date as fg_endeddate
,x.optype
,row_number() over(partition by s.esn, s.createddate order by s.prodnum, s.audit_date asc) rn
from #test s
outer apply
(
select top 1 t.receiptdate, t.optype, t.esn
from #test2 t where t.receiptdate < s.audit_date and t.esn = s.esn order by t.receiptdate
) x
--left join #test2 tb
--on tb.optype ='5' and s.esn= tb.esn --COLLATE SQL_Latin1_General_CP850_CI_AS
)t
where
rn=1

----expected result

--tlcsno----------------esn_no------------receiptdate--------------prodnum-------createddate-------------fg_endeddate--------------optype-- rn
--========================================================================================================================================
--358533052347000-------358533052347000-- NULL---------------------PRC004900476--2015-01-05 09:06:39.000--2015-01-05 23:41:27.000--NULL-1
--990001218015915-------990001218015915-- 2015-03-06 17:39:26.373--PRC005374384--2015-04-17 05:01:51.000-2015-04-18 20:28:39.000--5----1
--990001218015915-------990001218015915-- 2015-06-16 11:23:42.523--PRC005851019--2015-07-20 17:24:14.000--2015-07-24 07:43:25.000--5----1
--990001218015915-------990001218015915-- 2015-09-26 19:20:47.153--PRC006223013--2015-09-28 00:29:46.000--2015-10-01 20:51:28.000--5----1
--T9000000000019757027--358535056542684-- 2014-12-29 15:03:53.980--PRC004900192--2015-01-05 07:56:04.000--2015-01-05 19:48:29.000--5----1
--T9000000000019757037--358820050596088-- 2014-12-29 15:04:00.507--PRC004900196--2015-01-05 08:10:50.000--2015-01-05 19:47:42.000--5----1

](test)

Villanuev · November 20, 2015, 3:08am

Please this regards this new posted reply of mine. I already accomplishded the desired result. again. thank you very much.