Compare Records with values from different table

Transact-SQL
,
1

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
2

Just want to share this sample data. two different transaction with the same recoverednum and unifiedserial. just want to know if the unifiedserial createddatetime plus the startdate can be use to match the transaction. in actual process the first to transact is poolnum with ''IN-BD'' then followed with ''IP-BM''.

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
('T008311518089249843905318439','B89249843905318439','2016-10-08 12:39:00.000','T008311518','2016-10-13 04:27:31.000','T49843905318439M'),
('T008311518089249843905318439','B89249843905318439','2016-11-05 11:11:00.000','T008524845','2016-11-16 08:50:51.000','T49843905318439M')
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
('T49843905318439M','P07037433','IN-BD','2016-10-13 16:19:46.000','2016-10-14 01:23:42.000','tb_tb-BD1'),
('T49843905318439M','P07418769','IN-BD','2016-11-09 16:07:07.000','2016-11-13 04:10:30.000','tb_tb-BD1'),
('T49843905318439M','P07590987','IN-BD','2016-11-18 03:22:43.000','2016-11-18 22:41:50.000','tb_tb-BD1' )
3

Just made this script and used the first sample data and so far the records captured correctly. Using the next sample data I posted just encounter an issue. The first row is correct but the second row the data captured should be the latest not the nearest. this encounter when there is a multiple transaction. I'm using createddatetime as reference. the startdate and enddate for the second row should be this data 2016-11-20 03:22:43.000 and 2016-11-21 22:41:50.000.
Is there any idea guys on how to capture this records. thanks.

;with cte as
(
 select unifiednumber, pid, poolnum, startdate, enddate, linenum,
  row_number() over(partition by unifiednumber order by startdate) row_num
		
 from #tblHeader 
 where poolnum='IN-BD'
)--, cte2 as
--(
 select 
			rs1.unifiednumber, rs1.pid, rs1.poolnum, rs1.startdate, rs1.enddate, rs1.linenum,
			isnull(rs3.startdate,dateadd(year,-2,rs1.startdate)) previous_audit_date,
         isnull(rs2.startdate,dateadd(year,2,rs1.startdate)) next_audit_date,
		          rs1.row_num,
         rs2.row_num as rownum2,
         rs3.row_num as rownum3
into #data
from     cte rs1
         left join cte rs2 
            on rs1.unifiednumber = rs2.unifiednumber
            and rs1.row_num + 1 = rs2.row_num
         left join cte rs3
            on rs1.unifiednumber = rs3.unifiednumber
            and rs1.row_num - 1 = rs3.row_num


select tcps.esnnum, tcps.recoverednum, tcps.receiptdate, tcps.createddatetime, tcps.unifiednumber,
		pno.poolnum, pno.pid, pno.linenum, pno.startdate, pno.enddate
from #tblRegister tcps
         left join #data pno
					on tcps.unifiednumber = pno.unifiednumber collate chinese_taiwan_stroke_ci_as
					and tcps.createddatetime between pno.previous_audit_date and  pno.startdate

Sample Result : using the second sample data

esnnum	                        recoverednum	         receiptdate	       createddatetime	        unifiednumber	        poolnum	     pid	linenum	          startdate	            enddate
T008311518089249843905318439	B89249843905318439	2016-10-08 12:39:00.000	2016-10-13 04:27:31.000	T49843905318439M	IN-BD	P07037433	tb_tb-BD1	2016-10-13 16:19:46.000	2016-10-14 01:23:42.000
T008311518089249843905318439	B89249843905318439	2016-11-05 11:11:00.000	2016-11-16 08:50:51.000	T49843905318439M	IN-BD	P07590987	tb_tb-BD1	2016-11-18 03:22:43.000	2016-11-18 22:41:50.000
4

Any idea on how to achieve this result in getting the latest records. thanks.

5

Hi I am looking at this .. Could you please be "Clear"
about what you are looking for ? so that i can follow you and if needed help you out .. Thanks

6

Hi, Please see sample below data. this is the result that I would like be. thank you.

register data:

itemcode	  |ponum		|id2  |item	|wrkctr	|serialid |em_serial |date
---------------------------------------------------------------------------------------------
A5912947270138|P008279816	|S7843|A13	|FEntry	|12345678F|aaaaaaaaaa|2016-10-07 06:20:54.000
A5912947270138|P008508629	|S8479|A13	|FEntry	|12345678F|bbbbbbbbbb|2016-11-14 06:32:58.000
A5936181820133|P008281945	|S7809|A24	|FEntry	|56789045D|ffffffffff|2016-10-07 14:48:13.000
A5936181820133|P008510773	|S8482|A24	|FEntry	|56789045D|gggggggggg|2016-11-14 09:36:37.000



details data:

|line	|pool|	ponum	|itemid	|location	|date					|serialid
-----------------------------------------------------------------------------------------
L01 	|PC	 |P06965620	|pc13-t	|Entry		|2016-10-08 08:34:53.000|12345678F	
L01 	|PC	 |P06965620	|pc13-t	|Reject		|2016-10-08 08:44:45.000|12345678F	
L01 	|PC	 |P07628121	|pc24-t	|Entry		|2016-11-19 17:03:27.000|56789045D	
L01 	|PC	 |P07628121	|pc24-T	|Reject		|2016-11-19 17:37:18.000|56789045D	
L01 	|PC	 |P07891150	|pc24-t	|Entry		|2016-12-06 17:07:40.000|56789045D	
L01 	|PC	 |P07891150	|pc24-t	|Reject		|2016-12-06 17:17:18.000|56789045D


Desired Result:

|wrkctr	|serialid |em_serial |date					 |date2					 |location1|date3				   |location2|itemid
---------------------------------------------------------------------------------------------------------------------------------
|FEntry	|12345678F|aaaaaaaaaa|2016-10-07 06:20:54.000|2016-10-08 08:34:53.000|Entry	   |2016-10-08 08:44:45.000|Reject   |pc13-t
|FEntry	|12345678F|bbbbbbbbbb|2016-11-14 06:32:58.000|						 |		   |					   |
|FEntry	|56789045D|ffffffffff|2016-10-07 14:48:13.000|2016-11-19 17:03:27.000|Entry	   |2016-11-19 17:37:18.000|Reject	 |pc24-t	
|FEntry	|56789045D|gggggggggg|2016-11-14 09:36:37.000|2016-12-06 17:17:18.000|Entry	   |2016-12-06 17:17:18.000|Reject	 |pc24-t