Conditional field compare based on the value of another field (Case statement?)





.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}







0















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?










share|improve this question
















bumped to the homepage by Community 19 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
















  • Your code has >= in both cases.

    – ypercubeᵀᴹ
    Dec 23 '17 at 14:14











  • after looking into it, wouldnt I want the >= in both cases? because If i dont then every single "down" trend will get executed because the coin price will always be bigger than threshold of a going down trend before it even hits the actual threshold

    – Zoinky
    Dec 23 '17 at 20:06






  • 1





    whether you use >= or <= is going to depend on your desired logic; at the moment your textual description seems a bit off as I'd expect for IsUp=0 (down/false) that you want to check for the price being less than the threshold (ie, Coins.LastPriceBtc <= Alerts.threshold) ... ???

    – markp
    Dec 24 '17 at 1:19













  • I'm also wondering how you're going to handle repeated up (or down) moves in price where each move is greater (less) than the threshold ... isn't the current query going to generate an alert in every case (eg, threshold=100, the next 5 price changes are 110, 120, 135, 140, 150 => the query will generate 5 alerts?); or do you only want to generate an alert the first time the threshold is crossed (in a given direction) ... and if so ... how do you keep track of the 'last' time you crossed the threshold (in a given direction)? (I don't see how the current query would address this issue)

    – markp
    Dec 24 '17 at 1:26




















0















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?










share|improve this question
















bumped to the homepage by Community 19 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
















  • Your code has >= in both cases.

    – ypercubeᵀᴹ
    Dec 23 '17 at 14:14











  • after looking into it, wouldnt I want the >= in both cases? because If i dont then every single "down" trend will get executed because the coin price will always be bigger than threshold of a going down trend before it even hits the actual threshold

    – Zoinky
    Dec 23 '17 at 20:06






  • 1





    whether you use >= or <= is going to depend on your desired logic; at the moment your textual description seems a bit off as I'd expect for IsUp=0 (down/false) that you want to check for the price being less than the threshold (ie, Coins.LastPriceBtc <= Alerts.threshold) ... ???

    – markp
    Dec 24 '17 at 1:19













  • I'm also wondering how you're going to handle repeated up (or down) moves in price where each move is greater (less) than the threshold ... isn't the current query going to generate an alert in every case (eg, threshold=100, the next 5 price changes are 110, 120, 135, 140, 150 => the query will generate 5 alerts?); or do you only want to generate an alert the first time the threshold is crossed (in a given direction) ... and if so ... how do you keep track of the 'last' time you crossed the threshold (in a given direction)? (I don't see how the current query would address this issue)

    – markp
    Dec 24 '17 at 1:26
















0












0








0








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?










share|improve this question
















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?







sql-server t-sql sql-server-2017






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Dec 24 '17 at 12:52









Paul White

54.1k14287460




54.1k14287460










asked Dec 23 '17 at 5:09









ZoinkyZoinky

21319




21319





bumped to the homepage by Community 19 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.







bumped to the homepage by Community 19 mins ago


This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.















  • Your code has >= in both cases.

    – ypercubeᵀᴹ
    Dec 23 '17 at 14:14











  • after looking into it, wouldnt I want the >= in both cases? because If i dont then every single "down" trend will get executed because the coin price will always be bigger than threshold of a going down trend before it even hits the actual threshold

    – Zoinky
    Dec 23 '17 at 20:06






  • 1





    whether you use >= or <= is going to depend on your desired logic; at the moment your textual description seems a bit off as I'd expect for IsUp=0 (down/false) that you want to check for the price being less than the threshold (ie, Coins.LastPriceBtc <= Alerts.threshold) ... ???

    – markp
    Dec 24 '17 at 1:19













  • I'm also wondering how you're going to handle repeated up (or down) moves in price where each move is greater (less) than the threshold ... isn't the current query going to generate an alert in every case (eg, threshold=100, the next 5 price changes are 110, 120, 135, 140, 150 => the query will generate 5 alerts?); or do you only want to generate an alert the first time the threshold is crossed (in a given direction) ... and if so ... how do you keep track of the 'last' time you crossed the threshold (in a given direction)? (I don't see how the current query would address this issue)

    – markp
    Dec 24 '17 at 1:26





















  • Your code has >= in both cases.

    – ypercubeᵀᴹ
    Dec 23 '17 at 14:14











  • after looking into it, wouldnt I want the >= in both cases? because If i dont then every single "down" trend will get executed because the coin price will always be bigger than threshold of a going down trend before it even hits the actual threshold

    – Zoinky
    Dec 23 '17 at 20:06






  • 1





    whether you use >= or <= is going to depend on your desired logic; at the moment your textual description seems a bit off as I'd expect for IsUp=0 (down/false) that you want to check for the price being less than the threshold (ie, Coins.LastPriceBtc <= Alerts.threshold) ... ???

    – markp
    Dec 24 '17 at 1:19













  • I'm also wondering how you're going to handle repeated up (or down) moves in price where each move is greater (less) than the threshold ... isn't the current query going to generate an alert in every case (eg, threshold=100, the next 5 price changes are 110, 120, 135, 140, 150 => the query will generate 5 alerts?); or do you only want to generate an alert the first time the threshold is crossed (in a given direction) ... and if so ... how do you keep track of the 'last' time you crossed the threshold (in a given direction)? (I don't see how the current query would address this issue)

    – markp
    Dec 24 '17 at 1:26



















Your code has >= in both cases.

– ypercubeᵀᴹ
Dec 23 '17 at 14:14





