Matching pairs of rows

Transact-SQL
1

Hi, i have a data that came from my main query which i need to manipulate by matching a pair of records using esn1, en3 and it should be matching with pairs of descript CMA Receive and CMA Shipping. if the esn does not have this 2 pairs of descript it should not be included. CMA Receive and CMA shipping, this is a receive transaction from warehouse so its always an equal descript. Could not figure out on how to get the result based on the sample below. below is a sample DDL.

[code]

create table #s1
(esn1 nvarchar(35), esn2 nvarchar(35), esn3 nvarchar(35), prodid nvarchar(35), trndate datetime, operation nvarchar(35), descript nvarchar(35))
insert into #s1
select '354388062058004','354388062058004','NULL','NULL','2016-02-09 01:59:37.093','CMA Receive','CMA Receive' union all
select '354388062058004','354388062058004','NULL','NULL','2016-02-09 01:59:38.947','CMA Shipping','CMA Shipping' union all
select 'B9000000000022698487','B9000000000022698487','354388062058004','PRC006826971','2016-02-11 13:05:39.000','Create Order','Open PO' union all
select 'B9000000000022706825','B9000000000022706825','NULL','NULL','2016-02-09 23:37:40.900','CMA Receive','CMA Receive' union all
select 'B9000000000022706825','B9000000000022706825','NULL','NULL','2016-02-09 23:37:42.560','CMA Shipping','CMA Shipping' union all
select 'B9000000000022706825','B9000000000022706825','358536054597910','PRC006840220','2016-02-13 23:52:26.000','Create Order','Open PO' union all
select 'B9000000000022416383','352006066338378','352006066338378','PRC006721162','2016-01-05 13:45:25.670','CMA Receive','CMA Receive' union all
select 'B9000000000022416383','352006066338378','352006066338378','PRC006721162','2016-01-05 13:45:27.290','CMA Shipping','CMA Shipping' union all
select 'B9000000000022346886','B9000000000022346886','NULL','NULL','2016-01-08 23:36:55.157','Write-Off','Write-Off' union all
select 'B9000000000022346917','359238068445033','359238068445033','PRC006709807','2016-01-19 05:07:10.000','Create Order','Create Order' union all
select 'B9000000000022346917','359238068445033','359238068445033','PRC006709807','2016-01-19 07:55:19.000','Order Complete','Scrap' union all
select 'B9000000000022420604','356986068725332','356986068725332','PRC006766546','2016-01-05 23:15:37.860','CMA Receive','CMA Receive' union all
select 'B9000000000022420604','356986068725332','356986068725332','PRC006766546','2016-01-05 23:15:39.390','CMA Shipping','CMA Shipping' union all
select 'B9000000000022420604','356986068725332','356986068725332','PRC006766546','2016-01-28 12:26:21.000','Create Order','Create Order' union all
select 'B9000000000022420604','356986068725332','356986068725332','PRC006766546','2016-01-29 04:14:46.000','Order Complete','FG' union all
select 'B9000000000022420604','356986068725332','356986068725332','PRC006766546','2016-01-29 14:36:23.853','Shipping','Shipping' union all
select '357991057750878','357991057750878','NULL','NULL','2016-02-08 11:52:01.450','Receive','Receive' union all
select '357991057750878','357991057750878','NULL','NULL','2016-02-08 11:52:29.827','Pass',' Pass'

declare @begdate datetime
declare @enddate datetime
set @begdate ='2016-1-1'
set @enddate =getdate()

select esn1, esn2 , esn3, prodid , trndate, operation, descript
from #s1
where trndate between @begdate and @enddate
and operation in ('CMA Receive','CMA Shipping','Create Order','Order Complete','Shipping')

sample result 1
esn1-------------------------esn2-------------- esn3-------------- prodid--------trndate------------------operation------- descript

354388062058004----------354388062058004------- NULL-------------- NULL----------2016-02-09 01:59:37.093--CMA Receive----- CMA Receive
354388062058004----------354388062058004------- NULL-------------- NULL----------2016-02-09 01:59:38.947--CMA Shipping---- CMA Shipping
B9000000000022698487-----B9000000000022698487-- 354388062058004--- PRC006826971--2016-02-11 13:05:39.000--Create Order---- Open PO
B9000000000022706825-----B9000000000022706825-- NULL-------------- NULL----------2016-02-09 23:37:40.900--CMA Receive----- CMA Receive
B9000000000022706825-----B9000000000022706825-- NULL-------------- NULL----------2016-02-09 23:37:42.560--CMA Shipping---- CMA Shipping
B9000000000022706825-----B9000000000022706825-- 358536054597910--- PRC006840220--2016-02-13 23:52:26.000--Create Order---- Open PO
B9000000000022416383-----352006066338378------- 352006066338378--- PRC006721162--2016-01-05 13:45:25.670--CMA Receive----- CMA Receive
B9000000000022416383-----352006066338378------- 352006066338378--- PRC006721162--2016-01-05 13:45:27.290--CMA Shipping---- CMA Shipping
B9000000000022420604-----356986068725332------- 356986068725332--- PRC006766546--2016-01-05 23:15:37.860--CMA Receive----- CMA Receive
B9000000000022420604-----356986068725332------- 356986068725332--- PRC006766546--2016-01-05 23:15:39.390--CMA Shipping---- CMA Shipping
B9000000000022420604-----356986068725332------- 356986068725332--- PRC006766546--2016-01-28 12:26:21.000--Create Order---- Create Order
B9000000000022420604-----356986068725332------- 356986068725332--- PRC006766546--2016-01-29 04:14:46.000--Order Complete-- FG
B9000000000022420604-----356986068725332------- 356986068725332--- PRC006766546--2016-01-29 14:36:23.853--Shipping-------- Shipping

sample result 2
esn1-------------------------esn2-------------- esn3-------------- prodid--------receipt trndate---------------operation----descript---------po_created----------------po_ended---------------shipped date

354388062058004----------354388062058004------- 354388062058004--- PRC006826971--2016-02-09 01:59:37.093--CMA Receive----- CMA Receive--2016-02-11 13:05:39.00
B9000000000022706825-----B9000000000022706825-- 358536054597910--- PRC006840220--2016-02-09 23:37:40.900--CMA Receive----- CMA Receive--2016-02-13 23:52:26.00
B9000000000022416383-----352006066338378------- 352006066338378--- PRC006721162--2016-01-05 13:45:25.670--CMA Receive----- CMA Receive
B9000000000022420604-----356986068725332------- 356986068725332--- PRC006766546--2016-01-05 23:15:37.860--CMA Receive----- CMA Receive--2016-01-28 12:26:21.000--2016-01-29 04:14:46.000--2016-01-29 14:36:23.853[/code]

2

you need to explain what is the logic / business rules to obtain the expected result ( is that sample result 2 ?)

3

Just made this script and so far i'm getting the desired result but when i apply to actual data it takes time to process. Is there any idea to optmize this script. thanks.

this is the script for my first requirements (sample result 1)

SELECT * FROM #s1 A INNER JOIN #s1 B on A.esn1 = B.esn1 or A.esn1 = B.esn3 WHERE A.operation = 'CMA Receive'

4

these are the "matching" records ? The others seems to have the same esn1 and esn2. This doesn't

Just saw your last post. Looks like the condition is

  on A.esn1 = B.esn1 or A.esn1 = B.esn3
5

maybe this

select    r.esn1, r.esn2 , p.esn3, p.prodid , r.trndate, r.operation, r.descript, p.*
from    #s1 r
        inner join #s1 p    on    r.esn1    = p.esn1
                            or    r.esn1    = p.esn3
where   r.trndate  between @begdate and @enddate  
and     r.operation in ('CMA Receive')
and     p.operation in ('Create Order')

what are the indexes on the table ?

6

This is the final steps from my query.

--=====================================================
-- Union scans from all sources
--=====================================================
if object_id('tempdb..#final') is not null drop table #final;
create table #final(
tlcs_no varchar(35),
esn_no varchar(25),
recoveredserial varchar(25),
prodid varchar(20),
operation varchar(50),
descript varchar(50));

insert into #final
select *
from #temp_tcp_scans_tlcs2
;
insert into #final
select *
from #temp_production_scans
;

7

Hi Kthan not sure with my query on getting the below result specially the sample result 2. by the way, thanks for your prompt reply.

This is the desired result that i would like my query result. could not figure out on how to get this. any help is very much appreciated.

[code]sample result 1
esn1-------------------------esn2-------------- esn3-------------- prodid--------trndate------------------operation------- descript

354388062058004----------354388062058004------- NULL-------------- NULL----------2016-02-09 01:59:37.093--CMA Receive----- CMA Receive
354388062058004----------354388062058004------- NULL-------------- NULL----------2016-02-09 01:59:38.947--CMA Shipping---- CMA Shipping
B9000000000022698487-----B9000000000022698487-- 354388062058004--- PRC006826971--2016-02-11 13:05:39.000--Create Order---- Open PO
B9000000000022706825-----B9000000000022706825-- NULL-------------- NULL----------2016-02-09 23:37:40.900--CMA Receive----- CMA Receive
B9000000000022706825-----B9000000000022706825-- NULL-------------- NULL----------2016-02-09 23:37:42.560--CMA Shipping---- CMA Shipping
B9000000000022706825-----B9000000000022706825-- 358536054597910--- PRC006840220--2016-02-13 23:52:26.000--Create Order---- Open PO
B9000000000022416383-----352006066338378------- 352006066338378--- PRC006721162--2016-01-05 13:45:25.670--CMA Receive----- CMA Receive
B9000000000022416383-----352006066338378------- 352006066338378--- PRC006721162--2016-01-05 13:45:27.290--CMA Shipping---- CMA Shipping
B9000000000022420604-----356986068725332------- 356986068725332--- PRC006766546--2016-01-05 23:15:37.860--CMA Receive----- CMA Receive
B9000000000022420604-----356986068725332------- 356986068725332--- PRC006766546--2016-01-05 23:15:39.390--CMA Shipping---- CMA Shipping
B9000000000022420604-----356986068725332------- 356986068725332--- PRC006766546--2016-01-28 12:26:21.000--Create Order---- Create Order
B9000000000022420604-----356986068725332------- 356986068725332--- PRC006766546--2016-01-29 04:14:46.000--Order Complete-- FG
B9000000000022420604-----356986068725332------- 356986068725332--- PRC006766546--2016-01-29 14:36:23.853--Shipping-------- Shipping

