Average time from DateTime dataset in SQL Server 2005

Question

I am trying to get the avg time a driver completed a delivery stops at each unique customer. I am using the following columns:

Name (varchar(50))
Reference1 (varchar(50))
CompletedTime (Datetime).

I am getting an avg but it includes the date & time NOT just date. Which is making the time avg incorrect.

thanks for the help!

Current Code Below:

select name, Reference1, CAST(AVG(CAST(CompletedTime AS float)) AS datetime)
From tblOrderRouteStops
where Name not like 'Cta%' and Reference1 <> '000000' and Name <> '' and CompletedTime is not Null
Group By name, Reference1

Data Set:

CASCADE HEMOPHILIA CONSORTIUM   000117  2010-10-01 09:24:01.000
CASCADE HEMOPHILIA CONSORTIUM   000117  2010-09-29 09:30:23.000
CASCADE HEMOPHILIA CONSORTIUM   000117  2010-09-27 09:44:17.000
CASCADE HEMOPHILIA CONSORTIUM   000117  2010-09-24 09:36:49.000
CASCADE HEMOPHILIA CONSORTIUM   000117  2010-09-23 09:48:20.000
CASCADE HEMOPHILIA CONSORTIUM   000117  2010-09-22 09:21:20.000
BOTSFORD OUT-PATIENT PHARMACY   006106  2010-09-23 08:01:06.000
BOTSFORD OUT-PATIENT PHARMACY   006106  2010-09-22 08:02:42.000
BOTSFORD OUT-PATIENT PHARMACY   006106  2010-09-24 08:21:36.000
BOTSFORD OUT-PATIENT PHARMACY   006106  2010-09-27 08:24:49.000
BOTSFORD OUT-PATIENT PHARMACY   006106  2010-09-28 08:25:58.000
BOTSFORD GENERAL HOSPITAL PREM  006205  2010-10-04 07:33:13.000
BOTSFORD GENERAL HOSPITAL PREM  006205  2010-10-01 07:35:13.000
BOTSFORD GENERAL HOSPITAL PREM  006205  2010-09-30 07:31:15.000
BOTSFORD GENERAL HOSPITAL PREM  006205  2010-09-29 07:41:56.000
BOTSFORD GENERAL HOSPITAL PREM  006205  2010-09-28 08:03:38.000

Desired Output:

Name, Reference1, Time

Answer 1

Your float approach was a good start: you need to remove the whole number which gives you a fraction representing time of day. Average that, change back to datetime. To use this, ignore the "01 jan 1900" and take the time bit as your average

select name, Reference1,
          CAST(AVG(CAST(CompletedTime AS float) - CAST(CompletedTime AS int)) AS datetime)
From tblOrderRouteStops
where Name not like 'Cta%' and Reference1 <> '000000' and Name <> '' and CompletedTime is not Null
Group By name, Reference1

Answer 2

This wil work for you

You can get rid of the dateby using convert(varchar,Value,108)

select name, Reference1,
    CONVERT(varchar,  CAST(AVG(CAST(CompletedTime AS float) - CAST(CompletedTime AS int)) AS datetime),108) averageTime
        From tblOrderRouteStops
        where Name not like 'Cta%' and Reference1 <> '000000' and Name <> '' and CompletedTime is not Null
        Group By name, Reference1