Finding a value within a range using IN

Patients may be assigned up to 4 diagnoses for an encounter. I need to selec
t
patients (by their unique ID) when any one of the 4 diagnoses falls within a
range. So I wrote the SELECT as below. Now I am curious if there is a more
concise syntax using IN. Here's what I did:
SELECT PatUniqueID FROM Charges WHERE
diag_code1 BETWEEN '300.00' AND '399.99' OR
diag_code2 BETWEEN '300.00' AND '399.99' OR
diag_code3 BETWEEN '300.00' AND '399.99' OR
diag_code4 BETWEEN '300.00' AND '399.99' ;
Thanks...The problem is that the design of table Changes is not normalized and SQL
statements are more concise when used with normalized data models. For
example, the following would be normalized:
PatUniqueID
VisitDate
diag_code
ChargeAmt
If the clinic decides that a patient may be assigned up to 5 diagnoses,
would this involve a re-write of the application?
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:123AEEEE-91FE-42AD-8047-793FC3A6D797@.microsoft.com...
> Patients may be assigned up to 4 diagnoses for an encounter. I need to
> select
> patients (by their unique ID) when any one of the 4 diagnoses falls within
> a
> range. So I wrote the SELECT as below. Now I am curious if there is a more
> concise syntax using IN. Here's what I did:
> SELECT PatUniqueID FROM Charges WHERE
> diag_code1 BETWEEN '300.00' AND '399.99' OR
> diag_code2 BETWEEN '300.00' AND '399.99' OR
> diag_code3 BETWEEN '300.00' AND '399.99' OR
> diag_code4 BETWEEN '300.00' AND '399.99' ;
> Thanks...
>|||JT wrote:

> If the clinic decides that a patient may be assigned up to 5
> diagnoses, would this involve a re-write of the application?
Indeed, a good rule:
If the customer even thinks there might be more than one, make it an
unlimited. It'll save you lots of trouble afterwards.
Kind regards,
Stijn Verrept.|||JT, I like that I often learn things from my posts that are not what I
thought I was asking about. In standard medical billing only up to 4
diagnoses are allowed (adding more is unlikely) and they must be rank
ordered. The primary diagnosis goes to a specific place in a claim, secondar
y
to another place, etc. In addition, the reference list of diagnoses changes.
A 2005 diagnosis might be absent from the 2006 reference list. Unless the dx
is actually written to the claim, obsolete references would have to be
maintained forever. Given all that would you still normalize the diagnoses?
"JT" wrote:

> The problem is that the design of table Changes is not normalized and SQL
> statements are more concise when used with normalized data models. For
> example, the following would be normalized:
> PatUniqueID
> VisitDate
> diag_code
> ChargeAmt
> If the clinic decides that a patient may be assigned up to 5 diagnoses,
> would this involve a re-write of the application?
>
> "richardb" <richardb@.discussions.microsoft.com> wrote in message
> news:123AEEEE-91FE-42AD-8047-793FC3A6D797@.microsoft.com...
>
>|||>> In standard medical billing only up to 4 diagnoses are allowed (adding mo
re is unlikely) and they must be rank ordered. <<
Modifying the DDL you never posted, try something like this:
CREATE TABLE MedForms
(patient_id CHAR(20) NOT NULL,
diag_date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
icd_1 CHAR(7) NOT NULL CHECK (icd_1 LIKE
'[0-9][0-9][0-9].[0-9][0-9][0-9]'),
icd_2 CHAR(7) CHECK (icd_1 LIKE '[0-9][0-9][0-9].[0-9][0-9][0-9]'),
icd_3 CHAR(7) CHECK (icd_1 LIKE '[0-9][0-9][0-9].[0-9][0-9][0-9]'),
icd_4 CHAR(7) CHECK (icd_1 LIKE '[0-9][0-9][0-9].[0-9][0-9][0-9]'),
. );
I assume that you are using the ICD codes. You might also want to add
a REFERENCES clause to a table.
The primary diagnosis goes to a specific place in a claim, secondary
to another place, etc. In addition, the reference list of diagnoses
changes.
A 2005 diagnosis might be absent from the 2006 reference list. Unless
the dx
is actually written to the claim, obsolete references would have to be
maintained forever. Given all that would you still normalize the
diagnoses?|||Yes, I would still normalize the diagnosis codes in a seperate table, but I
come from a data warehousing background where retaining historical,
versioned, and even obsolete data is one of the primary requirements of the
database. The Charges table could have a column indicating it's rank.
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:FC270CD0-F38E-4E3C-ADD1-B0931A692FA5@.microsoft.com...
> JT, I like that I often learn things from my posts that are not what I
> thought I was asking about. In standard medical billing only up to 4
> diagnoses are allowed (adding more is unlikely) and they must be rank
> ordered. The primary diagnosis goes to a specific place in a claim,
> secondary
> to another place, etc. In addition, the reference list of diagnoses
> changes.
> A 2005 diagnosis might be absent from the 2006 reference list. Unless the
> dx
> is actually written to the claim, obsolete references would have to be
> maintained forever. Given all that would you still normalize the
> diagnoses?
> "JT" wrote:
>|||I work in a hospital and the dx codes are ordered in a separate table.
However, the issue with your query is that your where clause looks like
you are filtering a numeric field when you actually have a varchar
datatype. Between 300.00 and 399.99 is 33.0. I use something like the
following:
SELECT PatUniqueID FROM Charges WHERE
case when isnumeric(diag_code1) = 1 then
cast(isnumeric(diag_code1) as numeric) else 10000000 end BETWEEN 300.00
AND 399.99 OR
case when isnumeric(diag_code2) = 1 then
cast(isnumeric(diag_code1) as numeric) else 10000000 end BETWEEN 300.00
AND 399.99 OR
case when isnumeric(diag_code3) = 1 then
cast(isnumeric(diag_code1) as numeric) else 10000000 end BETWEEN 300.00
AND 399.99 OR
case when isnumeric(diag_code4) = 1 then
cast(isnumeric(diag_code1) as numeric) else 10000000 end BETWEEN 300.00
AND 399.99 .
Then case statement is necessary because some dx codes contain alpha
characters.
Alternatively, you could create a view of the table that contains the
the numeric dx code as well as the varchar code. I don't like this
option as much because you have to translate the columns even when they
are not needed.sql