CODEWITHSUNDEEP
sqlsql-serversql-server-2008datetime
Laura Harmon
Laura Harmon

Reputation: 321

How to get the average number of minutes from datetime?

What I want to do is show the average number of minutes a technician has worked on each ticket, but only where the average is greater than or equal to the overall average minutes per ticket. But you can't use avg on datetime.

I know this is quite wrong, but this is what I'm starting with:

select user_firstname + ' ' + user_lastname as technician_name, 
avg(ticket_requestdate - ticket_closeddate) as average_minutes_per_ticket
from hd_users
    join hd_tickets on ticket_requestor_id=user_id
where user_is_technician = '1' 
having avg(ticket_requestdate - ticket_closeddate) >=
        (select avg(ticket_requestdate - ticket_closeddate) from hd_users)

Upvotes: 0

Views: 2151

Answers (1)

Hedinn
Hedinn

Reputation: 864

I would use datediff() with minutes in a subquery

datediff(minute,datestart,dateend)

then you get the number of minutes as int and then you can do the avg()

or 

avg(datediff(minute,datestart,dateend))

----EDIT ---- I'm guessing a bit since I don't know your table structure, but what about this

select user_firstname + ' ' + user_lastname as technician_name, 
avg(Datediff(minute,ticket_requestdate,ticket_closeddate)) as average_minutes_per_ticket
from hd_users
join hd_tickets on ticket_requestor_id=user_id
where user_is_technician = '1' 
GROUP BY user_firstname, user_lastname 
having avg(Datediff(minute,ticket_requestdate,ticket_closeddate)) >=
    (select avg(Datediff(minute,ticket_requestdate,ticket_closeddate)) from hd_users)

Upvotes: 2

Related Questions