Find the Mitmatched Pair(s) - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Find the Mitmatched Pair(s)

Author

Topic

chrisnorris007
Starting Member

18 Posts

Posted - 2014-07-23 : 14:35:12

I have a set of data like so
ID HDRID segment
1 2 PA1*101*100
2 2 PA2*8*3 (each PA1 should have a PA2 proceeding it for a particular hdrid)

and I have some records like this without matching PA1 records for a particular hdrid
ID HDRID segment
1 1 PA2*3*3
2 1 PA2*4*48
3 1 PA1*101*100
4 1 PA2*4*48

i want the HDRID,ID and segment for the mismatched records

result set should be:

1,1,PA2*3*3
1,2,PA2*4*48

I have tried to wrap my brain around this and I can't seem to do it.

Any ideas?

Chris

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-23 : 14:39:10

You can use the ROW_NUMBER() function for this. Here's an example to get you started, grabbed part of it from BOL:

SELECT FirstName, LastName, TerritoryName, SalesYTD
FROM (
SELECT FirstName, LastName, TerritoryName, ROUND(SalesYTD,2,1) AS SalesYTD,
ROW_NUMBER() OVER(PARTITION BY TerritoryName ORDER BY SalesYTD DESC) AS Row
FROM Sales.vSalesPerson
WHERE TerritoryName IS NOT NULL AND SalesYTD <> 0
) dt
WHERE Row IN (1,2)

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-23 : 14:39:23

http://msdn.microsoft.com/en-us/library/ms186734.aspx

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

chrisnorris007
Starting Member

18 Posts

Posted - 2014-07-23 : 14:41:04

well the problem is the match has to be on the fact that the row has a PA1 but no PA2 on the next row

a mismatched PA1/PA2

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-23 : 14:41:46

http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

chrisnorris007
Starting Member

18 Posts

Posted - 2014-07-23 : 14:43:39

no offense, but how does performance problems diagnosis help me?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-23 : 14:48:06

Sorry I pasted in the wrong link. Please post your tables/data/question like this so that we can test on our own machines: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

chrisnorris007
Starting Member

18 Posts

Posted - 2014-07-23 : 15:10:29

IF OBJECT_ID('TempDB..#testhdrrecords','U') IS NOT NULL
DROP TABLE #testhdrrecords

CREATE TABLE #testhdrrecords
(
HdrID INT IDENTITY(1,1),
ReadDateTime DATETIME,
MachineNum INT,
ActiveDate DateTime
)

IF OBJECT_ID('TempDB..#testsegrecords','U') IS NOT NULL
DROP TABLE #testsegrecords

CREATE TABLE #testsegrecords
(
SegID INT IDENTITY(1,1),
HdrID INT,
Segment NVARCHAR(50),
ActiveDate DateTime
)

--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #testhdrrecords ON

--===== Insert the test data into the test table
INSERT INTO #testhdrrecords
(HdrID, ReadDateTime,MachineNum,ActiveDate)
SELECT '10','Oct 17 2007 12:00AM',200,'10-17-2007 08:00:00.000' UNION ALL
SELECT '22','Oct 17 2007 4:00AM',2128,'10-17-2007 08:00:00.000' UNION ALL
SELECT '33','Oct 17 2007 6:00AM',528,'10-17-2007 08:00:00.000'

--===== Set the identity insert back to normal
SET IDENTITY_INSERT #testhdrrecords OFF

--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #testsegrecords ON

--===== Insert the test data into the test table
INSERT INTO #testsegrecords
(SegID,HdrID,Segment,ActiveDate)
SELECT 1,10,'PA1*100*3','03-03-2007 08:00.000' UNION ALL
SELECT 2,10,'PA2*3*2','03-03-2007 03:00.000' UNION ALL
SELECT 3,10,'PA1*200*4','03-03-2007 02:00.000' UNION ALL
SELECT 4,10,'PA2*4*2','03-03-2007 01:00.000' UNION ALL
SELECT 5,22,'PA2*3*3','03-03-2007 01:00.000' UNION ALL
SELECT 6,22,'PA2*4*3','03-03-2007 01:00.000' UNION ALL
SELECT 7,22,'PA1*100*3','03-03-2007 01:00.000' UNION ALL
SELECT 8,22,'PA2*3*2','03-03-2007 01:00.000'

