Grouping / Aggregation in SQL Server 2008 R2

Question

When I run this (SQL Server 2008 R2):

SELECT [UserId]
      ,[StateDuration]
      ,[StatusKey]  
FROM [AgentActivityLog]
WHERE [StatusDateTime] between '2012-01-01' and '2012-12-31'

I get a row per UserId like this: UserId StatusDateTime StatusKey

cfrinco      3456   Meeting
echartl      23456  Available
mbarbewar    3256   Lunch
bsantiago    46565  Available
bsantiago    79     Follow Up
fburger      5464   Available

Which tells me the time in seconds that each user was at a each status.

Of course, there will be many entries per UserId per date. In other words, a user, in a day, will go from Available to Meeting to Available to Lunch to Available, etc.

What I want to do is to total the time in seconds per UserId, per StatusKey, per calendar day. So it would look something like this:

cfrinco      3456   Meeting       '2012-01-01'
cfrinco      45678  Available     '2012-01-01'
cfrinco      7845   Meeting       '2012-01-02'
cfrinco      32541  Available     '2012-01-02'
cfrinco      4455   Meeting       '2012-01-03'
cfrinco      12456  Available     '2012-01-03'
fburger      3456   Meeting       '2012-01-01'
fburger      75489  Available     '2012-01-01'
fburger      3541   Lunch         '2012-01-01'
fburger      5486   Meeting       '2012-01-02'
fburger      64587  Available     '2012-01-02'
fburger      3614   Lunch         '2012-01-02'
fburger      3225   Meeting       '2012-01-03'
fburger      35414  Available     '2012-01-03'
fburger      3745   Lunch         '2012-01-03'

Your help is greatly appreciated.

Answer 1

This is what I was looking for. After some research of different answers in stackoverflow and putting them together, I got it.

SELECT
UserId, SUM(StateDuration) SumStateDuration, StatusKey,
DATEADD(dd, 0, DATEDIFF(dd, 0, StatusDateTime)) StatusDate
FROM [AgentActivityLog]
WHERE StatusDateTime BETWEEN '2012-01-01' AND '2012-12-31'

GROUP BY
 UserId,
 DATEADD(dd, 0, DATEDIFF(dd, 0, StatusDateTime)),
 StatusKey

The key part was to (a) get just the date out of datetime column and (b) group by it.

Answer 2

If you want the data grouped by User ID, Status, and Date, then do something like:

SELECT
 UserId
 SUM(StateDuration),
 StatusKey,
 StatusDateTime
FROM AgentActivityLog
WHERE StatusDateTime BETWEEN '2012-01-01' AND '2012-12-31'
GROUP BY
 UserId,
 StatusDateTime,
 StatusKey