Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SQL query eliminating rows problem Expand / Collapse
Author
Message
Posted Saturday, September 22, 2012 2:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 3:38 AM
Points: 10, Visits: 83
Hi all!

I am trying to solve an issue and wondered if you could help, I'm relatively new to t-SQL and have the following table:

ClaimKey YesOrNo ClaimSequenceNumber
1002343. Y. 1
1002343. Y. 2
1002343. N. 3
1002343. N. 4
1008976. Y. 1
1008976. Y. 2
1008976. Y. 3
1008976. N. 4


I want to display only the rows that change from either a Y to a N or vice versa, in the format of:


ClaimKey. YesOrNoBefore. YesOrNoAfter.
1002343. Y. N
1008976 Y. N



I am unsure of the query to do this as whether I include an entry will depend on the previous row.


Please help!!

Thanks
Post #1363084
Posted Saturday, September 22, 2012 4:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, January 5, 2013 6:15 AM
Points: 15, Visits: 65
There are several ways to do acheive the same...
Here is the simple/basic query which strikes to my mind immediately

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


Post #1363092
Posted Saturday, September 22, 2012 5:31 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:07 PM
Points: 11,194, Visits: 11,109
It does depend a little on whether you want just the first change per claim key, or all changes. The following code demonstrates returning all changes using a self-join:

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





Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1363096
Posted Saturday, September 22, 2012 11:33 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: 2 days ago @ 3:38 AM
Points: 10, Visits: 83
Hi thanks for the replies I shall try them out when I get to work on Monday, they look good though thanks!!!!
Post #1363143
Posted Saturday, September 22, 2012 12:04 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 7:55 AM
Points: 1,945, Visits: 2,863
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);


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1363147
Posted Saturday, September 22, 2012 10:11 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:07 PM
Points: 11,194, Visits: 11,109
CELKO (9/22/2012)
LAG(screwup_flg) OVER (PARTITION BY claim_nbr ORDER BY claim_seq)

LAG is a good alternative solution for SQL Server 2012 (though not necessarily better performing).




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1363190
Posted Sunday, September 23, 2012 9:58 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:45 PM
Points: 23,081, Visits: 31,620
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);



Once again, you post code that won't even parse. Your CREATE TABLE isn't valid:

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


I would really like to be able to cut, paste, and execute your code without having to figure out what you did wrong first.




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1363239
Posted Sunday, September 23, 2012 10:06 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:07 PM
Points: 11,194, Visits: 11,109
Lynn Pettis (9/23/2012)
Once again, you post code that won't even parse. Your CREATE TABLE isn't valid:

All that is missing in a final close-parenthesis for the CREATE TABLE.




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1363242
Posted Sunday, September 23, 2012 10:15 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:45 PM
Points: 23,081, Visits: 31,620
SQL Kiwi (9/23/2012)
Lynn Pettis (9/23/2012)
Once again, you post code that won't even parse. Your CREATE TABLE isn't valid:

All that is missing in a final close-parenthesis for the CREATE TABLE.


True. The problem is that I haven't been able to directly use any of the code Mr. Celko has posted recently because it doesn't parse. He is supposed to be a SQL guru and he posts code like many of the OPs seeking help. I have posted code that had errors at times, and when I found out or was informed of the issue I have gone back to correct my code. Mr. Celko doesn't even do that.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1363245
Posted Sunday, September 23, 2012 10:42 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 6:07 PM
Points: 11,194, Visits: 11,109
Lynn Pettis (9/23/2012)
True. The problem is that I haven't been able to directly use any of the code Mr. Celko has posted recently because it doesn't parse. He is supposed to be a SQL guru and he posts code like many of the OPs seeking help. I have posted code that had errors at times, and when I found out or was informed of the issue I have gone back to correct my code. Mr. Celko doesn't even do that.

I think Joe has had some health problems recently (connected with this eyes I think). Perhaps that explains the odd typo or missing parenthesis, I don't know for sure. Anyway, I should stop now before this gets off topic




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1363248
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse