T-SQL, SQL Server 2008 and up
Given a sample table of
StatusSetDateTime | UserID | Status | StatusEndDateTime | StatusDuration(in seconds)
============================================================================
2012-01-01 12:00:00 | myID | Available | 2012-01-01 13:00:00 | 3600
I need to break that down into a view that uses 15 minute intervals for example:
IntervalStart | UserID | Status | Duration
===========================================
2012-01-01 12:00:00 | myID | Available | 900
2012-01-01 12:15:00 | myID | Available | 900
2012-01-01 12:30:00 | myID | Available | 900
2012-01-01 12:45:00 | myID | Available | 900
2012-01-01 13:00:00 | myID | Available | 0
etc....
Now I've been able to search around and find some queries that will break down I found something similar for MySql Here :
And something for T-SQL Here
But on the second example they are summing the results whereas I need to divide the total duration by the interval time (900 seconds) by user by status.
I was able to adapt the examples in the second link to split everything into intervals but the total duration time is returned and I cannot quite figure out how to get the Interval durations to split (and still sum up to the total original duration).
Thanks in advance for any insight!
edit : First Attempt
;with cte as
(select MIN(StatusDateTime) as MinDate
, MAX(StatusDateTime) as MaxDate
, convert(varchar(14),StatusDateTime, 120) as StartDate
, DATEPART(minute, StatusDateTime) /15 as GroupID
, UserID
, StatusKey
, avg(StateDuration) as AvgAmount
from AgentActivityLog
group by convert(varchar(14),StatusDateTime, 120)
, DATEPART(minute, StatusDateTime) /15
, Userid,StatusKey)
select dateadd(minute, 15*GroupID, CONVERT(datetime,StartDate+'00'))
as [Start Date]
, UserID, StatusKey, AvgAmount as [Average Amount]
from cte
edit : Second Attempt
;With cte As
(Select DateAdd(minute
, 15 * (DateDiff(minute, '20000101', StatusDateTime) / 15)
, '20000101') As StatusDateTime
, userid, statuskey, StateDuration
From AgentActivityLog)
Select StatusDateTime, userid,statuskey,Avg(StateDuration)
From cte
Group By StatusDateTime,userid,statuskey;