Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL query eliminating rows problem


SQL query eliminating rows problem

Author
Message
Dominic_godfrey
Dominic_godfrey
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 126
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
bala.a
bala.a
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
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



Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Dominic_godfrey
Dominic_godfrey
Grasshopper
Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)Grasshopper (10 reputation)

Group: General Forum Members
Points: 10 Visits: 126
Hi thanks for the replies I shall try them out when I get to work on Monday, they look good though thanks!!!!
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24177 Visits: 37948
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.

Cool
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)
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24177 Visits: 37948
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.

Cool
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)
Paul White
Paul White
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10338 Visits: 11350
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
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search