[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Departmental Time Keeping SQL statement

Posted on 2011-04-18
13
Medium Priority
?
549 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:echobridge
  • 3
  • 3
  • 3
  • +3
13 Comments
 
LVL 7

Expert Comment

by:TheTull
ID: 35419482
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
 
LVL 58

Expert Comment

by:cyberkiwi
ID: 35419488
(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
 
LVL 16

Expert Comment

by:Sheils
ID: 35419500
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 58

Expert Comment

by:cyberkiwi
ID: 35419517
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
 
LVL 9

Expert Comment

by:Philippe Damerval
ID: 35419547
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
 
LVL 1

Author Comment

by:echobridge
ID: 35419708
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
 
LVL 1

Author Comment

by:echobridge
ID: 35419726
Oops, all my numbers are wrong,

output from query 1: 240
query 2:
Emp 1: 180
Emp 2: 60
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 35420198
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
 
LVL 61

Expert Comment

by:HainKurt
ID: 35420219
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
 
LVL 61

Expert Comment

by:HainKurt
ID: 35420229
if db is access you can write similar functions and use it in the query like the one I posted...
0
 
LVL 9

Expert Comment

by:Philippe Damerval
ID: 35420336
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
 
LVL 9

Accepted Solution

by:
Philippe Damerval earned 2000 total points
ID: 35420384
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
 
LVL 1

Author Closing Comment

by:echobridge
ID: 35425159
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

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question