I'm tempted to take a crack at this, because time-oriented problems tend to be more common than we'd like, and their solutions tend to be more obnoxiously complex than one would think.
Can a shift overlap two days? i.e they Check-In at 11:55PM and Check-Out at 12:05AM the next day?
Can there be Check-Ins and Outs that aren't paired up? Like
11:55 AM Check-In
11:56 AM Check-In
11:57 AM Check-Out
Quite new to all this myself, but wouldn't something like this work?
load * inline [
ID, EmployeeID, CheckTime, CheckType
1, 1, 26/10/17 08:00:00, In
2, 2, 26/10/17 08:10:00, In
3, 3, 26/10/17 08:11:00, In
4, 2, 26/10/17 10:10:00, Out
5, 1, 26/10/17 12:10:00, Out
6, 3, 26/10/17 15:00:00, Out
Time(If(Peek(EmployeeID)=EmployeeID, Time-Peek(Time), Null()), 'hh:mm') as TimeDiff;
Time(CheckTime) as Time;
Timestamp#(CheckTime, 'DD/MM/YYYY hh:mm:ss') as CheckTime,
Order by EmployeeID;
Drop Table Temp_Data;
I had to do something similar in one of my projects for a help ticket tracker.
In your scenario, you could just replace the 'created' field value with your 'Clock-in' value and the 'resolved' field value with your 'clock-out' value. I would imagine similar results.
Resolution Time expression is:
where the variable is defined as:
(resolved - created) *86400