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?

Thanks in advance.

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)
1 Like
3

Thank you khtan.

Luigi

4

Just a little adding.
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
1 Like
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
use the latest version (i.e. with the highest version number).

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)
1 Like
9

Thanks again khtan :smile:
L.