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
David CIT Projects ManagerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

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
Mark WillsTopic 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

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

David CIT 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
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
David CIT Projects ManagerAuthor 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
David CIT 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.