This works, but it is very slow. Takes 1:43 to run on just 1 event. There are up to 200 events.
TIMECLOCK holds all timing events including meetings, breaks and lunches
MAPS maps users to events and the last event date/time
REASONS hold all reason codes
REASONSALLOWED hold the codes to filter out time events (we don't count off clock or lunch)
The goal is to remove 'AND MAPS.eventID = 464' and have this run against all events and calculate the time accumulated since the 'lastEvent' on a per user/event basis.
I'd like this job to run every minute if possible, but in it's current form it'll take 5 hours to run once.
SELECT TIMES.personID, MAPS.eventID,
CONVERT(TIME,DATEADD(s,SUM(DATEDIFF(SECOND, TIMES.startTime, TIMES.endTime) ),0)) as duration
SELECT TIMES1.personID, TIMES1.eventtype, TIMES1.reasoncode, TIMES1.eventdatetime,
TIMES1.eventdatetime as startTime,
FROM TIMECLOCK TIMES2
WHERE TIMES2.eventdatetime > TIMES1.eventdatetime
and TIMES2.personID = TIMES1.personID ),GETUTCDATE()) as endTime
FROM TIMECLOCK TIMES1
JOIN MAPS on MAPS.personID = TIMES.personID AND MAPS.active = 1
JOIN REASONS on REASONS.eventtype = TIMES.eventtype AND REASONS.reasoncode = TIMES.reasoncode
WHERE DATEADD(mi, DATEDIFF(mi, GETUTCDATE(), GETDATE()), TIMES.eventdatetime) > MAPS.lastEvent
AND REASONS.reasonID in (SELECT reasoncode FROM REASONALLOWED)
AND MAPS.eventID = 464
GROUP BY TIMES.personID, MAPS.eventID
ORDER BY MAPS.eventID, duration DESC