Departmental Time Keeping SQL statement

What I have are employee sign in/sign out logs in and out of departments in an access database.

Very simple data: logID, employeeID, deptID, InTime, OutTime

What was requested is the ability to input a department, start and end times and output the number of minutes worked within that time period in that department. They would like to see total minutes worked and also who worked them (two separate reports).

I am hoping that this should be able to be accomplished by a single SQL query for each report.
LVL 1
echobridgeSenior Software EngineerAsked:
Who is Participating?
 
Philippe DamervalSenior Analyst ProgrammerCommented:
My mistake - I did not see the need for inputting criteria. In English these criteria would be expressed as "Give me total minutes worked for a given department between the hours of X and Y".
For this you need to : find every record for the department you want, where InTime is on or after the X value OR outTime is on or before the Y value, and calculate the difference between the latter of X or the In time and the earlier of Y and the out time.

Here are the queries you need:

q1: SELECT DeptID, Sum(DATEDIFF("n", Iif([InTime] >= [X], [InTime], [X]), Iif([OutTime <= [Y], [OutTime], [Y]))) AS MinsWorked
FROM mytable
WHERE DeptID = [Department ID?]
AND ((InTime >= [X]) OR (OutTime <= [Y]))
GROUP BY DeptID

q2; SELECT employeeID, DeptID, Sum(DATEDIFF("n", Iif([InTime] >= [X], [InTime], [X]), Iif([OutTime <= [Y], [OutTime], [Y]))) AS MinsWorked
FROM mytable
WHERE DeptID = [Department ID?]
AND ((InTime >= [X]) OR (OutTime <= [Y]))
GROUP BY EmployeeID, DeptID

HTH

Philippe
0
 
TheTullCommented:
Have you tried using DateDiff?

Here is an example: select datediff(mi, intime, outtime)

The mi parameter is used to get back minutes.

http://msdn.microsoft.com/en-us/library/aa258269%28v=sql.80%29.aspx
0
 
cyberkiwiCommented:
(2) who worked them - Query to get distinct names

select distinct employeeID
from signinout
where InTime <= [End Time]
and [Start Time] <= OutTime
and deptIT = [Department ID]

The department ID will be cryptic, so it may be better to join to a department table if they know the name

select distinct employeeID
from signinout
inner join department on (department.id = signinout.deptID)
where InTime <= [End Time]
and [Start Time] <= OutTime
and department.Name = [Department Name]

(1) time worked - per employee

select employeeID, SUM(
   iif([End Time] < OutTime, [End Time], OutTime)
   -
   iif(InTime > [Start Time], InTime, [Start Time])) as TotalMinutes
from signinout
where InTime <= [End Time]
and [Start Time] <= OutTime
and deptIT = [Department ID]
group by employeeID

(1b) time worked - overall

select SUM(
   iif([End Time] < OutTime, [End Time], OutTime)
   -
   iif(InTime > [Start Time], InTime, [Start Time])) as TotalMinutes
from signinout
where InTime <= [End Time]
and [Start Time] <= OutTime
and deptIT = [Department ID]
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
SheilsCommented:
Try this

Select tblDepartment.deptID,sum(tblTimeLog.OutTime - tblTimeLog.InTime) AS TIME_WORKED
FROm tblDepartment INER JOIN tblTimeLog on tblDepartment.deptID=tblTimeLog.deptID
GROUP BY tblDepartment.deptID

0
 
cyberkiwiCommented:
Correction for the times in minutes (not days)

(1) time worked - per employee

select employeeID, SUM(TimeDiff("n",
   iif(InTime > [Start Time], InTime, [Start Time]),
   iif([End Time] < OutTime, [End Time], OutTime))) as TotalMinutes
from signinout
where InTime <= [End Time]
and [Start Time] <= OutTime
and deptIT = [Department ID]
group by employeeID

(1b) time worked - overall

select SUM(TimeDiff("n",
   iif(InTime > [Start Time], InTime, [Start Time]),
   iif([End Time] < OutTime, [End Time], OutTime))) as TotalMinutes
from signinout
where InTime <= [End Time]
and [Start Time] <= OutTime
and deptIT = [Department ID]
0
 
Philippe DamervalSenior Analyst ProgrammerCommented:
Hi Echobridge,
It seems from your request that you are looking for the MS Access formula to calculate the number of minutes between two date/time values. If so, this is it below:

DateDiff("n", [InTime], [OutTime])

In SQL query syntax it tends to vary a bit depending on which DBMS you are working with. For instance, with SQL server it is this:

DATEDIFF ( minute , [InTime] , [OutTime] )

In order to get both total minutes and minutes by employee, you could actually simply have one report for both, with the total minutes for each employee and the total of totals at the bottom.

The SQL statement for that would become (in MS Access):

SELECT employeeID, DeptID, Sum(DATEDIFF("n", [InTime], [OutTime])) AS MinsWorked
FROM mytable
GROUP BY employeeID, DeptID

In the Report footer simply put a field with a formula of Sum(MinsWorked) and it will calculate the correct total for you.

Let me know if you have any more questions.

Philippe
0
 
echobridgeSenior Software EngineerAuthor Commented:
Ill try being a little more specific.

I have records that state:
Emp 1
Dept 2
InTime 8:00am
OutTime: 4:00pm

Emp 2
Dept 2
InTime 2:00pm
OutTime: 3:00pm

They want to input:
Dept 2
From: 1:00pm
To: 4:00pm

and output from query 1: 300 minutes
query 2:
Emp 1 worked 240 mins
Emp 2 worked 60 mins

This will all be managed through an asp.net page. I apologize for the confusion.
0
 
echobridgeSenior Software EngineerAuthor Commented:
Oops, all my numbers are wrong,

output from query 1: 240
query 2:
Emp 1: 180
Emp 2: 60
0
 
HainKurtSr. System AnalystCommented:
have a look at this
/*
create function GetMinute(@dt datetime) returns int as
begin
  return datepart(hour, @dt)*60+datepart(minute, @dt)
end;

create function GetMin(@a int, @b int) returns int as 
begin
  return (case when @a<@b then @a else @b end);
end;

create function GetMax(@a int, @b int) returns int as 
begin
  return (case when @a>@b then @a else @b end);
end;
*/
declare @sd int = 13*60+30;
declare @ed int = 16*60+15;

with r as (
select 'Emp1' Emp, 'Dept2' Dep, convert(datetime, '04/18/2011 12:30', 101) FromDT, convert(datetime, '04/18/2011 17:45', 101) ToDT
union
select 'Emp2' Emp, 'Dept2' Dep, convert(datetime, '04/18/2011 14:15', 101) FromDT, convert(datetime, '04/18/2011 16:30', 101) ToDT
union
select 'Emp3' Emp, 'Dept2' Dep, convert(datetime, '04/18/2011 15:20', 101) FromDT, convert(datetime, '04/18/2011 18:15', 101) ToDT
union
select 'Emp4' Emp, 'Dept2' Dep, convert(datetime, '04/18/2011 11:45', 101) FromDT, convert(datetime, '04/18/2011 18:45', 101) ToDT
)
select *, dbo.GetMin(@ed,dbo.GetMinute(ToDT)) - dbo.GetMax(@sd,dbo.GetMinute(FromDT)) WorkedMinutes
--, dbo.GetMinute(FromDT), dbo.GetMinute(ToDT), @sd, @ed 
from r
where 
dbo.GetMinute(FromDT) between @sd and @ed 
or
dbo.GetMinute(ToDT) between @sd and @ed
or
dbo.GetMinute(FromDT) < @sd and dbo.GetMinute(ToDT) > @ed 

Emp	Dep	FromDT	ToDT	WorkedMinutes
Emp1	Dept2	2011-04-18 12:30:00.000	2011-04-18 17:45:00.000	165
Emp2	Dept2	2011-04-18 14:15:00.000	2011-04-18 16:30:00.000	120
Emp3	Dept2	2011-04-18 15:20:00.000	2011-04-18 18:15:00.000	55
Emp4	Dept2	2011-04-18 11:45:00.000	2011-04-18 18:45:00.000	165

Open in new window

0
 
HainKurtSr. System AnalystCommented:
oops, I guess we need parantheses around last condition


select *, dbo.GetMin(@ed,dbo.GetMinute(ToDT)) - dbo.GetMax(@sd,dbo.GetMinute(FromDT)) WorkedMinutes
, dbo.GetMinute(FromDT) FromMinute, dbo.GetMinute(ToDT) ToMinute, @sd StartMinute, @ed EndMinute
from r
where 
(dbo.GetMinute(FromDT) between @sd and @ed)
or
(dbo.GetMinute(ToDT) between @sd and @ed)
or
(dbo.GetMinute(FromDT) < @sd and dbo.GetMinute(ToDT) > @ed) 

Emp	Dep	FromDT	ToDT	WorkedMinutes	FromMinute	ToMinute	StartMinute	EndMinute
Emp1	Dept2	2011-04-18 12:30:00.000	2011-04-18 17:45:00.000	165	750	1065	810	975
Emp2	Dept2	2011-04-18 14:15:00.000	2011-04-18 16:30:00.000	120	855	990	810	975
Emp3	Dept2	2011-04-18 15:20:00.000	2011-04-18 18:15:00.000	55	920	1095	810	975
Emp4	Dept2	2011-04-18 11:45:00.000	2011-04-18 18:45:00.000	165	705	1125	810	975

Open in new window

0
 
HainKurtSr. System AnalystCommented:
if db is access you can write similar functions and use it in the query like the one I posted...
0
 
Philippe DamervalSenior Analyst ProgrammerCommented:
If DB is Access simply create q1 and q2 as follows:

q1: SELECT Sum(DATEDIFF("n", [InTime], [OutTime])) AS MinsWorked
FROM mytable

q2: SELECT employeeID, DeptID, Sum(DATEDIFF("n", [InTime], [OutTime])) AS MinsWorked
FROM mytable
GROUP BY employeeID, DeptID

HTH

Philippe
0
 
echobridgeSenior Software EngineerAuthor Commented:
Had to change the WHERE statement to allow for any way that an employee was logged in (logged in before requested start and out before end, in after and out before, ect.)

SELECT EmployeeID, LogID, SUM(DATEDIFF("n",IIF(InTime<@Begin,@Begin,InTime),IIF(OutTime>@End,@End,OutTime)))
FROM Login
WHERE ((InTime<=@Begin AND OutTime>=@Begin) OR (InTime<=@End AND OutTime >= @End) OR (InTime>=@Begin AND OutTime<=@End)) AND DeptID = @Dept
GROUP BY EmployeeID, LogID
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.