--===== Set the identity insert back to normal
SET IDENTITY_INSERT #testsegrecords OFF

I need to have the result set as follows:

5,22,PA2*3*3
6,22,PA2*4*3
because those two are the only mismatched (PA2 then a PA2) pairs (the rest are first the PA1 then the PA2)

is this more like what you are looking for?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-23 : 15:58:00

I see what you mean now. I threw out the ROW_NUMBER() option due to the topic's subject.

This one is beyond my T-SQL skills, so someone else will need to help. You're in good hands here. SwePeso, Lamprey, visakh, etc should be along shortly to assist.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

chrisnorris007
Starting Member

18 Posts

Posted - 2014-07-23 : 15:58:37

thank you for your attempts ;)

chrisnorris007
Starting Member

18 Posts

Posted - 2014-07-23 : 15:59:54

I changed the topic

chrisnorris007
Starting Member

18 Posts

Posted - 2014-07-23 : 16:40:51

WHAT ABOUT THIS?

IF OBJECT_ID('TempDB..#testhdrrecords','U') IS NOT NULL
DROP TABLE #testhdrrecords

CREATE TABLE #testhdrrecords
(
HdrID INT IDENTITY(1,1),
ReadDateTime DATETIME,
MachineNum INT,
ActiveDate DateTime
)

IF OBJECT_ID('TempDB..#testsegrecords','U') IS NOT NULL
DROP TABLE #testsegrecords

CREATE TABLE #testsegrecords
(
SegID INT IDENTITY(1,1),
HdrID INT,
Segment NVARCHAR(50),
ActiveDate DateTime
)

--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #testhdrrecords ON

--===== Insert the test data into the test table
INSERT INTO #testhdrrecords
(HdrID, ReadDateTime,MachineNum,ActiveDate)
SELECT '10','Oct 17 2007 12:00AM',200,'10-17-2007 08:00:00.000' UNION ALL
SELECT '22','Oct 17 2007 4:00AM',2128,'10-17-2007 08:00:00.000' UNION ALL
SELECT '33','Oct 17 2007 6:00AM',528,'10-17-2007 08:00:00.000'

--===== Set the identity insert back to normal
SET IDENTITY_INSERT #testhdrrecords OFF

--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #testsegrecords ON

--===== Insert the test data into the test table
INSERT INTO #testsegrecords
(SegID,HdrID,Segment,ActiveDate)
SELECT 1,10,'PA1*100*3','03-03-2007 08:00.000' UNION ALL
SELECT 2,10,'PA2*3*2','03-03-2007 03:00.000' UNION ALL
SELECT 3,10,'PA1*200*4','03-03-2007 02:00.000' UNION ALL
SELECT 4,10,'PA2*4*2','03-03-2007 01:00.000' UNION ALL
SELECT 5,22,'PA2*3*3','03-03-2007 01:00.000' UNION ALL
SELECT 6,22,'PA2*4*3','03-03-2007 01:00.000' UNION ALL
SELECT 7,22,'PA1*100*3','03-03-2007 01:00.000' UNION ALL
SELECT 8,22,'PA2*3*2','03-03-2007 01:00.000' union all
SELECT 9,33,'PA1*1*1','03-03-2007 01:00.000' union all
select 10,33,'PA2*2*3','03-03-2007 01:00.000' union all
select 11,33,'PA2*2*3','03-03-2007 01:00.000' union all
select 12,33,'PA2*2*3','03-03-2007 01:00.000'

