# 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?

LVL 1
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Assuming no overlapping times.... Could do something like :
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'
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

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

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

'19900101' is arbitrary -- any base date earlier than your earliest time and with enough room for the seconds calc would work.
0
IT Projects ManagerAuthor Commented:
Thank you! In case we ever have none corresponding IN/OUT I have gone with the query that handles that eventuality
0
Senior 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
IT Projects ManagerAuthor Commented:
Ok let me run some tests now.
0
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])

So, there is still plenty of scope to accommodate different conditions.
0
Senior 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
IT Projects ManagerAuthor 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
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.