Calculate average days between two dates

user_15012638 0
Viewed 272

I have the following table:

Team PhoneNumber OrderNo CallDate OrderDate
TM1 2222222222 26699443 2021-01-28 2021-02-05
TM1 1111111111 26699450 2021-01-22 2021-01-22
TM2 5555555555 26699466 2021-02-22 2021-02-23
TM2 5555555555 26699467 2021-01-22 2021-02-01
TM3 7777777777 26699488 2020-12-10 2021-01-03

I want to calculate the average time from call to order for each team for each month. This is my query:

SELECT 
    Team,
    MONTH(CallDate),
    AVG(DATEDIFF(day,CallDate,OrderDate))
FROM MyTab AS C 
GROUP BY Team, MONTH(CallDate)

I want to calculate the difference between the CallDate and the OrderDate and then apply the Average for each team for each month.

tsql sql-server average sql
1 Answers

There is no time part in the data so how you are planning to calculate time? Do you want to multiply number of days with 24 or 8 (official time)? Please mention. If orderdate and calldate are same date then day will be 0 or one? I have considered it as 1 day.

To calculate day difference:

SELECT 
    Team,
    format(CallDate,'yyyy-MMMM') Month,
    AVG(DATEDIFF(day,CallDate,OrderDate)+1) AverageDayTaken
FROM MyTab AS C 
GROUP BY Team, format(CallDate,'yyyy-MMMM')

Output:

enter image description here

To calculate working hour difference(8 hour per day):

SELECT 
    Team,
    format(CallDate,'yyyy-MMMM') Month,
    AVG(DATEDIFF(day,CallDate,OrderDate)+1)*8 AverageWorkingHourTaken
FROM MyTab AS C 
GROUP BY Team, format(CallDate,'yyyy-MMMM')
user_86516010
Related