sql - Monthly Average on Daily Statistics -

sql - Monthly Average on Daily Statistics -

i have table of phone call center agents (daily statistics), stored agent per day.

i monthly average agents, can compare monthly statistics with, current effort show daily averages during month opposed average whole month:

select convert(date, dateadd(month, datediff(month, 0, date), 0),0), avg(callsanswered) dbo.avayad_agent_stat grouping convert(date, dateadd(month, datediff(month, 0, date), 0),0)

appreciate help can provide (or need clarification), , give thanks you!

edit: helps bit, realize above isn't great @ explaining i'm shooting for. looking produce average of sum of agent statistics month. (if worded correctly)

for illustration if have 2 agents both work 10 days, 1 takes 10 calls day, other takes 20 calls day. shows 15 calls using query have above. i'd presented 150 calls (300 total calls/ 2 agents). problem can't set static amount of days/ agents changing.

including larger snip of table requested.

select date, agentlogin, callsanswered dbo.avayad_agent_stat date agentlogin callsanswered 2014-08-11 1005 21 2014-08-12 1005 21 2014-08-13 1005 23 2014-08-14 1005 18 2014-08-15 1005 16 2014-08-18 1005 25 2014-08-19 1005 44 2014-08-20 1005 17 2014-08-21 1005 25 2014-08-22 1005 12 2014-08-25 1005 23 2014-08-26 1005 32 2014-08-27 1005 24

if want average per agent entire month, sum total month , split number of agents:

select dateadd(month, datediff(month, 0, date), 0), sum(callsanswered) / count(distinct agentid) dbo.avayad_agent_stat grouping dateadd(month, datediff(month, 0, date), 0)

note removed final convert() on month calculation. redundant.

sql