sample result 2
esn1-------------------------esn2-------------- esn3-------------- prodid--------receipt trndate---------------operation----descript---------po_created----------------po_ended---------------shipped date

354388062058004----------354388062058004------- 354388062058004--- PRC006826971--2016-02-09 01:59:37.093--CMA Receive----- CMA Receive--2016-02-11 13:05:39.00
B9000000000022706825-----CMA Receive--2016-02-13 23:52:26.00
B9000000000022416383-----352006066338378------- 352006066338378--- PRC006721162--2016-01-05 13:45:25.670--CMA Receive----- CMA Receive
B9000000000022420604-----356986068725332------- 356986068725332--- PRC006766546--2016-01-05 23:15:37.860--CMA Receive----- CMA Receive--2016-01-28 12:26:21.000--2016-01-29 04:14:46.000--2016-01-29 14:36:23.853[/code]

8

so what is sample result 1 and sample result 2 ?

Does the last query that i posted gives you the required result ?

9

Yes kthan i only remove this portion and p.operation in ('Create Order')
also i will add index in my created temp table #final. the sample result 2 this is the final output per esn with the corresponding description. let say this esn B9000000000022420604 has been receive (CMA receive) and perform the following process, create Order, Order complete and shipping.

10

This is the sample for Result 2:

[code]sample result 2
esn1-------------------------esn2-------------- esn3-------------- prodid--------receipt trndate---------------operation----descript---------po_created----------------po_ended---------------shipped date

354388062058004----------354388062058004------- 354388062058004--- PRC006826971--2016-02-09 01:59:37.093--CMA Receive----- CMA Receive--2016-02-11 13:05:39.00
B9000000000022706825-----B9000000000022706825-- 358536054597910--- PRC006840220--2016-02-09 23:37:40.900--CMA Receive----- CMA Receive--2016-02-13 23:52:26.00
B9000000000022416383-----352006066338378------- 352006066338378--- PRC006721162--2016-01-05 13:45:25.670--CMA Receive----- CMA Receive
B9000000000022420604-----356986068725332------- 356986068725332--- PRC006766546--2016-01-05 23:15:37.860--CMA Receive----- CMA Receive--2016-01-28 12:26:21.000--2016-01-29 04:14:46.000--2016-01-29 14:36:23.853[/code]

11

Hi Guys, any help to achieve the sample result 2. thanks.

12

Actually the earlier query is almost there, just need to tweak a bit.

SELECT r.esn1
	,r.esn2
	,esn3 = coalesce(p.esn3, r.esn3)
	,prodid = coalesce(p.prodid, r.prodid)
	,r.trndate
	,r.operation
	,r.descript
	,po_created = p.trndate
	,po_ended = e.trndate
	,shipped_date = s.trndate
FROM #s1 r
LEFT JOIN #s1 p ON (
		r.esn1 = p.esn1
		OR r.esn1 = p.esn3
		)
	AND p.operation IN ('Create Order')
LEFT JOIN #s1 e ON (
		r.esn1 = e.esn1
		OR r.esn1 = e.esn3
		)
	AND e.operation IN ('Order Complete')
LEFT JOIN #s1 s ON (
		r.esn1 = s.esn1
		OR r.esn1 = s.esn3
		)
	AND s.operation IN ('Shipping')
WHERE r.trndate BETWEEN @begdate
		AND @enddate
	AND r.operation IN ('CMA Receive')
13

Hi Kthan, the issue that i encounter is it takes time to perform using OR. is this possile to breakdown into 2 layer of script for every process like this left join r.esn1 = p.esn1 and r.esn1 = p.esn3.

14

do you have index on those JOIN columns ?

15

this is what i have. the #final i perform union all for my 2 temp table and i dump it to this temp table.
this is the table structure.

-- Union scans from all sources

[code]if object_id('tempdb..#final') is not null drop table #final;

create table #final(
tlcs_no varchar(35),
esn_no varchar(25),
recoveredserial varchar(25),
prodid varchar(20),
asuprodlineid nvarchar(20),
audit_date datetime,
supplier_code varchar(35),
model_code varchar(25),
model_type varchar(25),
core_type varchar(25),
supplier_ref_no nvarchar(50),
operation_type int,
operation_description varchar(50),
op_desc varchar(50));

insert into #final
select *
from #temp_trp_scans_tlcs2
;
insert into #final
select *
from #temp_production_scans

create index #final on #final(tlcs_no,esn_no,recoveredserial)[/code]

16

May i know if this is correct in creating index.