SQL query for First swipe in and Last Swipe Out for each day for each pay period

Question

SQL query for First swipe in and Last Swipe Out for each day for each pay period

adisql

SSCrazy

Points: 2893
More actions
December 31, 2020 at 8:50 pm

#3826003

Hi,
"EMPLOYEE" table has EmpName,EMPID .
"SWIPE" table has SwipeTime_UTC,Swipe_location,EmpID .
Note:Swipein and swipeout locations are different. SwipeTime_UTC is UTC time format.
Looking for SQL query to generate the first swipe in and last swipe out for each employee on each day for given pay period.(Display time should be in EST).
Any suggestions pls.
Thanks.

September 19, 2021 at 7:39 pm

This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

So, what have you tried to solve this problem? You really didn't give much to work with and without the DDL for the tables, and some sample data to work with I don't think many may be willing to help.

Please remember that we are volunteers and we are giving our free time to help, it would be appropriate for you to help us help you by providing as much as possible to make it easier for us to do just that.

ScottPletcher SSC Guru Points: 101084 More actions · Answer 2

Agreed. I don't even see how to distinguish between a clock-in and a clock-out.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Phil Parkin SSC Guru Points: 246948 More actions · Answer 3

adisql wrote:

Any suggestions pls.
Thanks.

Yes. You've been here long enough to know this.

Please provide sample DDL, INSERT statements with test data and desired output based on the data provided.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Jonathan AC Roberts SSCoach Points: 18339 More actions · Answer 4

CREATE TABLE #Employee
(
    EmpId int NOT NULL,
    EmpName nvarchar(20)  NOT NULL
);
GO
CREATE TABLE #Swipe
(
    SwipeTime_UTC datetime  NOT NULL,
    Swipe_location nvarchar(40)  NOT NULL,
    EmpId int  NOT NULL
);
GO
SELECT e.EmpId, 
       e.EmpName,
       CONVERT(date, s.SwipeTime_UTC) SwipeDate,
       MIN(s.SwipeTime_UTC) SwipeIn,
       MAX(s.SwipeTime_UTC) SwipeOut
  FROM #Employee e
 INNER JOIN #Swipe s
         ON s.EmpId = e.EmpId
 GROUP BY e.EmpId, e.EmpName, CONVERT(date, s.SwipeTime_UTC)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

And what if they swipe in and out multiple times in a day or across dates, in on Monday and out on Tuesday?

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 6

First, would you explain why you didn't post any DDL. This is been basic netiquette for over 30 years on SQL forms. Why are you exempt?

Second, your narrative is wrong. Please download a copy of temporal queries in SQL by Rick Snodgrass. It's available as a free PDF from the University of Arizona website. What you should have posted would look like this

Third table set name should be a world but you told us that you have only one! Let's try this:

CREATE TABLE Pesonnel

(emp_id CHAR(9) NOT NULL PRIMARY KEY,

emp_name VARCHAR(35) NOT NULL);

please notice that a table by definition, must have a key. This is one of the many many reasons that we want DDL! Now let's go on to the second table

CREATE TABLE Swipes

(emp_id CHAR(9) NOT NULL

REFERENCES Personnel ON DELETE CASCADE,

swipe_in_timestamp DATETIME2(7) NOT NULL,

swipe_out_timestamp DATETIME2(7),

CHECK (swipe_in_timestamp < swipe_out_timestamp),

PRIMARY KEY (emp_id, swipe_in_timestamp));

>> Note:Swipein and swipeout locations are different. SwipeTime_UTC is UTC time format. <<

No, they are the start and end of the temporal interval. This is the ISO temporal model and has been for decades. If nobody has swiped out, this is represented with a null. Again, get that Snodgrass book!

By putting both the in and out times in the same column. You've created a "automobiles, squids, and Lady Gaga table." A single column has two distinct kinds of data elements and it, in violation of first normal form.

Please post DDL and follow ANSI/ISO standards when asking for help.

Ross McMicken SSCarpal Tunnel Points: 4435 More actions · Answer 7

Joe, that's not how the real world works. Most of the access control data I've seen sends the database a swipe time, a direction, and the number of the access device used, which would later be correlated to an employee number via another table. It would be very unusual to have the access control system itself take care of matching the swipes, when that's the role of the database and queries. This is especially true if the system also collects all of the internal swipes as employees move around the office, assuming swipes are required.

ScottPletcher SSC Guru Points: 101084 More actions · Answer 8

I've never seen a time system that didn't use separate system entries to record check in / check out. If fact, I don't see how else they could work, for a number of reasons.

The system needs to be FAST and thus as independent as possible. I.e., it needs to be able to check people out even if the original check-in record is inaccessible for some reason (check out records can be stored/cached locally for a while if needed). If, instead, the system had to go find the check-in record before it could do a check out, that could be a massive bottleneck and endless series of problems.

This is a financial issue as well. Some unions -- such as at some mills owned by International Paper (I worked at IP for over a decade) -- have contracts that specify they get paid for any excess time they wait to check out. Beyond a certain time, they got paid OVERTIME for waiting for the system to check them out. So, yeah, what's most important is that they can check out, the matching up to a check in can happen separately from that.

Joe is often all theory. The glass tower design, where everything always works perfectly. But the real world is never like that.

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".