Employee Time Clock

  • John C. Marx

    SSC-Addicted

    Points: 439

    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

  • Kathi Kellenberger

    SSChampion

    Points: 11811

    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

  • John C. Marx

    SSC-Addicted

    Points: 439

    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

  • Kathi Kellenberger

    SSChampion

    Points: 11811

    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

  • Ninja’s_RGR’us

    SSC Guru

    Points: 294069

    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.

  • henricsanyu

    SSC Rookie

    Points: 31

    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   | 1

    The 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.

  • pietlinden

    SSC Guru

    Points: 62317

    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
    );
    GO

    INSERT 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.

  • mikek4030

    Newbie

    Points: 3

    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 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply