Ok I have looked at multiple entries in stackoverflow for this but cant seem to quite work them for me.
I have a table called email_Archive
It contains every email sent by every user.
Columns in Db are:
id created username message recipient
Challenge: I am trying to get the average amount of emails sent by a select list of users per day for the last month. (created covers YYYY-MM-DD:MM:ss)
select List of users:
A: f: g: h: i: n: q: r:
The only info I want to return is userid and the average count per day for the last month only. and yes on some days that will be 0 but I need to show that 0 for that day
Advertisement
Answer
You can write
Select id as userid, Count(*) From MessagesTable Where created > Dateadd(month, -1, GETDATE()) and id in (userids you want) group by Cast(created as Date)
If you want it into a seperate table you can do either:
Select id as userid, Count(*) into TargetTableName From MessagesTable Where created > Dateadd(month, -1, GETDATE()) and id in (userids you want) group by Cast(created as Date)
or
Insert into TargetTableName Select id as userid, Count(*) From MessagesTable Where created > Dateadd(month, -1, GETDATE()) and id in (userids you want) group by Cast(created as Date)