Xrhrft

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 <= 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 <> 1

and     (   (a.IsUp = 1 and a.Threshold >= c.LastPriceBtc) 

         or (a.IsUp = 0 and a.Threshold >= 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 <> (SELECT Isup from Alerts

                     WHERE IsComplete <> @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 <= Coins.LastPriceBtc

            WHERE Alerts.IsComplete <> @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 >= Coins.LastPriceBtc

            WHERE Alerts.IsComplete <> @IsComplete and Alerts.Coinid =@id);

    FETCH NEXT

    FROM @getcoinid INTO @id

END



CLOSE @getcoinid 

DEALLOCATE @getcoinid 

Output:

Alerts for test:

Coins for test: