Solved

SQL - conditional checking assistance

Posted on 2014-10-24
6
306 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 50

Accepted Solution

by:
Vitor Montalvão earned 350 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 100 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
Independent Software Vendors: 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!

 
LVL 34

Assisted Solution

by:ste5an
ste5an earned 50 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 50

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

734 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