T-SQL Calculate total time spent based on IN/OUT clockings

Could do something like select the IN eevents and then cross apply to get the top 1 out event.

I will write some code, but first a couple of questions

1) is an OUT always following an IN -
2) if there is an IN and an OUT are they adjacent pairs  for the username/area ?

Back is a few minutes with some code...

If the INs and OUTs correspond, you don't need to use I/O to JOIN/look them up:

SELECT Area, Username,
    SUM(DATEDIFF(SECOND, '19900101', Timestamp) * CASE WHEN Event = 'IN' THEN -1 ELSE 1 END) / 60.0 AS MinsDiff
FROM [dbo].[area_log]
GROUP BY Area, Username

'19900101' is arbitrary -- any base date earlier than your earliest time and with enough room for the seconds calc would work.

Thank you! In case we ever have none corresponding IN/OUT I have gone with the query that handles that eventuality

But it doesn't.  If an OUT is missing, that code will just pick up the next OUT for the same person.  For example, if the 16:12:01.563 row was missing.

Ok let me run some tests now.

What it does accommodate is a case when there is the missing final  "OUT"

Was also thinking we could simply check that they (the IN and the OUT) are on the same DATE in the cross apply

Can always add in the extra test / check in the where clause - which is what I was going to do if there were overlapping IN and OUT

and not exists (select 1 from Area_Log L 
             where L.Area = i.Area and L.username = i.username and L.[event] = 'IN' and L.[Timestamp] > i.[timestamp] and L.[timestamp] < o.[timestamp])

Select allOpen in new window

So, there is still plenty of scope to accommodate different conditions.

What it does accommodate is a case when there is the missing final  "OUT"

In that case, the code ignores the final "IN" -- is that the desired behavior or not?  No way to know for sure.

there is still plenty of scope to accommodate different conditions.

Well sure, any code can always be rewritten to accommodate anything.

But in time calcs, the major thing is to get a valid processing set -- i.e. INs and OUTs match as required -- first.  If you need to drop rows, do so; if you need to gen implicit OUT rows, do so.  Only then do the total, and by that point, it's a waste of resources to do any join in the dataset.

Yes this is the challenge so I will tighten the hardware to ensure we always have the IN's and OUT's then I have also created a "Anomalies" page in case the hardware doesn't do its job. Only then will the report be run