Conditional field compare based on the value of another field (Case statement?) I have two tables


Alerts(iscomplete, threshold, isup, coinid)
Coins(coinid, LastPriceBtc)


Coins table is updated regularly with prices (going up n down).

I want to trigger alerts that meet the thresholds but I am having difficulty writing the correct query and making it efficient.

Basically what I would like to do is, if alert is not complete (!IsComplete) then if the IsUp field is 1 (up/true) then I want to check if the price of coin (Coins.LastPriceBtc) >= the threshold (Alerts.threshold), and if the IsUp field is 0 (down/false) then check to see if the Alerts.threshold &lt;= Coins.LastPriceBtc.

Here is what I got so far 

select  * 
from    alerts a 
inner 
join    coins c 
on      c.coinid = a.coinid
where   a.IsComplete &lt;&gt; 1
and     (   (a.IsUp = 1 and a.Threshold &gt;= c.LastPriceBtc) 
         or (a.IsUp = 0 and a.Threshold &gt;= c.LastPriceBtc)
        )


Is this the most efficient way to write this query?
I don't know what is that you are trying to do with this logic but the T-SQL query that you are asking for will most probably look like this :

DECLARE @IsComplete integer,@Isup integer,@LatestAlertid integer  
        SET @Isup = 1  
        SET @IsComplete = 1 
DECLARE @id INT
DECLARE @getcoinid  CURSOR

SET @getcoinid   = CURSOR FOR
SELECT coinid from Alerts 
OPEN @getcoinid  
FETCH NEXT
FROM @getcoinid  INTO @id
WHILE @@FETCH_STATUS = 0
BEGIN

    --where  IsComplete is not 1 and Isup is also not 1
        IF @Isup &lt;&gt; (SELECT Isup from Alerts
                     WHERE IsComplete &lt;&gt; @IsComplete 
    and coinid=@id) 
    --this query will execute  
            (SELECT *
            FROM Alerts as Alerts
            join  Coins as Coins 
            on Coins.coinid = Alerts.coinid and Alerts.threshold &lt;= Coins.LastPriceBtc
            WHERE Alerts.IsComplete &lt;&gt; @IsComplete and Alerts.Coinid =@id);  
        ELSE  
        --where IsComplete is not 1 but Isup is 1 
        --this query will execute  
            (SELECT *
            FROM Alerts as Alerts
            join  Coins as Coins 
            on Coins.coinid = Alerts.coinid and Alerts.threshold &gt;= Coins.LastPriceBtc
            WHERE Alerts.IsComplete &lt;&gt; @IsComplete and Alerts.Coinid =@id);
    FETCH NEXT
    FROM @getcoinid INTO @id
END

CLOSE @getcoinid 
DEALLOCATE @getcoinid 


Output:


Alerts for test:



Coins for test: