# Where with dynamic operator

#1

Hello,
I have a Select that perform checks between 2 numeric values
present in 2 different table.

Product
ProductId - Quantity
1 25
2 32

Threshold
ProductId Operator ThresholdValue
1 > 30
2 < 25

I have to select ProductId that has quantity over
the threshould but taking count of the operator.

So, if the operator is equal to >
the checks is

Quantity > ThresholdValue

and if the operator is <
the check is

Quantity < ThresholdValue

How can I accomplish this?

Luigi

#2
``````SELECT *
FROM    Product p
INNER JOIN Threshold t ON p.ProductId = t.ProductId
WHERE   (Operator = '>' AND p.Quantity > ThresholdValue)
OR      (Operator = '<' AND p.Quantity < ThresholdValue)``````

#3

Thank you khtan.

Luigi

#4

How can I insert a new column that tells me is there is something over the threashold?

For example:

ProductId - Quantity - IsOverThreashold

L.

#5
``````SELECT *,
IsOverThreashold = CASE
WHEN (
Operator = '>'
AND p.Quantity > ThresholdValue
)
OR (
Operator = '<'
AND p.Quantity < ThresholdValue
)
THEN 1
ELSE 0
END
FROM Product p
INNER JOIN Threshold t
ON p.ProductId = t.ProductId``````

#6

Great! You're great khtan.

Thanks a lot.

L.

#7

Another little addidng, sorry for that.

The user want in the Threshold table a "Version" field
for the ThresholdValue, so, in the matching, I have to

Threshold
ProductId Operator ThresholdValue Version
1 > 30 0
1 > 32 1
2 < 25 0
....
....

How can I change the SELECT?

SELECT *
FROM Product p
INNER JOIN Threshold t ON p.ProductId = t.ProductId
WHERE (Operator = '>' AND p.Quantity > ThresholdValue)
OR (Operator = '<' AND p.Quantity < ThresholdValue)

Thanks again a lot.

L.

#8
``````SELECT  *
FROM    Product p
CROSS APPLY
(
SELECT  TOP 1 *
FROM    Threshold x
WHERE    x.ProductId    = p.ProductId
ORDER BY Version DESC
) t
WHERE   (t.Operator = '>' AND p.Quantity > t.ThresholdValue)
OR      (t.Operator = '<' AND p.Quantity < t.ThresholdValue)``````

#9

Thanks again khtan
L.