Here's a handy article about How to Post to Get the Best Help.
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. Probably long-winded (I'm sure someone will shred it). 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. Our new generation of time card electronic time clocks are manufactured for long lasting durability with multi-features and capabilities including employee punch in, as well as document date and time. Lathem employee punch time card clocks are ideal for any size business and designed to stand up to the harshest environments. Since 1919 Lathem time clocks have set the standard for quality, durability and accuracy. LAG(punch_time,1) OVER (PARTITION BY employee_id ORDER BY punch_time ASC) END Affordable employee punch clocks to meetthe needs for any type of business. Designed for easy, efficient use, the WaspTime RFID. , DATEDIFF(minute, p.PrevPunch, p.punch_time) AS MinsWorked Improve productivity and eliminate costly payroll errors with the WaspTime RFID solution. INSERT INTO punches (employee_id, punch_time, punch_type) VALUES I added a few extra records to make sure my LAG wasn't doing something silly. Here's a script to create the table and populate it with some data. 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 can do Select XXX from dbo.Punch where FkEmploye = and DateFin is null. 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.īut the query to check if he's in is much simpler.
NOT NULL CONSTRAINT DEFAULT (getdate()),ĬONSTRAINT PRIMARY KEY CLUSTEREDĬONSTRAINT FOREIGN KEYĬONSTRAINT CHECK ( < or is null)įrom then I have a pretty straight forward PunchEmloye Stored Proc. Time Clock - NGTeco Time Clocks for Employees Small Business with Face, Finger Scan, RFID and PIN Punching in One, Office Time Card Machine Automatic Punch with APP for iOS Android (0 Monthly Fee) 18999. However My table design is somewhat different that yours :ĬREATE TABLE (/* ya I now know better than that*/ 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). WHERE (o.punchType = 0) AND (i.punchType = 1) AND (o.punchTime = SELECT i.employeeID, e.firstName, e.lastName, i.punchTime AS, o.punchTime AS, CONVERT(decimal, DATEDIFF(mi, i.punchTime,Ĭlock o ON i.employeeID = o.employeeID INNER JOIN Here are the tables I used: create table employee (id int, firstName varchar(20), lastName varchar(20)) goĬreate table clock (employeeID int, punchTime dateTime, punchType bit) go