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);