nicom nicom -4 years ago 97
MySQL Question

MySQL - Difference between values related to a date

With help of the following query I'm able to request the difference between two values for a specific ID between two dates.

SET @ID = 46;
SET @DATE1 = '2017-02-08';
SET @DATE2 = '2017-02-12';


select today.value - tbd_date.value from
(Select value from Table1 WHERE Datum = @DATE1 and ID = @ID) as today
CROSS JOIN
( (Select value from Table1 WHERE Datum = @DATE2 and ID = @ID) as tbd_date)


But there are more than one ID. How is the query to get for all IDs of the Table the difference between this two dates?

Afterwards I want to sort them based on the difference of the values.

Table:

ID DATE value
46 2017-02-08 5
47 2017-02-08 8
48 2017-02-08 10
46 2017-02-12 7
47 2017-02-12 11
48 2017-02-12 5


Result:

ID value (today) difference
47 11 +3
46 7 +2
48 5 -5

Answer Source

Just join on the id:

Select a.id, b.value - a.value
From table1 a
   join table1 b on b.Id = a.Id 
      and b.datum = @date2
      and a.datum = @date1
order by b.value - a.value
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download