• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 663
  • Last Modified:

Compare Time from two tables

I have two tables, TBL_JobTrack and TBL_TimeTrack. When employee comes in the morning they sing in and the data goes to TBL_TimeTrack and later they start job and data goes to TBL_JobTrack.

How do I get a report or a query to get a daily comparison who started job after how many minutes they signed in. Also my table structures are not the same but they both have a [StartTime] field which contains the Date and time.

Any idea?
0
nibirkhan
Asked:
nibirkhan
2 Solutions
 
NestorioCommented:
Try this:

Select a.Empl_Id, DateValue(a.StartTime) as SignIn, b.StartJob, b.StartJob- a.StartTime as DiffTime
From TBL_TimeTrack as a Inner Join
   (Select Empl_Id, DateValue(StartTime) as dDate, Min(StartTime) as StartJob
     From TBL_JobTrack
     Group by Empl_id, DateValue(StartTime)) as b
On a.Empl_Id = b.Empl_Id
Where DateValue(a.StartTime) = DateValue(b.StartTime)
0
 
oldmanbimCommented:
Hi,

I've made a few assumptions and built three tables:
TBL_JobTrack
  ID
  EmployeeID
  StartTime
  EndTime

Tbl_TimeTrack
  ID
  EmployeeID
  StartTime
  EndTime

TBL_Employees
  EmployeeID
  EmployeeName

SELECT
  TBL_JobTrack.StartTime, TBL_TimeTrack.StartTime,
  CDate(TBL_JobTrack.StartTime-TBL_TimeTrack.StartTime) AS DiffTime,
  Hour([DiffTime])*60+Minute([DiffTime]) AS Mins, [TBL_Employees].EmployeeName
FROM
  (TBL_JobTrack INNER JOIN TBL_TimeTrack ON TBL_JobTrack.EmployeeID=TBL_TimeTrack.EmployeeID)
  INNER JOIN TBL_Employees ON TBL_JobTrack.EmployeeID=[TBL_Employees].EmployeeID
WHERE
  (((TBL_JobTrack.StartTime)>TBL_TimeTrack.StartTime)
  And ((TBL_TimeTrack.EndTime)=0 Or (TBL_TimeTrack.EndTime)>TBL_JobTrack.StartTime));

The tables are joined by EmployeeID.

In my table, EndTimes default to 0, which means the task is still active.  The "Where" statement will select JobTrack start times that are greater than TimeTrack start times as long as the task is still active or less than TimeTrack end times.

"Mins" shows the number of minutes that a task begins (in JobTrack) after the employee began signed in.    
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now