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.
Advertisement
Answer
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:
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')