Your code has >= in both cases.

– ypercubeᵀᴹ
Dec 23 '17 at 14:14













after looking into it, wouldnt I want the >= in both cases? because If i dont then every single "down" trend will get executed because the coin price will always be bigger than threshold of a going down trend before it even hits the actual threshold

– Zoinky
Dec 23 '17 at 20:06





after looking into it, wouldnt I want the >= in both cases? because If i dont then every single "down" trend will get executed because the coin price will always be bigger than threshold of a going down trend before it even hits the actual threshold

– Zoinky
Dec 23 '17 at 20:06




1




1





whether you use >= or <= is going to depend on your desired logic; at the moment your textual description seems a bit off as I'd expect for IsUp=0 (down/false) that you want to check for the price being less than the threshold (ie, Coins.LastPriceBtc <= Alerts.threshold) ... ???

– markp
Dec 24 '17 at 1:19







whether you use >= or <= is going to depend on your desired logic; at the moment your textual description seems a bit off as I'd expect for IsUp=0 (down/false) that you want to check for the price being less than the threshold (ie, Coins.LastPriceBtc <= Alerts.threshold) ... ???

– markp
Dec 24 '17 at 1:19















I'm also wondering how you're going to handle repeated up (or down) moves in price where each move is greater (less) than the threshold ... isn't the current query going to generate an alert in every case (eg, threshold=100, the next 5 price changes are 110, 120, 135, 140, 150 => the query will generate 5 alerts?); or do you only want to generate an alert the first time the threshold is crossed (in a given direction) ... and if so ... how do you keep track of the 'last' time you crossed the threshold (in a given direction)? (I don't see how the current query would address this issue)

– markp
Dec 24 '17 at 1:26







I'm also wondering how you're going to handle repeated up (or down) moves in price where each move is greater (less) than the threshold ... isn't the current query going to generate an alert in every case (eg, threshold=100, the next 5 price changes are 110, 120, 135, 140, 150 => the query will generate 5 alerts?); or do you only want to generate an alert the first time the threshold is crossed (in a given direction) ... and if so ... how do you keep track of the 'last' time you crossed the threshold (in a given direction)? (I don't see how the current query would address this issue)

– markp
Dec 24 '17 at 1:26












1 Answer
1






active

oldest

votes


















0














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:
Output



Alerts for test:



Alerts test



Coins for test:



Coins test






share|improve this answer


























  • doesnt have to be this complex, can be done in 1 join select * from coin left join alerts where (coin.price > alerts.price and coin.isup=0) or (coin.price < alerts.price and coin.isdown=0) rough syntax as i dont know tsql

    – PirateApp
    Mar 12 '18 at 15:05














Your Answer








StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f193880%2fconditional-field-compare-based-on-the-value-of-another-field-case-statement%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























1 Answer
1






active

oldest

votes








1 Answer
1






active

oldest

votes









active

oldest

votes






active

oldest

votes









0














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:
Output



Alerts for test:



Alerts test



Coins for test:



Coins test






share|improve this answer


























  • doesnt have to be this complex, can be done in 1 join select * from coin left join alerts where (coin.price > alerts.price and coin.isup=0) or (coin.price < alerts.price and coin.isdown=0) rough syntax as i dont know tsql

    – PirateApp
    Mar 12 '18 at 15:05


















0














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:
Output



Alerts for test:



Alerts test



Coins for test:



Coins test






share|improve this answer


























  • doesnt have to be this complex, can be done in 1 join select * from coin left join alerts where (coin.price > alerts.price and coin.isup=0) or (coin.price < alerts.price and coin.isdown=0) rough syntax as i dont know tsql

    – PirateApp
    Mar 12 '18 at 15:05
















0












0








0







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:
Output



Alerts for test:



Alerts test



Coins for test:



Coins test






share|improve this answer















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:
Output



Alerts for test:



Alerts test



Coins for test:



Coins test







share|improve this answer














share|improve this answer



share|improve this answer








edited Dec 24 '17 at 5:13

























answered Dec 24 '17 at 4:06









Shivam KumarShivam Kumar

191117




191117













  • doesnt have to be this complex, can be done in 1 join select * from coin left join alerts where (coin.price > alerts.price and coin.isup=0) or (coin.price < alerts.price and coin.isdown=0) rough syntax as i dont know tsql

    – PirateApp
    Mar 12 '18 at 15:05





















  • doesnt have to be this complex, can be done in 1 join select * from coin left join alerts where (coin.price > alerts.price and coin.isup=0) or (coin.price < alerts.price and coin.isdown=0) rough syntax as i dont know tsql

    – PirateApp
    Mar 12 '18 at 15:05



















doesnt have to be this complex, can be done in 1 join select * from coin left join alerts where (coin.price > alerts.price and coin.isup=0) or (coin.price < alerts.price and coin.isdown=0) rough syntax as i dont know tsql

– PirateApp
Mar 12 '18 at 15:05







doesnt have to be this complex, can be done in 1 join select * from coin left join alerts where (coin.price > alerts.price and coin.isup=0) or (coin.price < alerts.price and coin.isdown=0) rough syntax as i dont know tsql

– PirateApp
Mar 12 '18 at 15:05




















draft saved

draft discarded




















































Thanks for contributing an answer to Database Administrators Stack Exchange!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f193880%2fconditional-field-compare-based-on-the-value-of-another-field-case-statement%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

Liste der Baudenkmale in Friedland (Mecklenburg)

Single-Malt-Whisky

Czorneboh