CODEWITHSUNDEEP
sql-serveraverage
Peppe
Peppe

Reputation: 121

Average calls per week for each Month

Hi I would like to get average calls per week for each month Also, average calls per week for each Quarter Also, average calls per week for each Year

I need a query for each of the above. Thank you for your help!

Below is the raw data set:

Name    Call        CallDate
Jack    Phone Call  2017-03-10
Laura   Meeting     2017-03-08
Helen   Phone Call  2017-02-02
Sam     Video Conf  2017-02-10
David   Skype       2017-01-01
Jack    Skype       2017-01-14
Laura   Video Conf  2014-03-09
Helen   Meeting     2017-02-12
Sam     Meeting     2017-02-12
David   Meeting     2017-03-09
David   Video Conf  2017-02-25
David   Skype       2017-02-20
Jack    Skype       2017-02-20
Jack    Phone Call  2017-03-10
Laura   Phone Call  2017-02-07
Laura   Skype       2017-02-07
Sam     Phone Call  2017-03-04
Sam     Phone Call  2017-03-04
Sam     Skype       2017-03-10

Upvotes: 0

Views: 682

Answers (1)

t-clausen.dk
t-clausen.dk

Reputation: 44336

Best effort since your question is strange.

This will count the calls in each month divide it with the days in the month and multiply by 7

;WITH CTE as
(
  SELECT 
    dateadd(month, datediff(month,0, cast(CallDate as datetime)), 0) month
  FROM yourtable
)
SELECT
  -- day(eomonth(month)) calculates the number of days in the month
  count(*)*7./day(eomonth(month)) avgweeklycallsmonth,
  month
FROM CTE
GROUP BY month

This will count the calls in each quarter divide it with the days in the quarter and multiply by 7

;WITH CTE as
(
  SELECT dateadd(quarter, datediff(quarter,0, CallDate), 0) quarter
  FROM yourtable
)
SELECT 
  count(*)*7./
    (datediff(day,dateadd(year, datediff(year, 0, quarter), 0),
      dateadd(month,3, quarter))) avgweeklycallsquarter, 
  quarter
FROM CTE
GROUP BY quarter

Upvotes: 1

Related Questions