home
Sql Server. Calculate event time
Author Nigel Rivett
We have a login and logout event, breakStart and BreakEnd events and other events.
Wish to calculate per day per user
Shift time - The accumulated time between login and logout
Break time - The accumulated break time
Shift number - The number of shifts
Break number - The number of breaks
Event number - The number of events during the event types (break, shift)
declare @events table (d datetime, event varchar(15), UserName varchar(10))
insert @events select '20000101 12:00:00', 'login', 'John'
insert @events select '20000101 12:30:00', 'BreakStart', 'John'
insert @events select '20000101 12:31:00', 'NullEvent', 'John'
insert @events select '20000101 12:32:00', 'NullEvent', 'John'
insert @events select '20000101 12:45:00', 'BreakEnd', 'John'
insert @events select '20000101 12:47:00', 'NullEvent', 'John'
insert @events select '20000101 17:00:00', 'logout', 'John'
insert @events select '20000102 08:00:00', 'test', 'John'
insert @events select '20000102 08:15:00', 'login', 'John'
insert @events select '20000102 09:15:00', 'BreakStart', 'John'
insert @events select '20000102 09:20:00', 'BreakEnd', 'John'
insert @events select '20000102 13:00:00', 'BreakStart', 'John'
insert @events select '20000102 14:00:00', 'BreakEnd', 'John'
insert @events select '20000102 17:00:00', 'logout', 'John'
insert @events select '20000101 12:00:00', 'login', 'William'
insert @events select '20000101 12:30:00', 'BreakStart', 'William'
insert @events select '20000101 12:45:00', 'BreakEnd', 'William'
insert @events select '20000101 17:00:00', 'logout', 'William'
insert @events select '20000102 08:15:00', 'login', 'William'
insert @events select '20000102 09:15:00', 'BreakStart', 'William'
insert @events select '20000102 09:20:00', 'BreakEnd', 'William'
insert @events select '20000102 13:00:00', 'BreakStart', 'William'
insert @events select '20000102 14:00:00', 'BreakEnd', 'William'
insert @events select '20000102 17:00:00', 'logout', 'William'
-- Shift time
;with cte_EventSeq as
(
select *
, seq = row_number() over (partition by UserName, dateadd(dd,datediff(dd,0,d),0) order by d)
, EventType = case when event in ('login','logout') then 'Shift' when event in ('BreakStart','BreakEnd') then 'Break' end
from @events
)
, cte_ShiftBreak1 as
(
select
UserName
,d
, dte = dateadd(dd,datediff(dd,0,d),0)
, EventType
, event
, seq = row_number() over (partition by UserName, EventType, dateadd(dd,datediff(dd,0,d),0) order by d)
, EventSeq = t.seq
from cte_EventSeq t
where event in ('login','logout','BreakStart','BreakEnd')
)
, cte_shift as
(
select
t1.EventType
, t1.UserName
, t1.dte
, ShiftStart = t1.d
, ShiftEnd = t2.d
, ShiftSecs = datediff(ss, t1.d, t2.d)
, StartEvent = t1.EventSeq
, EndEvent = t2.EventSeq
, NoEvents = t2.EventSeq - t1.EventSeq - 1
from cte_ShiftBreak1 t1
join cte_ShiftBreak1 t2
on t1.UserName = t2.UserName
and t1.dte = t2.dte
and t1.EventType = t2.EventType
and t1.seq = t2.seq - 1
)
select
EventType -- Break, Shift
, UserName
, dte
, Tot_Time = dateadd(ss,sum(ShiftSecs),0)
, StartTime = min(ShiftStart)
, EndTime = max(ShiftEnd)
, Events = sum(NoEvents) -- Number events during EventType
, NumberPeriods = count(*) -- Number of periods of EventType
from cte_shift
group by UserName, dte, EventType
order by UserName, dte, EventType
EventType UserName dte Tot_Time StartTime EndTime Events NumberPeriods
--------- ---------- ----------------------- ----------------------- ----------------------- ----------------------- -------------------- -------------
Break John 2000-01-01 00:00:00.000 1900-01-01 00:15:00.000 2000-01-01 12:30:00.000 2000-01-01 12:45:00.000 2 1
Shift John 2000-01-01 00:00:00.000 1900-01-01 05:00:00.000 2000-01-01 12:00:00.000 2000-01-01 17:00:00.000 5 1
Break John 2000-01-02 00:00:00.000 1900-01-01 04:45:00.000 2000-01-02 09:15:00.000 2000-01-02 14:00:00.000 0 3
Shift John 2000-01-02 00:00:00.000 1900-01-01 08:45:00.000 2000-01-02 08:15:00.000 2000-01-02 17:00:00.000 4 1
Break William 2000-01-01 00:00:00.000 1900-01-01 00:15:00.000 2000-01-01 12:30:00.000 2000-01-01 12:45:00.000 0 1
Shift William 2000-01-01 00:00:00.000 1900-01-01 05:00:00.000 2000-01-01 12:00:00.000 2000-01-01 17:00:00.000 2 1
Break William 2000-01-02 00:00:00.000 1900-01-01 04:45:00.000 2000-01-02 09:15:00.000 2000-01-02 14:00:00.000 0 3
Shift William 2000-01-02 00:00:00.000 1900-01-01 08:45:00.000 2000-01-02 08:15:00.000 2000-01-02 17:00:00.000 4 1
home