Prescient Prescient - 5 months ago 15
SQL Question

Compare rows in db and select if value is greater than 15%

I'm trying to select rows that have changes greater than 15% when the dates are the same.
Here's an example of the db data that I want to compare.

id coin high24hr low24hr date
397 BTC_1CR 0.00030000 0.00030000 2016-06-10 08:39:26
525 BTC_LSK 0.00078898 0.00078898 2016-06-10 08:39:26
529 BTC_1CR 0.00030000 0.00030000 2016-06-10 08:48:05
657 BTC_LSK 0.00078884 0.00078884 2016-06-10 08:48:05


So I want to select any rows that have those the two times 2016-06-10 08:39:26 and 2016-06-10 08:48:05. Then if the 2 coin column values are the same. Compare the high24hr column values. If the difference is greater than 15% higher return the difference. if it's not return 0. And then compare low24hr column values. If the difference is greater than 15% lower return the difference. if it's not return 0.

And the output should be something like the following. Showing the percentage change if it's greater than 15%.

coin high low
BTC_1CR 20% 25%
BTC_LSK 30% 0

Answer

SQL uses set operations so you usually don't think of comparing 2 sequential rows; however, you could do with this query something similar:

declare @percent  float
set @percent = 0.0000000000000000015;

select distinct t.coin,t.date,
    (t1.high24hr-t.high24hr)/100.0 as high, 
    (t2.low24hr-t.low24hr)/100.0 as low 
    from YourTable t
    left join YourTable t1 on 
        t.coin=t1.coin and t.date>t1.date and (t1.high24hr-t.high24hr)/100.0>@percent
    left join YourTable t2 on 
        t.coin=t2.coin and t.date>t2.date and (t2.low24hr-t.low24hr)/100.0>@percent