SQL query eliminating rows problem

SELECT A.ClaimKey,
CASE WHEN A.MinClaim > B.MinClaim THEN 'Y' ELSE 'N' END AS YesOrNoBefore,
CASE WHEN A.MinClaim > B.MinClaim THEN 'N' ELSE 'Y' END AS YesOrNoAfter
FROM
(
SELECT ClaimKey, MIN(ClaimSequenceNumber) MinClaim
FROM TESTTABLE
WHERE YesOrNo='N'
GROUP BY ClaimKey
)A
,
(
SELECT ClaimKey, MIN(ClaimSequenceNumber) MinClaim
FROM TESTTABLE
WHERE YesOrNo='Y'
GROUP BY ClaimKey
)B
WHERE
A.ClaimKey = B.ClaimKey

-- Demo table structure
DECLARE @Example
AS TABLE
(
ClaimKey integer NOT NULL,
SequenceNumber tinyint NOT NULL,
SomeFlag bit NOT NULL,

PRIMARY KEY (ClaimKey, SequenceNumber)
);

-- Sample data
INSERT @Example
(ClaimKey, SequenceNumber, SomeFlag)
VALUES
(1002343, CONVERT(tinyint, 1), CONVERT(bit, 'true')),
(1002343, CONVERT(tinyint, 2), CONVERT(bit, 'false')),
(1002343, CONVERT(tinyint, 3), CONVERT(bit, 'false')),
(1008976, CONVERT(tinyint, 1), CONVERT(bit, 'true')),
(1008976, CONVERT(tinyint, 2), CONVERT(bit, 'true')),
(1008976, CONVERT(tinyint, 3), CONVERT(bit, 'true')),
(1008976, CONVERT(tinyint, 4), CONVERT(bit, 'false'));

-- Self-join
SELECT
ThisRow.ClaimKey,
ThisRow.SequenceNumber,
ThisFlag = ThisRow.SomeFlag,
PreviousFlag = Previous.SomeFlag
FROM @Example AS ThisRow
JOIN @Example AS Previous ON
Previous.ClaimKey = ThisRow.ClaimKey
AND Previous.SequenceNumber = ThisRow.SequenceNumber - CONVERT(tinyint, 1)
WHERE
ThisRow.SomeFlag <> Previous.SomeFlag

CELKO (9/22/2012)

I am trying to solve an issue and wondered if you could help, I'm relatively new to T-SQL

We also do not use bit flags; that was assembly language. What was changed? When was it changed? Where is the DDL? You might be new, but this is minimal Netiquette and it is posted at the front of the forum. The attribute property key tells us how something is used in this one place ; in data modeling, we name a thing for what is by its nature in all tables, in all context.

CREATE TABLE Claims
(claim_nbr CHAR(7) NOT NULL
CHECK (claim_nbr LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
claim_seq SMALLINT NOT NULL
CHECK (claim_seq > 0),
PRIMARY KEY (claim_nbr, claim_seq),
screwup_flg CHAR(1) NOT NULL
CHECK (screwup_flg IN ('Y','N'));

Look at the key and constraints; most of the work in SQL is done in the DDL.

WITH Claim_Deltas
AS
(SELECT claim_nbr, claim_seq, current_screwup_flg,
LAG(screwup_flg) OVER (PARTITION BY claim_nbr
ORDER BY claim_seq)
AS prior_screwup_flg
FROM Claims)

SELECT claim_nbr, current_screwup_flg, prior_screwup_flg
FROM Claim_Deltas
WHERE current_screwup_flg <> COALESCE (prior_screwup_flg, current_screwup_flg);

CREATE TABLE Claims
(claim_nbr CHAR(7) NOT NULL
CHECK (claim_nbr LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'),
claim_seq SMALLINT NOT NULL
CHECK (claim_seq > 0),
PRIMARY KEY (claim_nbr, claim_seq),
screwup_flg CHAR(1) NOT NULL
CHECK (screwup_flg IN ('Y','N'));