CODEWITHSUNDEEP
phpmysql
CodeOverload
CodeOverload

Reputation: 48505

Get duration between records from separate tables by date

This is a bit tricky to explain but let's say i have 2 tables, one for the quotes and the other for the quotes responses.

Table: Quotes
-----------------
id  |  name  | date(DATETIME)
1   |  foo   | 2012-09-11 16:21:18
2   |  bar   | 2012-09-11 16:48:28

Table: Responses
-----------------
quote_id  |  response  |  date(DATETIME)
1         |  blah      |  2012-09-11 16:28:42
2         |  Meh       |  2012-09-11 16:58:34

I'd like to get the total average response rate to the quotes based on deducting the quote date off the response date.

So i get something like 8.7 seconds average response time.

How to do that efficiently using only a MySQL query if possible?

P.S. A Quote can have multiple responses, The calculation should only consider the first response of each quote for an accurate result.

Upvotes: 0

Views: 81

Answers (2)

Derek
Derek

Reputation: 23268

This joins your Quotes to the first date in Responses and gets the AVG TIMEDIFF. This will only consider those Quotes that have Responses.

SELECT AVG(TIMEDIFF(b.`date`, a.`date`))
FROM Quotes a
INNER JOIN (
  SELECT quote_id, MIN(`date`) as `date` 
  FROM Responses
  GROUP BY quote_id) b 
  ON (b.quote_id = a.quote_id)

Upvotes: 2

a1ex07
a1ex07

Reputation: 37384

Will it work for you?

SELECT AVG(TIMEDIFF(b.`date`, a.`date`))
FROM Quotes a
INNER JOIN Responses b ON (b.quote_id = a.quote_id)

Upvotes: 0

Related Questions