Solved

SQL - conditional checking assistance

Posted on 2014-10-24
6
300 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 45

Accepted Solution

by:
Vitor Montalvão earned 350 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 32

Assisted Solution

by:Stefan Hoffmann
Stefan Hoffmann earned 50 total points
Comment Utility
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
Comment Utility
Thanks!
0
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video discusses moving either the default database or any database to a new volume.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now