Conditional field compare based on the value of another field (Case statement?)
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: