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

Hi Experts, given the data below - is there a example query/function that I can use to calculate the total time spent by Tom in Area 1?

Query Screenshot
LVL 1
takwirirarAsked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Assuming no overlapping times.... Could do something like :
select i.area,i.username,sum(datediff(minute,i.[timestamp], o.[timestamp])) as total_minutes
from Area_Log i
cross apply (select top 1 [timestamp] 
             from Area_Log L 
             where L.Area = i.Area and L.username = i.username and L.[event] = 'OUT' and L.[Timestamp] >= i.[timestamp] order by L.Area, L.username, L.[timestamp]) O
where i.[Event] = 'IN'
group by i.Area,i.username

Open in new window

0
 
Mark WillsTopic AdvisorCommented:
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...
0
 
Scott PletcherSenior DBACommented:
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.
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
takwirirarAuthor Commented:
Thank you! In case we ever have none corresponding IN/OUT I have gone with the query that handles that eventuality
0
 
Scott PletcherSenior DBACommented:
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.
0
 
takwirirarAuthor Commented:
Ok let me run some tests now.
0
 
Mark WillsTopic AdvisorCommented:
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])

Open in new window


So, there is still plenty of scope to accommodate different conditions.
0
 
Scott PletcherSenior DBACommented:
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.
1
 
takwirirarAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.