SQLTeam.com | Weblogs | Forums

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

tsql
sql2008

#1

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
/

Select

MAX(jlo.TXN_DateTime) as [Direction TXN],
jl.Name as [Employee Name],
jlo.Direction

from
tbArea as jlo
inner join tbPersonal AS jl
ON jlo.A_GUID=jl.GUID

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

	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