SQL - conditional checking assistance

Hello experts,

Noob question here, I apologize up front.

I have a table that holds diagnosis codes in one of 10 columns, along with a uniqueidentifier for the whole record.  I have a desire to find any rows that may hold a code in any of the ten columns that starts with ''99%' AND I wish to be able to make sure that none of the columns in that row contain the value '93000'.  These are varchar(15) fields believe it or not, even though they appear to store numeric / int data.  SOme of the columns contain nulls if they do not have codes.

So the table looks something like:

[uniqueid]                                                           [1]          [2]           [3]          [4]      [5]         [6]          ...... [10]
22h34g-3jh434b-3464h34-rjr4kf-2434           99173   93000    99122     null    null       96332           null
22h34g-3jh434b-3464h34-rjr4kf-2434           99416   93000    99122     null    54555   99876           null
22h34g-3jh434b-3464h34-rjr4kf-2434           99651   93000    99122     null    null        95678           null
22h34g-3jh434b-3464h34-rjr4kf-2434           99665   93000    99122     null    67888    94211           null
22h34g-3jh434b-3464h34-rjr4kf-2434           98222   94567    99122     null    null        98334           null

My goal is to again, return the row that has a 99 in one of the columns, without a 93000, or this one from above:

[uniqueid]                                                           [1]          [2]           [3]          [4]      [5]         [6]          ...... [10]
22h34g-3jh434b-3464h34-rjr4kf-2434           98222   94567    99122     null    null        98334           null

Something like:

select * from table
where
(
[1] like '99%' or
[2] like '99%' or
[3] like '99%' or
[4] like '99%' or
[5] like '99%' or
[6] like '99%' or
[7] like '99%' or
[8] like '99%' or
[9] like '99%' or
[10] like '99%'
)

AND
(
[1] <> '93000' and
[2] <> '93000' and
[3] <> '93000' and
[4] <> '93000' and
[5] <> '93000' and
[6] <> '93000' and
[7] <> '93000' and
[8] <> '93000' and
[9] <> '93000' and
[10] <> '93000'
)

I keep getting all or none when I run this, I'm sure it's something dumb in the logic.

Thoughts?

Thanks!
robthomas09Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Your query looks good to me. You can try to reduce the code a little bit:
select * from table
 where 
 (
 [1] like '99%' or 
 [2] like '99%' or 
 [3] like '99%' or 
 [4] like '99%' or 
 [5] like '99%' or 
 [6] like '99%' or 
 [7] like '99%' or 
 [8] like '99%' or 
 [9] like '99%' or 
 [10] like '99%'
 )
 AND
'93000' NOT IN  ( [1],  [2], [3],  [4], [5], [6], [7], [8], [9], [10]) )

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Christopher GordonSenior Developer AnalystCommented:
Here is an approach using the unpivot command.  You can just drop this into SSMS to test.  Note:  the code prior to the common table expression is just me building test data.

declare @diagnosisTable table (
	myUniqueId uniqueidentifier
,	d1 varchar(15)
,	d2 varchar(15)
,	d3 varchar(15)
,	d4 varchar(15)
,	d5 varchar(15)
,	d6 varchar(15)
,	d7 varchar(15)
,	d8 varchar(15)
,	d9 varchar(15)
,	d10 varchar(15)
)

insert into @diagnosisTable values
(newid(), '99173',   '93000',    '99122',     null,    null,       '96332',        null, null, null, null),
(newid(), '99173', '93000',    '99122',     null,    null,       '96332',           null, null, null, null),

(newid(), '99416', '93000',    '99122',     null,    '54555',   '99876',           null, null, null, null),
(newid(), '99651',   '93000',   '99122',     null,    null,        '95678',           null, null, null, null),
(newid(), '99665',   '93000',    '99122',     null,    '67888',    '94211',           null, null, null, null),
(newid(), '98222',   '94567',    '99122',     null,    null,        '98334',           null, null, null, null)

;with diagnosis_detail as
(
	select	myUniqueId, diagnosis, left(diagnosis,2) as diagnosisCategory
	from
	(	
		select	myUniqueId, diagnosis
		from	@diagnosisTable
		unpivot (diagnosis for myUnqiueId in (d1, d2, d3, d4, d5, d6, d7, d8, d9, d10)) as unpvt
	) diagnosis
)

select	*
from	diagnosis_detail
where	myUniqueId not in
(
	select	myUniqueId
	from	diagnosis_detail
	where	diagnosisCategory ='93'
)

and diagnosisCategory = '99'

Open in new window

0
Christopher GordonSenior Developer AnalystCommented:
Just realized that the final SELECT in my previous post should look more like this (if you want to go this route):

select	*
from	@diagnosisTable
where	myUniqueId in 
(
	select	myUniqueId
	from	diagnosis_detail
	where	myUniqueId not in
	(
		select	myUniqueId
		from	diagnosis_detail
		where	diagnosisCategory ='93'
	)

	and diagnosisCategory = '99'
)

Open in new window

0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

ste5anSenior DeveloperCommented:
Seems like it is why-should-I-care-for-normalization day...

DECLARE @Codes TABLE
    (
      [uniqueid] VARCHAR(255) ,
      [1] INT ,
      [2] INT ,
      [3] INT ,
      [4] INT ,
      [5] INT ,
      [6] INT ,
      [10] INT
    );

INSERT  INTO @Codes
VALUES  ( '22h34g-3jh434b-3464h34-rjr4kf-2434', 99173, 93000, 99122, 99, NULL, 96332, NULL ),
        ( '22h34g-3jh434b-3464h34-rjr4kf-2434', 99416, 93000, 99122, NULL, 54555, 99876, NULL ),
        ( '22h34g-3jh434b-3464h34-rjr4kf-2434', 99651, 93000, 99122, NULL, NULL, 95678, NULL ),
        ( '22h34g-3jh434b-3464h34-rjr4kf-2434', 99665, 93000, 99122, NULL, 67888, 94211, NULL ),
        ( '22h34g-3jh434b-3464h34-rjr4kf-2434', 98222, 94567, 99122, 99, NULL, 98334, NULL );

WITH    Ordered
          AS ( SELECT   C.* ,
                        ROW_NUMBER() OVER ( PARTITION BY uniqueid ORDER BY ( SELECT 1
                                                                           ) ) RN
               FROM     @Codes C
             ),
        Normalized
          AS ( SELECT   *
               FROM     Ordered O UNPIVOT ( Value FOR Code IN ( [1], [2], [3], [4], [5], [6], [10] ) ) U
             ),
        Matches
          AS ( SELECT  DISTINCT
                        N.uniqueid ,
                        N.RN
               FROM     Normalized N
               WHERE    N.Value = 99
               EXCEPT
               SELECT  DISTINCT
                        N.uniqueid ,
                        N.RN
               FROM     Normalized N
               WHERE    N.Value = 93000
             )
    SELECT  O.*	
    FROM    Ordered O
            INNER JOIN Matches M ON M.RN = O.RN
                                    AND M.uniqueid = O.uniqueid; 

Open in new window


p.s. your data is neither normalized, cause your columns [1]-[10] are a repeating group, nor does your table contains a meaningful candidate key.
0
robthomas09Author Commented:
Thanks!
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Just now I could made some tests and realized that the problem is the columns with NULL values. So you need to avoid that by doing this:
select * 
from Table
where 
 (
[1] like '99%' or 
 [2] like '99%' or 
 [3] like '99%' or 
 [4] like '99%' or 
 [5] like '99%' or 
 [6] like '99%' or 
 [7] like '99%' or 
 [8] like '99%' or 
 [9] like '99%' or 
 [10] like '99%'
)
 AND
 (
 ISNULL([1], '') <> '93000' and 
 ISNULL([2], '') <> '93000' and 
 ISNULL([3], '') <> '93000' and 
 ISNULL([4], '') <> '93000' and 
 ISNULL([5], '') <> '93000' and 
 ISNULL([6], '') <> '93000' and 
 ISNULL([7], '') <> '93000' and 
 ISNULL([8], '') <> '93000' and 
 ISNULL([9], '') <> '93000' and 
 ISNULL([10], '') <> '93000'
 )

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.