Employee Time Clock
-
I'm working on our first "complex" database. I have two tables. One with for an Employee database which has an ID field, Employee ID, First and Last names. The second table stores the time for when an employee came in or out. This table has an ID field, Employee ID, Punch Time (date time field) and Punch Type (0 for Out and 1 for In).
I want to create a simple query that shows the following:
Employee ID, First Name, Last Name, In Punch, Out Punch, Difference (hours worked)
How can I achieve this?
Thank you, John
John C Marx -
Here are the tables I used:
- create table employee (id int, firstName varchar(20), lastName varchar(20))
- go
create table clock (employeeID int, punchTime dateTime, punchType bit)
- go
Here's my test data:
id firstName lastName
1 John Smith
2 Harry Jones
3 Mary Johnson
employeeID punchTime punchType
1 1/2/2004 8:00:00 AM 1
1 1/2/2004 5:00:00 PM 0
1 1/3/2004 8:03:00 AM 1
1 1/3/2004 5:15:00 PM 0
2 1/3/2004 7:15:00 AM 1
2 1/3/2004 4:30:00 PM 0
3 1/2/2004 9:30:00 AM 1
3 1/2/2004 6:15:00 PM 0
3 1/5/2004 7:30:00 AM 1
3 1/5/2004 7:30:00 PM 0
Here is the query that worked for me:
SELECT i.employeeID, e.firstName, e.lastName, i.punchTime AS [In Punch], o.punchTime AS [Out Punch], CONVERT(decimal, DATEDIFF(mi, i.punchTime,
o.punchTime)) / 60 AS [Hours Worked]
FROM clock i INNER JOIN
clock o ON i.employeeID = o.employeeID INNER JOIN
employee e ON e.id = i.employeeID
WHERE (o.punchType = 0) AND (i.punchType = 1) AND (o.punchTime =
(SELECT MIN(punchtime)
FROM clock
WHERE employeeId = i.employeeID AND punchtime > i.punchtime and punchtype = 0))
employeeID firstName lastName In Punch Out Punch Hours Worked
1 John Smith 1/2/2004 8:00:00 AM 1/2/2004 5:00:00 PM 9
1 John Smith 1/3/2004 8:03:00 AM 1/3/2004 5:15:00 PM 9.2
2 Harry Jones 1/3/2004 7:15:00 AM 1/3/2004 4:30:00 PM 9.25
3 Mary Johnson 1/2/2004 9:30:00 AM 1/2/2004 6:15:00 PM 8.75
3 Mary Johnson 1/5/2004 7:30:00 AM 1/5/2004 7:30:00 PM 12
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Kathi,
That's awesome!
I just ran it on our initial run of 10 employee punches over the weekend and it showed the in/out of the employees. Many of the employees didn't have an out punch so out of the 10 punches only 3 had out punches. Is there a way to show the in punch with no out punch so that we can tell if there's punches missing?
Thanks for the great start. Time to head to Barnes & Noble and get a book on SQL now.
John
John C Marx -
Here is a query to find records without an out punch:
select employeeID, punchtime from clock i where
punchtype = 1 and not exists(select * from clock where punchtype = 0 and employeeid = i.employeeid and punchtime between i.punchtime and isnull((select min(punchtime) from clock where punchtype = 1 and punchtime > i.punchtime and employeeid = i.employeeid),'1/1/2025'))
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Just another idea here. I've created a similar system to count how much time I spend at work (I don't call it a punch because I can actually change the underlying data and I'm the only one using but it's basically a punch). However My table design is somewhat different that yours :
CREATE TABLE [Punch] (/* ya I now know better than that*/
[PkPunch] [int] IDENTITY (1, 1) NOT NULL ,
[DateDebut] [datetime] NOT NULL CONSTRAINT [DF_Punch_DatePunch] DEFAULT (getdate()),
[DateFin] [datetime] NULL ,
[FkEmploye] [int] NOT NULL ,
[FkSemaine] [int] NOT NULL ,
CONSTRAINT [PK_Punch] PRIMARY KEY CLUSTERED
(
[PkPunch]
) ON [PRIMARY] ,
CONSTRAINT [FK_Punch_DateDebutSemaine] FOREIGN KEY
(
[FkSemaine]
) REFERENCES [DatesDebutsSemaines] (
[PkDateDebutSemaine]
),
CONSTRAINT [FK_Punch_FkUsers] FOREIGN KEY
(
[FkEmploye]
) REFERENCES [Users] (
[PkUser]
),
CONSTRAINT [CK_Punch_Dates] CHECK ([DateDebut] < [DateFin] or [DateFin] is null)
) ON [PRIMARY]
GO
From then I have a pretty straight forward PunchEmloye Stored Proc. It goes something like this :
Validate input
set 2-3 variables
check if employe is punched in or out
if he's in and it's been more than X hours, I do an automatic punch out for 12 hours of work and punch him in again.
else I punch him out.
or else I punch him in.
But the query to check if he's in is much simpler. I can do Select XXX from dbo.Punch where FkEmploye = @FkEmploye and DateFin is null. No join no subquery, nothing fancy here.
I was wondering if anybody else was using a system similar to this one? Any design advice on pros and cons would be welcome from anyone.
-
I have a table in the format below
employee_id | punch_time | punch_type
528 | 2018-04-30 08:20:30 | 0
528 | 2018-04-30 06:20:30 | 1
522 | 2018-04-30 08:25:30 | 0
522 | 2018-04-30 05:20:30 | 1The punch_type field stores both punch in and punch_out time
I want a query to create something like:
employee_id | date | punch_in(time) | punch_out (time)
528 | 2018-04-30 | 08:20:30 | 06:20:30
522 | 2018-04-30 | 08:25:30 | 05:20:30
Note: There are so many records for different dates.
Please help me out. -
Henric,
Nice post for a rookie. Welcome to SSC. Here's a script to create the table and populate it with some data... I added a few extra records to make sure my LAG wasn't doing something silly...CREATE TABLE punches (
employee_id int,
punch_time datetime,
punch_type bit
);
GOINSERT INTO punches (employee_id, punch_time, punch_type) VALUES
(522, '2018-04-30 08:25:30', 0),
(522, '2018-04-30 17:20:30', 1),
(522, '2018-05-01 06:00:00', 0),
(522, '2018-05-01 19:00:00', 1),
(528, '2018-04-30 08:20:30', 0),
(528, '2018-04-30 16:20:30', 1),
(528, '2018-05-01 07:00:00', 0),
(528, '2018-05-01 16:00:00', 1);SELECT p.employee_id
, p.PrevPunch AS clockIn
, p.punch_time AS clockOut
, DATEDIFF(minute, p.PrevPunch, p.punch_time) AS MinsWorked
FROM
( SELECT tc.employee_id
, tc.punch_time
, tc.PrevPunch
, tc.punch_type
FROM
(SELECT employee_id
, punch_time
, CASE WHEN punch_type = 1 THEN
LAG(punch_time,1) OVER (PARTITION BY employee_id ORDER BY punch_time ASC) END
AS PrevPunch
, punch_type
FROM punches) tc
WHERE tc.punch_type = 1 ) p;Now that I've provided a CREATE TABLE script and an INSERT script, the only thing left is to run the code and then write a query to answer the question...
SELECT p.employee_id
, p.PrevPunch AS clockIn
, p.punch_time AS clockOut
, DATEDIFF(minute, p.PrevPunch, p.punch_time) AS MinsWorked
FROM
( SELECT tc.employee_id
, tc.punch_time
, tc.PrevPunch
, tc.punch_type
FROM
(SELECT employee_id
, punch_time
, CASE WHEN punch_type = 1 THEN
LAG(punch_time,1) OVER (PARTITION BY employee_id ORDER BY punch_time ASC) END
AS PrevPunch
, punch_type
FROM punches) tc
WHERE tc.punch_type = 1 ) p;Probably long-winded (I'm sure someone will shred it)...
Here's what I'm doing... Since 1 is a "punch out", I'm filtering for those, and then I'm using LAG() to read the previous value for the same employee to get the punch_in value.Then it's just date math.
Here's a handy article about How to Post to Get the Best Help... I would consider it essential reading for anyone new here. -
Hi Kathi,
It's probably been awhile since you visited this thread, but i'll ask my question anyway.
First thank you for your post, it is of great value.
I have a situation that there are times when someone will punch in on a Tuesday lets say, and then punch out on a Wednesday.
Is there away to modify your pair matching code to allow overnight employees?
Thanks Again
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply