Solved

SQL - conditional checking assistance

Posted on 2014-10-24
6
304 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
6 Comments
 
LVL 47

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 33

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 47

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

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