AVG(DateDiff) in one line?

• jennigirl

  Old Hand

  Points: 372

  More actions

  November 10, 2014 at 1:05 pm

  #298331

  I am trying to pull a report with average down time and I getting the error message "Msg 195, Level 15, State 10, Line 4

  'AVG' is not a recognized built-in function name." when I try to run the below query. How can I rephrase the AVG(DateDiff) line to calculate this for me? Also, what would this be called so I can do some additional learning on the topic?

  SELECT

  TT.PartNumber

  ,AVG (TT.TimeToRepair) as [Avg Time to Repair (Hours)]

  ,AVG(DateDiff (hour,TT.TimeDateReported,TT.DateClosed) as [Turnaround Time(Hours)])

  FROM dbo.vt_TroubleTicket TT

  WHERE TT.Closed = '-1'

  and TT.DateClosed between '1/1/2013' and '1/1/2014'

  and (TT.PartNumber = '12345')

  GROUP BY TT.PartNumber

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  November 10, 2014 at 1:11 pm

  #1757544

  jennigirl (11/10/2014)

  ---

  I am trying to pull a report with average down time and I getting the error message "Msg 195, Level 15, State 10, Line 4

  'AVG' is not a recognized built-in function name." when I try to run the below query. How can I rephrase the AVG(DateDiff) line to calculate this for me? Also, what would this be called so I can do some additional learning on the topic?

  SELECT

  TT.PartNumber

  ,AVG (TT.TimeToRepair) as [Avg Time to Repair (Hours)]

  ,AVG(DateDiff (hour,TT.TimeDateReported,TT.DateClosed) as [Turnaround Time(Hours)])

  FROM dbo.vt_TroubleTicket TT

  WHERE TT.Closed = '-1'

  and TT.DateClosed between '1/1/2013' and '1/1/2014'

  and (TT.PartNumber = '12345')

  GROUP BY TT.PartNumber

  You need to change your closing parenthesis.

  SELECT

  TT.PartNumber

  ,AVG (TT.TimeToRepair) as [Avg Time to Repair (Hours)]

  ,AVG(DateDiff (hour,TT.TimeDateReported,TT.DateClosed)) as [Turnaround Time(Hours)]

  FROM dbo.vt_TroubleTicket TT

  WHERE TT.Closed = '-1'

  and TT.DateClosed between '1/1/2013' and '1/1/2014'

  and (TT.PartNumber = '12345')

  GROUP BY TT.PartNumber

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• jennigirl

  Old Hand

  Points: 372

  More actions

  November 10, 2014 at 1:14 pm

  #1757546

  That was embarrassingly simple. Thank you for your time!

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  November 10, 2014 at 1:17 pm

  #1757547

  Sometimes we miss the little details and just need a different point of view.

  Glad I could help.

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2