Time between two non consecutive rows? (MySQL)
I've looked here Calculate the time difference between of two rows.
I modified the code here:
SELECT A.Test_ID, B.TestDate - A.TestDate AS DaysBetweenTests FROM Exams A JOIN Exams B ON B.Test_ID = A.Test_ID WHERE (SELECT B.Test_ID FROM B.Exams ORDER BY Animal_ID, B.TestDate LIMIT 1 OFFSET 1)
This query isn't complete. I'm trying to calculate the days between TestDate with the next TestDate for the same Animal_ID. The problem here is that an Animal_ID TestDate is not directly consecutive with Test_ID. I can't use Test_ID +1.
Sample Exams table:
Test_ID | TestDate | Animal_ID 1 2013-01-01 100 2 2007-06-18 162 3 2013-02-01 100 4 2013-04-16 100
The TestDate for a single Animal_ID is always increasing in order, thus a higher Test_ID number.
I'm having trouble with the subquery.
This query repeats the subquery that determines the next test date. You don't necessarily need to do that in production; it just helps to see the data when you're figuring things out.
SELECT T1.Animal_ID, T1.TestDate, (SELECT min(TestDate) FROM Exams WHERE Animal_ID = T1.Animal_ID AND TestDate > T1.TestDate GROUP BY Animal_ID ) AS Next_TestDate, datediff((SELECT min(TestDate) FROM Exams WHERE Animal_ID = T1.Animal_ID AND TestDate > T1.TestDate GROUP BY Animal_ID ), T1.TestDate) AS Elapsed_Days FROM Exams T1 ORDER BY Animal_ID, TestDate;