--===== Set the identity insert back to normal
SET IDENTITY_INSERT #testsegrecords OFF

--I need to have the result set as follows:

--5,22,PA2*3*3
--6,22,PA2*4*3

DECLARE @hdrid INT
DECLARE @gethdrid CURSOR
SET @gethdrid = CURSOR FOR -- ITERATE BY HDRIDS
SELECT hdrid
FROM #testhdrrecords
OPEN @gethdrid
FETCH NEXT
FROM @gethdrID INTO @hdrid
WHILE @@FETCH_STATUS = 0
BEGIN
-- ITERATE BY SEGMENTS
DECLARE @segID INT
DECLARE @getsegID CURSOR
SET @getsegID = CURSOR FOR
SELECT segid
FROM #testsegrecords
OPEN @getsegID
FETCH NEXT
FROM @getsegID INTO @segID
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @segID
if left((select segment from #testsegrecords where segid=@segid+1 and hdrid=@hdrid),3)=left((select segment from #testsegrecords where segid=@segid and hdrid=@hdrid),3) -- DOES THE FIRST 3 CHARACTERS IN THE RECORD BELOW ME MATCH MY FIRST 3 RECORDS (THIS IS A NON MATCHED PAIR)
and left((select segment from #testsegrecords where segid=@segid-1 and hdrid=@hdrid),3)<>'PA1' -- ONLY IF THE PREVIOUS RECORD ISNT A PA1 (THAT WOULD MEAN ITS A MATCHED PAIR)
begin
select * from #testsegrecords where segid in (@segid,@segid+1) -- SHOW ME THE COLUMNS FOR THAT MATCHING ROW
end
FETCH NEXT
FROM @getsegID INTO @segID
END
CLOSE @getsegID
DEALLOCATE @getsegID

FETCH NEXT
FROM @gethdrID INTO @hdrid
END
CLOSE @gethdrID
DEALLOCATE @gethdrID

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-23 : 18:19:41

Something like this?

WITH cteSource (SegID, HdrID, theTrick, rnMod, rnDiv)
AS (
	SELECT	SegID,
		HdrID,
		SUBSTRING(Segment, 3, 1) % 2 AS theTrick,
		ROW_NUMBER() OVER (PARTITION BY HdrID ORDER BY SegID) % 2 AS rnMod,
		(ROW_NUMBER() OVER (PARTITION BY HdrID ORDER BY SegID) - 1) / 2 AS rnDiv
	FROM	#TestSegRecords 
)
SELECT		x.SegID,
		x.HdrID,
		x.Segment,
		x.ActiveDate
FROM		(
			SELECT		MIN(SegID) AS minSeg,
					MAX(SegID) AS maxSeg,
					HdrID
			FROM		cteSource
			GROUP BY	HdrID,
					rnDiv
			HAVING		SUM(theTrick) <> SUM(rnMod)

		) AS w
INNER JOIN	#TestSegRecords AS x ON x.HdrID = w.HdrID
			AND x.SegID BETWEEN w.minSeg AND w.maxSeg;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

chrisnorris007
Starting Member

18 Posts

Posted - 2014-07-23 : 18:43:47

Thanks so much, swePeso. It is really close. However, it is still pulling non consecutive matches. It should only be those records that are consecutive that dont fit the PA1 then PA2 model.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2014-07-24 : 03:30:27

Then post all possible permutations of mismatched and matched pairs of rows, so that we have a chance to have a look at this.

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

chrisnorris007
Starting Member

18 Posts

Posted - 2014-07-24 : 10:18:02

Not matched
PA1
PA2

Matched
PA1
PA1

PA2
PA2

EVERY ROW should follow the pattern PA1 then PA2 then PA1 then PA2..if it doesnt fit this pattern I want to know so I can correct it.

chrisnorris007
Starting Member

18 Posts

Posted - 2014-08-01 : 11:45:53

Anyone?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources