Select and comparison across columns

Question

Select and comparison across columns

kwood111

Old Hand

Points: 380
More actions
April 13, 2009 at 5:16 pm

#132812

Hello all,
First off - full disclosure here - I am an application developer, not a DBA. Although I am asked at times to step in and do a little database programming. Up until this question, I thought I was getting pretty good at SQL and T-SQL. But on this, I am stumped.
I have a table with 6 columns, named (for example) 'Ecode1' through 'Ecode6'. These fields can be null, but if a value within a specific range is present in any one of the columns, then there must be a value in another specific range in at least one of the other columns. I need to find all records for which this is not the case. Here is the original wording of the request :
“In one or more of the E Code fields 1-6 there is one or more E codes reported in the range of E8800 - E8889, indicating injury by fall, but there is no corresponding E Code for place of occurrence (range E8490 - E8499).”
I did not create the table, or the request, but it is my job to fulfill it. I have tried to create some big, ugly query that goes through and checks a column to see if the value is in the first range, and if so, check the remaining 5 to see if there is a value in the second range. If not, check the value in the second column, see if it is in the first range, and if so, check the other 5 columns.... ad inifinitum (or so it seems).
Surely there must be an easier way to do this, but for lack of DBA experience, I am not sure what it is.
Any and all help or advice is greatly appreciated. Even if you can just give me a hint to get me on the right track, I would be very grateful. Thank you.
q-kev

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

sounds like nested case statements might be what you need, but it's help enourmously if we had some sample data and expected results;

are you trying to identify where there are gaps in the data, or inserting into the gap? (SELECT Vs INSERT)

in your example, how do you know what the range of values are for the additional columns?

for example, if ECODE1 had the value E8490 - E8499, how do you know what ECODE2 should be if it is null? E8500-E8509? the pattern to "fill" is critical to the answer, if we are trying to insert missing values.

SELECT ECODEID,

CASE

WHEN ECODE1 IS NOT NULL

THEN CASE

WHEN ECODE2 IS NOT NULL

THEN CASE

WHEN ECODE3 IS NOT NULL

THEN CASE

WHEN ECODE4 IS NOT NULL

THEN 'ECODE4 IS NOT NULL'

ELSE 'ECODE4 IS NULL'

END

ELSE 'E3 IS NULL'

END

ELSE 'E2 IS NULL'

END

ELSE 'E1 IS NULL'--E1 was null

END AS Results

FROM YOURTABLE

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

ghollis SSChasing Mays Points: 633 More actions · Answer 2

Try the Following:

Test code:

declare @t as Varchar(25) = 'E8800'

declare @s-2 as Varchar(25) = 'E8489'

select 'YES' where

(@t between 'E8800' and 'E8889' )

AND NOT (@S between 'E8490' and 'E8499')

Possible code:

SELECT *

FROM TableName

WHERE (Ecode1 between 'E8800' and 'E8889' OR

Ecode2 between 'E8800' and 'E8889' OR

Ecode3 between 'E8800' and 'E8889' OR

Ecode4 between 'E8800' and 'E8889' OR

Ecode5 between 'E8800' and 'E8889' OR

Ecode6 between 'E8800' and 'E8889')

AND NOT (Ecode1 between 'E8490' and 'E8499' OR

Ecode2 between 'E8490' and 'E8499' OR

Ecode3 between 'E8490' and 'E8499' OR

Ecode4 between 'E8490' and 'E8499' OR

Ecode5 between 'E8490' and 'E8499' OR

Ecode6 between 'E8490' and 'E8499')

kwood111 Old Hand Points: 380 More actions · Answer 3

Thanks for the answer. It is rather hard to explain exactly what the desired outcome is, but basically this is a medical record auditing process, and the data is coming in from a non-relational data system. There are 6 'Ecode' columns (Ecode1 thru Ecode6) that can hold either a fall injury code, OR a fall location code. If any one of the columns has a fall code - a value between E8800 and E8889, then at least one of the other columns from Ecode1 to Ecode6 must have a fall location code, which is a value between E8490 and E8499.

The design of this whole process, including table structure (or lack thereof), allowable data values, the data model itself - everything is pretty horrible in my opinion. Our company 'absorbed' another one that used Microsoft Access as their primary database product, and I have been tasked to run their auditing process one last time - as is - in Access. I have communicated to my boss that if we EVER need to do this again, the entire process needs to be revisited. He has assured me that we won't have to - let's pray he's right!

I appreciate the tips. It looks like there is no quick and dirty way to do it, and that's ok. At least I know now that I can proceed as I was with a little more confidence that I am not missing some obvious alternative : )

q-kev

P.S. Oh - I forgot to add - any or all of the columns can be null. The value ranges are static, they do not change, i.e. any code between E8800 represents a fall, and any code between E8490 and E8499 represents a location, regardless as to which column it is in. The purpose is merely to identify records where the criteria described is met - there is a fall code in one of the columns, but no location code in any of the other columns - and to select those records so they can be inserted into a temp table so that data can then be sent to the 'auditing authorities'.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

Does the table have an ID column?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kwood111 Old Hand Points: 380 More actions · Answer 5

ghollis - thank you, I think that just may work. I am going to give it a rip this morning and see what happens.

q-kev

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

kwood111 (4/14/2009)
ghollis - thank you, I think that just may work. I am going to give it a rip this morning and see what happens.
q-kev

Does the table have an ID column?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kwood111 Old Hand Points: 380 More actions · Answer 7

Chris,

As is, no - it does not. I have imported the data into SQL Server 2005 however, and can add an autonumber ID column. In fact, I am probably going to have to in order to identify the selected records. As I stated - this whole thing is horrible, and I just want to get this audit complete and be done with it. It would make a great example of all the things NOT to do in database design, and it has given me a new appreciation for relational databases!

q-kev

kwood111 Old Hand Points: 380 More actions · Answer 8

ghollis - the code you posted worked perfectly!

Thank you SOOOO much - I really appreciate it.

q-kev

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

A very simple solution is possible using UNPIVOT, but it relies upon an ID column...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ghollis SSChasing Mays Points: 633 More actions · Answer 10

kwood111 (4/14/2009)
ghollis - the code you posted worked perfectly!
Thank you SOOOO much - I really appreciate it.
q-kev

My pleasure, glad to hear it work for you.

Have a great day,

Gary

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Here's an unpivot method, for the record:

DROP TABLE #sample

--

CREATE TABLE #sample (id INT, Ecode1 VARCHAR(5), Ecode2 VARCHAR(5), Ecode3 VARCHAR(5), Ecode4 VARCHAR(5), Ecode5 VARCHAR(5))

--

INSERT INTO #sample (id, Ecode1, Ecode2, Ecode3, Ecode4, Ecode5)

SELECT 1, NULL, NULL, NULL, 'E8800', 'E8800' UNION ALL -- fall/fall

SELECT 2, NULL, NULL, NULL, 'E8800', 'E8490' UNION ALL -- fall/location

SELECT 3, NULL, NULL, NULL, 'E8889', 'E8499' UNION ALL -- fall/location

SELECT 4, NULL, NULL, NULL, 'E8490', 'E8499' UNION ALL -- location/location

SELECT 5, NULL, NULL, NULL, NULL, NULL

--

SELECT s.*

FROM #sample s

INNER JOIN (SELECT u.ID,

[Fall] = MAX(CASE WHEN u.Ecodes BETWEEN 'E8800' and 'E8889' THEN 1 END),

[Loc] = MAX(CASE WHEN u.Ecodes BETWEEN 'E8490' and 'E8499' THEN 1 END)

FROM (SELECT * FROM #sample) AS s

UNPIVOT (Ecodes FOR EcodeColumn IN (s.Ecode1, s.Ecode2, s.Ecode3, s.Ecode4, s.Ecode5) ) AS u

GROUP BY ID) d ON d.id = s.id

WHERE d.Fall > 0 AND d.Loc > 0

Results:

id Ecode1 Ecode2 Ecode3 Ecode4 Ecode5

----------- ------ ------ ------ ------ ------

2 NULL NULL NULL E8800 E8490

3 NULL NULL NULL E8889 E8499

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden