How to check for a series of equal X paired rows in a row related to a specific column?

Question

I am using SQL Server 2008 R2.

Consider this @t table (TOP 20 ORDER BY PK DESC):

PK SK VC APP MC == == == ==== == ================== 21 7 79 NULL 0 NULL 20 9 74 1 3 20=14, 18=13, 15=2 19 6 79 1 2 19=11, 17=7 18 9 77 1 0 NULL 17 6 74 1 0 NULL 16 7 79 1 0 NULL 15 9 74 1 0 NULL 14 9 74 1 0 NULL 13 9 77 1 0 NULL 12 7 77 1 0 NULL 11 6 79 1 0 NULL 10 7 79 1 0 NULL 9 7 74 1 0 NULL 8 7 79 1 0 NULL 7 6 74 1 0 NULL 6 6 74 1 0 NULL 5 7 79 1 0 NULL 4 7 77 1 0 NULL 3 6 79 1 0 NULL 2 9 74 1 0 NULL 

Created using this:

 DECLARE @t TABLE(PK INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, SK INT NOT NULL, VC INT NULL, APP INT NULL, M INT NOT NULL, C NVARCHAR(111) NULL); INSERT @t (SK,VC,APP,M,C) VALUES (7,77,1,0,NULL), (9,74,1,0,NULL), (6,79,1,0,NULL), (7,77,1,0,NULL), (7,79,1,0,NULL), (6,74,1,0,NULL), (6,74,1,0,NULL), (7,79,1,0,NULL), (7,74,1,0,NULL), (7,79,1,0,NULL), (6,79,1,0,NULL), (7,77,1,0,NULL), (9,77,1,0,NULL), (9,74,1,0,NULL), (9,74,1,0,NULL), (7,79,1,0,NULL), (6,74,1,0,NULL), (9,77,1,0,NULL), (6,79,1,2,'19=11, 17=7'), (9,74,1,3,'20=14, 18=13, 15=2'), (7,79,NULL,0,NULL) 

My task is to return true to match if the last line ( where APP IS NOT NULL ) terminates a series of match pairs X or the last lines of the same group (same current SK).

For example, when testing for only two pairs, given the current test required, for SK = 6, as soon as PK = 19 is reached, there is a match.

Correspondence VC (19) = VC (11) = 79 AND VC (17) = VC (7) = 74

Take a look by doing the following:

 DECLARE @PairsToTest int = 2 DECLARE @SK int = 6 SELECT TOP (2*@PairsToTest) * FROM @t WHERE APP IS NOT NULL AND SK = @SK ORDER BY SK, PK DESC 

results:

 PK SK VC APP MC 19 6 79 1 2 19=11, 17=7 17 6 74 1 0 NULL 11 6 79 1 0 NULL 7 6 74 1 0 NULL 

Another example:

When testing for 3 pairs, the match is at PK = 20 when viewed in SK = 9 (Although this is an interesting question in itself, for my task there is no need to check all SKs. For me, the result for this SK is enough.

To see a match, do the following:

 DECLARE @PairsToTest int = 3 DECLARE @SK int = 9 SELECT TOP (2*@PairsToTest) * FROM @t WHERE APP IS NOT NULL AND SK = @SK ORDER BY SK, PK DESC 

which leads to:

 PK SK VC APP MC 20 9 74 1 3 20=14, 18=13, 15=2 18 9 77 1 0 NULL 15 9 74 1 0 NULL 14 9 74 1 0 NULL 13 9 77 1 0 NULL 2 9 74 1 0 NULL 

as you can see: VC (20) = VC (14) = 74, VC (18) = VC (13) = 74 and VC (15) = VC (2)

I was thinking about choosing the required rowset in the correct order and counting equal rows in VC. If the counter matches the @PairsToTest symbol, this is a sign to raise the flag.

I tried:

 DECLARE @PairsToTest int = 3 DECLARE @SK int = 9 ;with t0 as ( SELECT TOP (2*@PairsToTest) * FROM @t WHERE APP IS NOT NULL AND SK = @SK ORDER BY SK, PK DESC ), t1 AS ( SELECT TOP (@PairsToTest) * FROM t0 ), t2 AS ( SELECT TOP (@PairsToTest) * FROM t0 ORDER BY PK ASC ) ,t3 AS ( SELECT TOP 99999999 * FROM t2 ORDER BY PK DESC ) IF (SELECT COUNT(*) FROM t1 LEFT OUTER JOIN t3 ON t1.VC = t3.VC) = @PairsToTest SELECT 1 ELSE SELECT 0 

but there may be disadvantages:

• VC does not contain unique data (only randomly)
• IF not allowed
• I have to get rid of TOP 99999999 in t3 (although I can live with it)

What changes do I need to take to solve this problem?

Answer 1

 DECLARE @t TABLE(PK INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, SK INT NOT NULL, VC INT NULL, APP INT NULL, M INT NOT NULL, C NVARCHAR(111) NULL); INSERT @t (SK,VC,APP,M,C) VALUES (7,77,1,0,NULL), (9,74,1,0,NULL), (6,79,1,0,NULL), (7,77,1,0,NULL), (7,79,1,0,NULL), (6,74,1,0,NULL), (6,74,1,0,NULL), (7,79,1,0,NULL), (7,74,1,0,NULL), (7,79,1,0,NULL), (6,79,1,0,NULL), (7,77,1,0,NULL), (9,77,1,0,NULL), (9,74,1,0,NULL), (9,74,1,0,NULL), (7,79,1,0,NULL), (6,74,1,0,NULL), (9,77,1,0,NULL), (6,79,1,2,'19=11, 17=7'), (9,74,1,3,'20=14, 18=13, 15=2'), (7,79,NULL,0,NULL) DECLARE @PairsToTest int = 3 DECLARE @SK int = 9 IF ((SELECT COUNT(*) FROM @t WHERE APP IS NOT NULL AND SK = @SK) -@PairsToTest ) >=0 BEGIN DECLARE @swapData TABLE(PK1 INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, PK INT NOT NULL, SK INT NOT NULL, VC INT NULL, APP INT NULL, M INT NOT NULL, C NVARCHAR(111) NULL); DECLARE @olderData TABLE(PK2 INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, PK INT NOT NULL, SK INT NOT NULL, VC2 INT NULL, APP INT NULL, M INT NOT NULL, C NVARCHAR(111) NULL); DECLARE @newerData TABLE(PK3 INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, PK INT NOT NULL, SK INT NOT NULL, VC3 INT NULL, APP INT NULL, M INT NOT NULL, C NVARCHAR(111) NULL); INSERT @swapData SELECT TOP ((SELECT COUNT(*) FROM @t WHERE APP IS NOT NULL AND SK = @SK) -@PairsToTest ) * FROM @t WHERE APP IS NOT NULL AND SK = @SK ORDER BY PK INSERT @olderData SELECT TOP (@PairsToTest) PK,SK,VC,APP,M,C FROM @swapData ORDER BY PK1 DESC INSERT @newerData SELECT TOP (@PairsToTest) * FROM @t WHERE APP IS NOT NULL AND SK = @SK ORDER BY SK, PK DESC DECLARE @Matches int = (SELECT COUNT(*)FROM @newerData INNER JOIN @olderData ON PK2 = PK3 WHERE VC2=VC3) IF @Matches = @PairsToTest SELECT 1 AS Match ELSE SELECT 0 AS Match END ELSE SELECT 0 AS Match /* SELECT TOP (2*@PairsToTest) * FROM @t WHERE APP IS NOT NULL AND SK = @SK ORDER BY SK, PK DESC SELECT * FROM @olderData SELECT * FROM @newerData */ 

Answer 2

Try this code, it counts the number of pairs of rows in each of the SK sections and excludes rows without a pair from the result:

 DECLARE @t TABLE(PK INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, SK INT NOT NULL, VC INT NULL, APP INT NULL, M INT NOT NULL, C NVARCHAR(111) NULL); INSERT @t (SK,VC,APP,M,C) VALUES (7,77,1,0,NULL), (9,74,1,0,NULL), (6,79,1,0,NULL), (7,77,1,0,NULL), (7,79,1,0,NULL), (6,74,1,0,NULL), (6,74,1,0,NULL), (7,79,1,0,NULL), (7,79,1,0,NULL), (6,79,1,0,NULL), (7,77,1,0,NULL), (9,77,1,0,NULL), (9,74,1,0,NULL), (9,74,1,0,NULL), (7,79,1,0,NULL), (6,74,1,0,NULL), (9,77,1,0,NULL), (6,79,1,2,'19=11, 17=7'), (9,74,1,3,'20=14, 18=13, 15=2'), (7,79,NULL,0,NULL) ;WITH c AS ( SELECT *, DENSE_RANK() OVER (PARTITION BY SK ORDER BY VC DESC) DenseRankPartitionBySK, ROW_NUMBER() OVER (PARTITION BY SK ORDER BY PK DESC) ordinalNumberInSKPartition FROM @t WHERE APP IS NOT NULL ), e AS ( SELECT *, COUNT(*) OVER (PARTITION BY SK, DenseRankPartitionBySK) _Sum, ROW_NUMBER() OVER (PARTITION BY SK, DenseRankPartitionBySK ORDER BY PK) Odd FROM c ), d AS ( SELECT *, COUNT(*) OVER (PARTITION BY SK) numberOfRows FROM e WHERE _Sum % 2 = 0 OR Odd <> 1 ) SELECT d.PK, d.SK, d.VC, d.APP, dM, dC, CASE WHEN ordinalNumberInSKPartition = 1 THEN 1 ELSE 0 END IsTopRow, numberOfRows / 2 [NumberOfPairsInSKPartition(M)] FROM d ORDER BY SK, PK DESC