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

find order by date range or order id

hi basically what i have is 3 text boxes. one for start date, one for end date and one for order id, i also have this bit of SQL

SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order]WHERE (([Date_ordered] >= @.Date_ordered OR @.Date_ordered IS NULL) AND ([Date_ordered] <= @.Date_ordered2 OR @.Date_ordered2 IS NULL OR (Order_ID=ISNULL(@.OrderID_ID,Order_ID) OR @.Order_ID IS NULL))">

but the problem is it does not seem to work! i am not an SQL guru but i cant figure it out, someone help me please!

Thanks

Jez

If you use SqlDataSource, you can add this to the SqlDataSource:CancelSelectOnNullParameter="false"

And change your SelectCommand to:

SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order] WHERE [Date_ordered] >= ISNULL(@.Date_ordered, Date_ordered) AND ([Date_ordered] <= ISNULL(@.Date_ordered2, Date_ordered) AND Order_ID=ISNULL(@.OrderID_ID,Order_ID)">

|||

hi thanks for the code, right near the end you put OrderID_ID instead of Order_ID, and missed one ) at the end, but thank you very much for your help!

Jez

|||

Thank you for catching the typos. Glad that you got the idea.

|||

hey i just wondered if i could pick your brains again quickly, i wanted to have the same statement, but also see if it would also take input from a querystring, so could i just do the same thing, put all the WHERE clause in brackets, put a "OR" then just put where order_ID = ? and in the select perameters just have where ? = querystringorderID ?

Jez

find Minimum in range thats not in Table?

Hi Folks,

any help appreciated on this problem:
I've got a Table with a comparable Datatype (inet on postgres).
The values in the table have a minimum and a maximum value.
Now, I've got to find the smallest value betweeen min. and max. that is NOT in the table.

Example:
Min=10, Max=20
Entries: 10, 11, 12, 14, 18
needed value: 13 (larger than Min., smallest value not in Table)
I dont have pure Numbers to deal with, so

I dont want to create an auxiliary table with all the possible values and do a SELECT ... WHERE NOT IN ... statement.

thanks in advance!could you give some examples of what "inet" values are?

kinda curious why you chose the oracle forum to post

http://dbforums.com/f81/ is the postresql forum

and what's wrong with an auxiliary table?

rudy|||Originally posted by r937
could you give some examples of what "inet" values are?

kinda curious why you chose the oracle forum to post

http://dbforums.com/f81/ is the postresql forum

and what's wrong with an auxiliary table?
rudy

Sorry, but my browser says 'SQL and PL/SQL' Forum; I'd say this is a SQL problem - not a postgres.

'inet' is an ip(v4)-address - this datatype is comparable but not incrementable.

I don't think it's necessary to create a table with some hundred continuous values that can be expressed by two borders; perhaps there is no other solution than an auxiliary table but that would be a pity.

thanks anyway, Z|||yes, you're right, there's a separate oracle forum, although this one should definitely have the "PL/SQL" taken off its name

if you want an sql solution and not a postgresql solution, the only ones i'm familiar with are:

-- NOT EXISTS
-- NOT IN
-- EXCEPT
-- OUTER JOIN with test for no match

each of these requires some way of specifying the set of things that aren't there

you don't actually have to have an auxiliary table, though

try joining the table to itself with a left outer join on a.inet = (b.inet - 1)

or something :rolleyes:

rudy|||Originally posted by r937
...
try joining the table to itself with a left outer join on a.inet = (b.inet - 1)

or something :rolleyes:

rudy

darn, you were right from the beginning: As the 'inet'-Type cant be incremented or decremented a pure SQL solution seems even more unapplicable now. I think I have to do some postgres-specific hack... :(

thanks,

zaphod|||yeah, but i didn't know i was right at the time!

meanwhile, i had a look at the postgresql docs, and found this (http://developer.postgresql.org/docs/pgsql/src/test/regress/expected/inet.out) page, which (a) freaks me out, but (b) offers encouragement that there might be custom functions available in postgresql for working with that datatype

rudy

Filtering values in ResultSet by setting range on complex index

Hi!
I have table with complex index on 5 fields. One of them is string filed. I want to implement some sort of filtering, by setting SetRange() in my SQLCeCommand. But i need to fileter only by one string field and to get the values starting with the input string value.
I tried to use such code:
...
command.SetRange(DbRangeOptions.Prefix, new object[] {null, null, null, "Com", null}, null);
resultSet = cmd.ExecuteResultSet(ResultSetOptions.Scrollable);
....
But it doesn't work. As a result i've got an empty result set.
Usage of simple index on one field and setting the correspondent range will solve problem, but i can't have such index due to project restrictions.
Is there any way to set prefix range only by one value of complex index? If not, please, explain me how does Prefix Range works.

Thanx

If filtering has the same behavior as seeking (which I am assuming - read: not tested) then you might want to consider putting the column with the non-null value as the first in the index. This will require that you recreate the index with the new column order.