Calculate average time difference between two datetime fields per day

Question

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.

Solution 1

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) 

Solution 2

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) 

Solution 3

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)