SQLTeam.com | Weblogs | Forums

Using DATEDIFF and MAX to find last date in a table and find the differance



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],

tbArea as jlo
inner join tbPersonal AS jl

group by
jl.Name, jlo.TXN_Datatime
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

		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]
		tbPersonal as jl
			inner join tbArea AS jlo
			ON jl.GUID=jlo.A_GUID
		jlo.Direction = 'inside'
	group by 
		(jl.Name), (jlo.Direction), jl.CK_Datetime