September 21, 2015 at 4:32 am
I'm trying to get a result set without the NULLs. Here is the code I'm using. I'd like the results to look like:
17285204 90471 090471
17285204 90715 090715
17285204 99396 099396
17285204 99420 099420
17285204 90471 090471
17285204 NULL G0444
Any thoughts?
create table #Test
(
AppNum varchar(10),
CPT varchar(10),
src char(1)
)
insert into #Test(AppNum, CPT, src) values('17285204','090471','b')
insert into #Test(AppNum, CPT, src) values('17285204','090715','b')
insert into #Test(AppNum, CPT, src) values('17285204','099396','b')
insert into #Test(AppNum, CPT, src) values('17285204','90471','a')
insert into #Test(AppNum, CPT, src) values('17285204','90715','a')
insert into #Test(AppNum, CPT, src) values('17285204','99396','a')
insert into #Test(AppNum, CPT, src) values('17285204','99420','a')
insert into #Test(AppNum, CPT, src) values('17285204','G0444','b')
select AppNum,
case when src='a' then CPT end as 'a',
case when src='b' then CPT end as 'b'
from #Test
September 21, 2015 at 4:44 am
How do I tell which row goes with which row? Is it always the case that the CPT for src of 'b' will be the CPT for the src of 'a' with a 0 prefixed?
Or will there be cases where other CPT values are expected to go together?
Edit: And where did this value come from? '17285204 99420 099420'
There's no row with a src of 'b' and a CPT of 099420
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 21, 2015 at 5:59 am
You might expect that '090471' is equal to '90471' because they have the same numeric value. However, they're different strings.
Here's a code that removes leading zeros to solve this problem. This isn't a good option, it's only a patch and you should be validating your data.
SELECT AppNum,
MAX( CASE WHEN src='a' THEN CPT END) AS 'a',
MAX( CASE WHEN src='b' THEN CPT END) AS 'b'
FROM #Test
GROUP BY AppNum,
STUFF( CPT, 1, PATINDEX( '%[^0]%', CPT), '') --Removes leading zeros
September 21, 2015 at 7:57 am
Unfortunately, miskeyd CPT codes are tough to deal with. Legitimate CPT codes can and do begin with leading 0's and removing them can cause them to change meaning.
Here's a bit more information... Current Procedural Terminology
IIRC the the max length of a CPT code (without modifiers) is 5, so that and the fact that a complete list of valid CPT codes should be available for download, should help with validation.
September 22, 2015 at 6:31 am
"Edit: And where did this value come from? '17285204 99420 099420'"
"There's no row with a src of 'b' and a CPT of 099420 "
Now that I'm looking at this, I understand that the dilemma is with the data. The codes should be the same in both sources. One of the sources is adding a leading zero in some cases. This is a system thing that needs to be fixed.
Sorry for wasting your time on this but, also, thanks for pointing this out.
September 22, 2015 at 6:38 am
I believe that the leading zero is correct and missing the zero is incorrect. That coming from the article shared by Jason.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply