September 22, 2012 at 2:44 am
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
September 22, 2012 at 4:28 am
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
September 22, 2012 at 5:31 am
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
SQLkiwi blog
@SQL_Kiwi
September 22, 2012 at 11:33 am
Hi thanks for the replies I shall try them out when I get to work on Monday, they look good though thanks!!!!
September 22, 2012 at 10:11 pm
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
SQLkiwi blog
@SQL_Kiwi
September 23, 2012 at 9:58 am
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.
September 23, 2012 at 10:06 am
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
SQLkiwi blog
@SQL_Kiwi
September 23, 2012 at 10:15 am
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.
September 23, 2012 at 10:42 am
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
SQLkiwi blog
@SQL_Kiwi
September 23, 2012 at 12:13 pm
SQL Kiwi (9/23/2012)
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 🙂
Not trying to go off-topic, just trying to get Mr. Celko to at least test/check his code before he posts it. As I said, I sometimes post erronous code, but I try to fix it. He should do the same.
September 24, 2012 at 12:12 am
Here's another way using (mostly) Paul's set up data:
-- Demo table structure
DECLARE @Example
AS TABLE
( ClaimKey integer,
SequenceNumber tinyint,
SomeFlag CHAR(1));
-- Sample data
INSERT @Example (ClaimKey, SequenceNumber, SomeFlag)
VALUES
(1002343, CONVERT(tinyint, 1), 'Y'),
(1002343, CONVERT(tinyint, 2), 'N'),
(1002343, CONVERT(tinyint, 3), 'N'),
(1002343, CONVERT(tinyint, 4), 'Y'),
(1008976, CONVERT(tinyint, 1), 'Y'),
(1008976, CONVERT(tinyint, 2), 'Y'),
(1008976, CONVERT(tinyint, 3), 'Y'),
(1008976, CONVERT(tinyint, 4), 'N');
SELECT a.ClaimKey, FlagBefore=a.SomeFlag, FlagAfter=b.SomeFlag
FROM @Example a
CROSS APPLY (
SELECT b.ClaimKey, b.SomeFlag
FROM @Example b
WHERE a.ClaimKey = b.ClaimKey AND a.SequenceNumber - 1 = b.SequenceNumber AND
a.SomeFlag <> b.SomeFlag) b
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
September 24, 2012 at 3:14 pm
Hi all, thanks again for the replies it worked great, plus it was interesting to see the different ways to approach it. I used Paul's in the end and modified it to add other constraints.
I'm not exactly sure what CELCO was on about but I will read the "netiquette" before posting again and look up what DDL is.
Cheers guys
September 24, 2012 at 10:50 pm
Dominic_godfrey (9/24/2012)
I'm not exactly sure what CELCO was on about but I will read the "netiquette" before posting again and look up what DDL is.
It's hard to know for sure, but I think he was referring to the idea that it is easier for other people to get involved if the original question includes CREATE TABLE and INSERT statements to describe the problem and provide sample data. It is very helpful, though I am not sure I would call it netiquette. I have noticed that questions get better answers faster if the problem is expressed using data-definition language (DDL) like CREATE TABLE and INSERT.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 25, 2012 at 1:31 am
Ah right I see!! Thanks will do next time
September 25, 2012 at 1:46 am
Dominic_godfrey (9/24/2012)
Hi all, thanks again for the replies it worked great, plus it was interesting to see the different ways to approach it. I used Paul's in the end and modified it to add other constraints.
Just notice, be sure that you do always have a breaking condition (i.e. ThisRow.SomeFlag <> Previous.SomeFlag) for every squence, or this sequence may be lost.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply