CODEWITHSUNDEEP
sql-server-2005
Shift
Shift

Reputation: 642

SQL Server query based on record value

I have the following data structure:

P_ID    EVSSMIN EVSSMAX TDMIN   TDMAX   WARD    SYRINGE_ID   COMBINE_WITH   CUSTOM_CONCENTRATION
1003    0   20  125 250 2ZKG    1021038
    44444444    5
1003    20  60  125 250 2ZKG    1021037
    44444444    5

Normally I am using the following query for result.

[QUERY 1]

SELECT * 
FROM [definities] 
WHERE 160 BETWEEN [TDMIN] AND [TDMAX] AND [WARD] = '2ZKG' AND [P_ID]=` 1003;

The last column in the table can contain a value higher then 0. Above it's 5.

Normally I want to run the query below instead of the query above.

[QUERY 2]

SELECT * FROM [definities] 
WHERE 160 BETWEEN [TDMIN] AND [TDMAX] 
AND CAST(160 as decimal) / [CUSTOM_CONCENTRATION]) BETWEEN EVSSMIN AND EVSMAX 
AND [WARD] = '2ZKG' -- AND [P_ID]= 1003;

As you can see the query is calculating the value 160 by dividing it with the [CUSTOM_CONCENTRATION]) value. Than it matches between the EVVSMIN and EVSSMAX and also the value 160 is matched between the TDMIN and TDMAX. Normally when the [CUSTOM_CONCENTRATION] column contains a value higer then 0 the query 2 will work properly.

The problem is when the [CUSTOM_CONCENTRATION] column doesn't contain a value higher then 0 then the query can't return a record. This means that the query must be altered or something based on the [CUSTOM_CONCENTRATION] value. I can solve this within the C# code but the neat way is to able to do this as a query that returns me the exact record based on the values and not multiple records that I need to iterate and check it with if statements.

Upvotes: 1

Views: 87

Answers (2)

aF.
aF.

Reputation: 66727

Assuming you want the first query when the [CUSTOM_CONCENTRATION] value is zero or lower and the second otherwise, you can use a UNION ALL with another restriction on both queries:

SELECT * FROM [definities] 
WHERE 160 BETWEEN [TDMIN] AND [TDMAX] AND [WARD] = '2ZKG' AND [P_ID]=` 1003
AND [CUSTOM_CONCENTRATION] <= 0

UNION ALL

SELECT * FROM [definities] 
WHERE 160 BETWEEN [TDMIN] AND [TDMAX] 
AND CAST(160 as decimal) / [CUSTOM_CONCENTRATION]) BETWEEN EVSSMIN AND EVSMAX 
AND [WARD] = '2ZKG' -- AND [P_ID]= 1003
AND [CUSTOM_CONCENTRATION] > 0;

Another solution, and a more elegant one is:

SELECT * FROM [definities] 
WHERE 160 BETWEEN [TDMIN] AND [TDMAX] AND [WARD] = '2ZKG'
AND (([P_ID]=` 1003
      AND [CUSTOM_CONCENTRATION] <= 0)
     OR
     (CAST(160 as decimal) / [CUSTOM_CONCENTRATION]) BETWEEN EVSSMIN AND EVSMAX
      AND AND [CUSTOM_CONCENTRATION] > 0));

Upvotes: 1

vasja
vasja

Reputation: 4792

Maybe something like this:

SELECT * FROM [definities] WHERE 160 BETWEEN [TDMIN] AND [TDMAX] AND
 ( case when [CUSTOM_CONCENTRATION] = 0 then EVSSMIN 
         else CAST(160 as decimal) / [CUSTOM_CONCENTRATION]) end
            BETWEEN EVSSMIN AND EVSMAX )
         AND [WARD] = '2ZKG'

Upvotes: 1

Related Questions