How to find the average time? SQL Server 2017

I have a request like this:

created_at  - datetime

I need to find the average time for each status. For example, add up the time of all statuses 2 and divide by their number. As a result, get something like 2 days 5 hours.

How to fix my request to make belt operations possible?

Answer

Taking your limited example data, something like this does the job, although it’s not pretty:

declare @deals table(
    deal_id int,
    status_id int,
    created_at datetime
)

insert into @deals values
(1,1,'2021-05-12 18:54:00.000'),
(1,2,'2021-05-13 13:34:00.000'),
(1,3,'2021-05-17 14:39:00.000'),
(1,2,'2021-05-18 09:24:00.000'),
(1,3,'2021-05-18 09:24:00.000'),
(1,4,'2021-05-18 10:27:00.000'),
(1,5,'2021-05-19 13:00:00.000'),
(1,4,'2021-05-19 13:00:00.000'),
(1,5,'2021-05-24 08:06:00.000'),
(2,1,'2021-05-14 17:31:00.000')

select 
    status_id,
    cast(avg(datediff(minute,cast(created_at as date),created_at))/60 as varchar(2)) + ':' + right('0' + cast(avg(datediff(minute,cast(created_at as date),created_at)) - ((avg(datediff(minute,cast(created_at as date),created_at))/60)*60) as varchar(2)),2) as averageTime
from @deals
group by status_id
order by status_id

You get these results: