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.

Answers


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;

Need Your Help

mod_rewrite regex (too many redirects)

.htaccess mod-rewrite subdomain wildcard-subdomain

I am using mod_rewrite, to convert subdomains into directory urls. (solution from here). When I explicity write a rule for one subdomain, it works perfectly:

How can show multiple data on hover with highmaps?

javascript jquery html highcharts

How can show multipe datas on hover on map with highmaps?

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.