The difference of two time or two timestamp fields is the elapsed time in seconds. From what I see, you know this already given the elapsed time field that I see.
So getting the previous timestamp would seem to be the issue. What criteria would you use to identify the previous log entry from which to get that value? Same Created by Name, but greatest TimeStamp value that is still less than the current record's time stamp?
A self join could be used to match to that field.
ExecuteSQL could grab that value also.
To minimize performance hits, I'd copy that value into a field of the current record at the time it is created so that you can use a stored calculation that computes elapsed time instead of a "chain" of unstored calculations.
We can break it down into segments.
Example, the criteria is the CreatedBy (which gives the username) and we would break it down by date.
So we have the total amount spent on X date by X person.
This is where I got the Idea of subtracting the Timestamps to get the total amount spent. I have tried using summary fields but doesn't quite work as I intended it to.
After all of this is established, it's as simple as adding up the dates to get the total amount spent for a month.
When you say copy the value into a field, you mean copy the timestamp that is shown? Because that is already hard coded in and does not change. ProcessTime field is a stored calculation since it does it once per record. And that is ScriptEnd - ScriptStart.
Regarding the SelfJoin, Would I have multiple ones? One matching the CreatedBy and the other for matching the TimeStamp?
I assume the table with the audit log is populated with a script, if so;
maybe you can set the previous timestamp as a global variable $$previousTimestamp in your script.
In your script can start with $currentTimestamp and after the new record in your audit table is created you can set $$previousTimestamp in the previous entry field.
At the end of the script you can set the variable $$previoustimestamp with $currenttimestamp.
If this is a possibility there should also be a start script and a script for lunch break and shift end.
Otherwise you will never know the time spent.
Hope this helps or maybe it will give you an idea for a another approach.
I am gathering information on how much time they spend using the application for tracking purposes. Lunch or shift end is irrelevant here.
And yes, the records are populated based on a script.
And I am planning on getting daily values first so when the next day starts, it starts from 0 again.
We can go as far as finding records from x time to x time to keep it within the work hours anyways.
But I do like setting a previous timestamp and use that as part of the calculation.
Putting the value into a global variable seems an excellent idea here. Fast and simple.