?
Solved

SQL - conditional checking assistance

Posted on 2014-10-24
6
Medium Priority
?
308 Views
Last Modified: 2014-10-24
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!
0
Comment
Question by:robthomas09
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 51

Accepted Solution

by:
Vitor Montalvão earned 1400 total points
ID: 40401940
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
 
LVL 14

Assisted Solution

by:Christopher Gordon
Christopher Gordon earned 400 total points
ID: 40401966
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
 
LVL 14

Expert Comment

by:Christopher Gordon
ID: 40401971
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
How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.

 
LVL 35

Assisted Solution

by:ste5an
ste5an earned 200 total points
ID: 40401981
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
 

Author Closing Comment

by:robthomas09
ID: 40401992
Thanks!
0
 
LVL 51

Expert Comment

by:Vitor Montalvão
ID: 40401995
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question