I'm using SQL 2008 R2. Already requested to install SQL 2012.
I have the following sample table tblRegister and tblHeader. I want to obtain the recods using unifiedserial from tblRegister and then match the records tblHeader. Couldn't find the solution if will used the date to match the records.
My concern if recoverednum will comeback and create differerent pid with same unifiednumber. how can I capture this separate if will will use unifiednumber. This is a tricky part. I don't have any reference, I don't know if the date can be use as other reference.
Please see below sample DDL and desired result:
Create table #tblRegister
( esnnum nvarchar(50),
recoverednum nvarchar(50),
receiptdate datetime,
pid nvarchar(10),
createddatetime datetime,
unifiednumber nvarchar(50)
)
insert into #tblRegister (esnnum,recoverednum,receiptdate,pid, createddatetime, unifiednumber) values
('T008265112B89366733401463396','B89366733401463396','2016-09-29 01:40:00.000','T008265112','2016-10-05 13:34:58.000','V161619880'),
('T008664116B89779994200160279','B89779994200160279','2016-11-22 13:25:00.000','T008664116','2016-12-08 11:10:13.000','9994200160279AM'),
('T008672862B89609549407485080','B89609549407485080','2016-12-07 05:35:00.000','T008672862','2016-12-09 14:56:30.000','9549407485080AM'),
('T008508625B89780685401151636','B89780685401151636','2016-11-07 22:35:00.000','T008508625','2016-11-13 02:30:29.000','80685401151636M'),
('T008298526B89780685401151636','B89780685401151636','2016-10-10 10:31:00.000','T008298526','2016-10-11 11:58:41.000','80685401151636M')</code>
Create table #tblHeader
( unifiednumber nvarchar(50),
pid nvarchar(10),
poolnum nvarchar(10),
startdate datetime,
enddate datetime,
linenum nvarchar(20)
)
insert into #tblHeader ( unifiednumber, pid, poolnum, startdate, enddate,linenum ) values
('V161619880','P06941235','IN-BD','2016-10-07 01:48:37.000','2016-10-07 12:16:49.000','Tb_T5-BD1'),
('V161619880','P06958465','IP-BM','2016-10-08 00:24:24.000','2016-10-11 11:18:44.000','Tb_BM5'),
('V161619880','P07034882','IP-BM','2016-10-13 05:06:23.000','2016-10-13 19:37:53.000','Tb_BM5-R'),
('9994200160279AM','P07947296','IN-BD','2016-12-08 18:04:02.000','2016-12-09 05:34:09.000','Tb_T5-BD1'),
('9994200160279AM','P07962025','IP-BM','2016-12-09 07:29:09.000','2016-12-09 20:27:07.000','Tb_BM4'),
('9549407485080AM','P08045188','IN-BD','2016-12-12 20:17:09.000','2016-12-14 11:28:03.000','Tb_T5-BD1e'),
('80685401151636M','P07005168','IN-BD','2016-10-11 12:17:06.000','2016-10-11 19:25:17.000','Tb_T5-BD1'),
('80685401151636M','P07018016','IP-BM','2016-10-12 06:38:52.000','2016-10-12 21:23:13.000','Tb_BM8'),
('80685401151636M','P07515398','IN-BD','2016-11-14 08:36:19.000','2016-11-15 12:29:55.000','Tb_T5-BD1')
select r.pid+r.recoverednum as uniquenum, r.recoverednum, r.receiptdate, r.pid, r.createddatetime, r.unifiednumber
from #tblRegister r
left join (
select unifiednumber, pid, poolnum, startdate, enddate, linenum, [type]='IN-BD',
row_number() over (partition by unifiednumber order by startdate) rnk_l
from #tblHeader
where poolnum='IN-BD'
union all
select unifiednumber, pid, poolnum, startdate, enddate, linenum, [type]='IP-BM',
row_number() over (partition by unifiednumber order by startdate) rnk_l
from #tblHeader
where poolnum='IP-BM'
)t on r.unifiednumber=t.unifiednumber
Sample Result: Just show some selected columns only .
uniquenum---------------------recoverednum--------receiptdate--------------pid---------createddatetime-----------unifiednumber----type----startdate_BD-------------type2--startdate_BM--
T008265112B89366733401463396--B89366733401463396--2016-09-29 01:40:00.000--T008265112---2016-10-05 13:34:58.000--V161619880-------IN-BD---2016-10-07 01:48:37.000--
T008298526B89780685401151636--B89780685401151636--2016-10-10 10:31:00.000--T008298526-- 2016-10-11 11:58:41.000--80685401151636M--IN-BD---2016-10-11 12:17:06.000--IN-BM--016-10-12 06:38:52.000
T008508625B89780685401151636--B89780685401151636--2016-11-07 22:35:00.000--T008508625-- 2016-11-13 02:30:29.000--80685401151636M--IN-BD---2016-11-14 08:36:19.000--IN-BM--2016-10-13 05:06:23.000
T008664116B89779994200160279--B89779994200160279--2016-11-22 13:25:00.000--T008664116-- 2016-12-08 11:10:13.000--9994200160279AM--IN-BD---2016-12-08 18:04:02.000--IN-BM--2016-12-09 07:29:09.000
T008672862B89609549407485080--B89609549407485080