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.
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.
(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]
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]
Try this
Select tblDepartment.deptID,sum(t
FROm tblDepartment INER JOIN tblTimeLog on tblDepartment.deptID=tblTi
GROUP BY tblDepartment.deptID
Select tblDepartment.deptID,sum(t
FROm tblDepartment INER JOIN tblTimeLog on tblDepartment.deptID=tblTi
GROUP BY tblDepartment.deptID
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]
(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]
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
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
ASKER
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.
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.
ASKER
Oops, all my numbers are wrong,
output from query 1: 240
query 2:
Emp 1: 180
Emp 2: 60
output from query 1: 240
query 2:
Emp 1: 180
Emp 2: 60
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
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
if db is access you can write similar functions and use it in the query like the one I posted...
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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(InTim
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
SELECT EmployeeID, LogID, SUM(DATEDIFF("n",IIF(InTim
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
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