CODEWITHSUNDEEP
sqlt-sqldatedatetime
ChrisCurrie
ChrisCurrie

Reputation: 1619

Calculate average time difference between two datetime fields per day

I have a taxi database with two datetime fields 'BookedDateTime' and 'PickupDateTime'. The customer needs to know the average waiting time from the time the taxi was booked to the time the driver actually 'picked up' the customer.

There are a heap of rows in the database covering a couple of month's data.

The goal is to craft a query that shows me the daily average.

So a super simple example would be:

BookedDateTime           | PickupDateTime
2014-06-09 12:48:00.000    2014-06-09 12:45:00.000
2014-06-09 12:52:00.000    2014-06-09 12:58:00.000    
2014-06-10 20:23:00.000    2014-06-10 20:28:00.000
2014-06-10 22:13:00.000    2014-06-10 22:13:00.000

2014-06-09 ((-3 + 6) / 2) = average is 00:03:00.000 (3 mins)

2014-06-10 ((5 + 0) / 2) = average is 00:02:30.000 (2.5 mins)

Is this possible or do I need to do some number crunching in code (i.e. C#)?

Any pointers would be greatly appreciated.

Upvotes: 8

Views: 29000

Answers (4)

Gouri Shankar Aechoor
Gouri Shankar Aechoor

Reputation: 1581

Please try this:

IF OBJECT_ID(N'tempdb..#TEMP') > 0
    BEGIN
       DROP TABLE #TEMP
    END
CREATE TABLE #TEMP(BookedDateTime DateTime,
                PickupDateTime DateTime)
INSERT INTO #TEMP
VALUES
('2014-06-09 12:48:00.000', '2014-06-09 12:45:00.000'),
('2014-06-09 12:52:00.000', '2014-06-09 12:58:00.000'), 
('2014-06-10 20:23:00.000', '2014-06-10 20:28:00.000'),
('2014-06-10 22:13:00.000', '2014-06-10 22:13:00.000'),
('2014-06-10 23:59:00.000', '2014-06-11 00:01:00.000')

SELECT CAST(BookedDateTime AS DATE) AS YMDDate,
      CONVERT(CHAR(8), DATEADD(second, AVG(DATEDIFF(s, BookedDateTime, PickupDateTime)), 0), 108) [hh:mi:ss],
      CONVERT(CHAR(15), DATEADD(second, AVG(DATEDIFF(s, BookedDateTime, PickupDateTime)), 0), 114) [hh:mi:ss:mmm(24h)]
FROM #TEMP
GROUP BY CAST(BookedDateTime AS DATE)

Upvotes: 1

Akalanka
Akalanka

Reputation: 310

This can be done by finding the difference of each row, summing them up and dividing by the number of rows.

In SQLSERVER it will look like below.

SELECT SUM(DATEDIFF(MINUTE,BookedDateTime,PickupDateTime)) * 1.0 
           / (SELECT COUNT(*) * 1.0 FROM MyTable)
FROM MyTable

Upvotes: 0

Bohemian
Bohemian

Reputation: 424983

Using the day of the booked time as the day for reporting:

select
    convert(date, BookedDateTime) as day,
    AVG(DATEDIFF(minute, PickupDateTime, BookedDateTime)) as avg_minutes
from bookings
group by convert(BookedDateTime, datetime, 101)

Upvotes: 2

NeedAnswers
NeedAnswers

Reputation: 1435

I think this will do : 

select Convert(date, BookedDateTime) as Date, AVG(datediff(minute, BookedDateTime, PickupDateTime)) as AverageTime
    from tablename 
    group by Convert(date, BookedDateTime)  
    order by Convert(date, BookedDateTime)

Upvotes: 6

Related Questions