Most effecient way of finding records

Transact-SQL
1

Hi expert,

My script could not find the exact match of data. it reflects NULL values. I'm not sure if my scripts has a missing codes. based on my sample query the result has a null values in prodid, date ended, created date for this esn (352005064852935,352006061935814) which it has a records in prodtable and #data_scans. appreciate any help. thank you.

below a sample DDL and script. i provide a sample but in the actual data it has 1.4 million records. I'm using sql 2008 R2.
--the first script is to pull production scan and get the desired location

create table #t1
(esn_num nvarchar(35), audit_date datetime)
insert into #t1(esn_num,audit_date)values('352005064852935','2015-07-25 03:51:24.640')
insert into #t1(esn_num,audit_date)values('352006061935814','2015-09-05 01:28:39.800')
insert into #t1(esn_num,audit_date)values('352452060607048','2015-08-04 01:12:11.810')
insert into #t1(esn_num,audit_date)values('P9000000000020008008','2015-02-11 03:43:53.583')
insert into #t1(esn_num,audit_date)values('P9000000000019835962','2015-02-11 03:43:53.583')

create table #recover
(p_id nvarchar(35), serialnum nvarchar(35), recoverserial nvarchar(35),recoverdate datetime)
insert into #recover(p_id,serialnum,recoverserial,recoverdate)values('PRC005893647','P9000000000021254995','352005064852935','2015-07-28 03:43:53.583')
insert into #recover(p_id,serialnum,recoverserial,recoverdate)values('PRC006125852','P9000000000021579704','352006061935814','2015-9-10 03:43:53.583')
insert into #recover(p_id,serialnum,recoverserial,recoverdate)values('PRC005329518','P9000000000020008008','990005336416160','2015-4-7 03:43:53.583')
insert into #recover(p_id,serialnum,recoverserial,recoverdate)values('PRC006413076','P9000000000019835962','990002296677337','2015-11-10 03:43:53.583')

create table #prodtable
(prodid nvarchar(35), dimension nvarchar(35), enddeddate datetime,createddate datetime, status int)
insert into #prodtable(prodid,dimension,createddate,enddeddate,status)values('PRC005893647','DIM14497727','2015-8-5','2015-7-28',7)
insert into #prodtable(prodid,dimension,createddate,enddeddate,status)values('PRC006125852','DIM15214894','2015-9-10','2015-10-20',7)
insert into #prodtable(prodid,dimension,createddate,enddeddate,status)values('PRC005329518','DIM13206463','2015-4-7','2015-4-18',7)
insert into #prodtable(prodid,dimension,createddate,enddeddate,status)values('PRC006413076','DIM15679604','2015-11-10','',4)
insert into #prodtable(prodid,dimension,createddate,enddeddate,status)values('PRC006017426','DIM14685801','2015-8-19','2015-8-19',7)

create table #dimensiontbl
(dimension nvarchar(35), location nvarchar(35), invserialid nvarchar(35))
insert into #dimensiontbl(dimension,location,invserialid)values('DIM14497727','SOF','352005064852935')
insert into #dimensiontbl(dimension,location,invserialid)values('DIM15214894','SOF','352006061935814')
insert into #dimensiontbl(dimension,location,invserialid)values('DIM13206463','FG','990005336416160')
insert into #dimensiontbl(dimension,location,invserialid)values('DIM15679604','FG','P9000000000019835962')
insert into #dimensiontbl(dimension,location,invserialid)values('DIM14685801','FG','352452060607048')

if object_id('tempdb..#data_scans') is not null drop table #data_scans;
insert into #data_scans
select
coalesce(nullif(r.serialnum ,''),d.invserialid ) as cs_no,
d.invserialid,
r.recoverserial,
p.prodid, p.createddate ,p.enddeddate ,p.dimension, p.[status], d.location

from #prodtable p
inner join #dimensiontbl d on p.dimension = d.dimension
left join #recover r on p.prodid = r.p_id

--the second script i used the receipt table to get the esn exact match from production--final query select
select
t.esn_num, x.cs_no , x.invserialid ,t.audit_date, x.createddate , x.enddeddate, x.location , x.prodid, x.status
from #t1 t
outer apply
(
select top 1 t2.enddeddate , t2.prodid, t2.location, t2.cs_no , t2.invserialid , t2.createddate , t2.[status]
from #data_scans t2
where t2.enddeddate >= t.audit_date and t2.cs_no= t.esn_num
order by t2.enddeddate asc
)x

--Result of my query
result of my final queryesn_num---------tlcs_no-esn_num--receiptdate------------createddate---dateended--location--prodid--pstatus
352005064852935 NULL NULL 2015-07-25 03:51:26.450 NULL NULL NULL NULL NULL
352006061935814 NULL NULL 2015-09-05 01:28:41.493 NULL NULL NULL NULL NULL
352452060607048 352452060607048 352452060607048 2015-08-04 01:12:11.810 2015-08-28 00:00:00.000 2015-08-20 00:00:00.000 FG-L PRC006017426 7
P9000000000020008008 P9000000000020008008 990005336416160 2015-02-11 03:43:53.583 2015-08-19 00:00:00.000 2015

2

Hi, anybody help with my query?

By the way, I tried to use ctrl -K to arrange the coding but it appears insert hyperlink. this is wat i did highlight the codes and i press ctl-K but did not work.

3

Just modified this code and i got the result that i wanted. May i know your idea if this query is reliable or good enough also id this will have an effect on the performance specially on faster querying of records.

select
coalesce(nullif(r.serialnum ,''),d.invserialid ) as cs_no,
d.invserialid,
r.recoverserial,
p.prodid, p.createddate ,p.enddeddate ,p.dimension, p.[status], d.location
into #data_scans
from #prodtable p
inner join #dimensiontbl d on p.dimension = d.dimension
left join #recover r on p.prodid = r.p_id

--the second script i used the receipt table to get the esn exact match from production--final query select

select
t.esn_num, x.cs_no , coalesce(x.invserialid,y.invserialid) as esn_no , t.audit_date, coalesce(x.createddate,y.createddate) as createddate
, coalesce(x.enddeddate, y.enddeddate) as enddeddate ,
coalesce(x.location ,y.location) as location, coalesce(x.prodid, y.prodid) as prodid, coalesce(x.status, y.status) as status

from #t1 t
outer apply
(
select top 1 t2.enddeddate , t2.prodid, t2.location, t2.cs_no , t2.invserialid , t2.createddate , t2.[status]
from #data_scans t2
where t2.enddeddate >= t.audit_date and t2.cs_no= t.esn_num
order by t2.enddeddate asc
)x
outer apply
(
select top 1 t3.enddeddate , t3.prodid, t3.location, t3.cs_no , t3.invserialid , t3.createddate , t3.[status]
from #data_scans t3
where t3.enddeddate >= t.audit_date and t3.invserialid = t.esn_num
order by t3.enddeddate asc
)y