Hi Guys, Hope you can Help. I am using SQL 2008 R2
I have two tables tbPersonal and tbArea
- tbPersonal * * tbArea *
- GUID * * A_GUID *
- Name * * Direction *
- CK DateTime * * TXN DateTime *
The tbPersonal table holds all the transactions for employees who are clocking in and out.
The tbarea table holds the direction (in or Out) they are going.
What I am trying to do it list all employees who have a 'last transaction of IN and filter out all last transactions that are OUT.
When I have this list, then I need to calculate how many hours there are from that 'IN' transaction to current date/Time and output it.
The GUID and A_GUID are the relationship between the two tables.
Below are a couple script I have been playing about at but I am now stuck. I am a novice at SQL Script writing.
Need to find out how to put the two scripts together and only list the IN transactions that does NOT have an OUT transaction after it.
Hope I have given you enough info. P.S. The Control-K did not work
/script below lists the last transaction for both the IN and OUT direction
How can I list all IN transactions that DO NOT have an OUT transaction after it/
MAX(jlo.TXN_DateTime) as [Direction TXN], jl.Name as [Employee Name], jlo.Direction
tbArea as jlo
inner join tbPersonal AS jl
having jlo.Direction = 'outside' or jlo.Direction = 'inside'
/* The script below will find the dime differance in hours for all the "IN" transaction
declare @startTime as datetime declare @Time_diff as datetime declare @Max_TXN as datetime select jl.Name as [Employee Name], max(jl.CK_DateTime) as [Last Transaction], jlo.Direction as Area, (DATEDIFF(hh, jl.CK_DateTime, GETDATE())) as [time_diff] from tbPersonal as jl inner join tbArea AS jlo ON jl.GUID=jlo.A_GUID Where jlo.Direction = 'inside' group by (jl.Name), (jlo.Direction), jl.CK_Datetime