October 3, 2013 at 5:02 am
Please give this a try- (I just revised the before query)
WITH cte_punches AS (
SELECT uidfirstname, uidlastname, reader, dtdate,
ROW_NUMBER() OVER (PARTITION BY uidfirstname, uidlastname ORDER BY dtdate, reader)
as punch_number FROM dbo.EMPTMS )
SELECT c1.reader, c1.uidfirstname, c1.uidlastname, c1.dtdate AS logDateTimeIn, c2.dtdate AS logDateTimeOut
FROM cte_punches c1
LEFT OUTER JOIN
(SELECT max(punch_number) punch_number, max(dtdate) dtdate, uidfirstname
FROM cte_punches
WHERE reader = 2
group by uidfirstname) c2
ON c1.uidfirstname = c2.uidfirstname
AND c1.punch_number = c2.punch_number
WHERE c1.reader = 2 and c2.dtdate is not null
October 3, 2013 at 6:17 am
CREATE TABLE EMPTMS
(Uidfirstname varchar (50),
Uidlastname varchar(50),
Reader int,
dtdate datetime);
INSERT INTO EMPTMS (uidfirstname, uidlastname, reader, dtdate) VALUES
('001','Alvin','1','2013-09-20 6:30:000 '),
('001','Alvin','2','2013-09-20 17:50:000 '),
('002','Rebecca','1','2013-09-20 22:10:000 '),
('003','Aliyah','1','2013-09-20 9:08:000 '),
('002','Rebecca','2','2013-09-21 7:30:000 '),
('001','Alvin','1','2013-09-21 6:30:000 '),
('001','Alvin','2','2013-09-21 17:50:000 '),
('002','Rebecca','1','2013-09-21 22:10:000 '),
('002','Rebecca','2','2013-09-22 7:30:000 '),
('002','Rebecca','2','2013-09-24 7:30:000 '),
('002','Rebecca','1','2013-09-25 22:10:000 '),
('002','Rebecca','2','2013-09-26 7:30:000 '),
('002','Rebecca','1','2013-09-28 22:10:000 ');
WITH FiddledData AS (
SELECT uidfirstname, uidlastname, reader, dtdate,
gn = reader - ROW_NUMBER() OVER(PARTITION BY uidfirstname,uidlastname ORDER BY dtDate)
FROM EMPTMS
),
ins AS (SELECT * FROM FiddledData WHERE reader = 1),
outs AS (SELECT * FROM FiddledData WHERE reader = 2)
SELECT
uidfirstname = ISNULL(i.uidfirstname, o.uidfirstname),
uidlastname = ISNULL(i.uidlastname, o.uidlastname),
DateIn = i.dtdate,
DateOut = o.dtdate,
MinutesWorked = DATEDIFF(minute,i.dtdate,o.dtdate)
FROM ins i
FULL OUTER JOIN outs o
ON o.Uidfirstname = i.Uidfirstname
AND o.Uidlastname = i.Uidlastname
AND o.gn = i.gn
ORDER BY ISNULL(i.uidfirstname, o.uidfirstname),
ISNULL(i.uidlastname, o.uidlastname),
ISNULL(i.dtdate,o.dtdate)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 3, 2013 at 7:55 am
sharon27 (10/2/2013)
Hi wolfkillj thank you for helping me find solution to my problem.i tried your script and it was ok, but i found something wrong. I tried to add 1 log for Rebecca which is same day of 2013-9-21 23:10:00.
this what i got
001Alvin 2013-09-21 06:30:00.0002013-09-21 17:50:00.000
002Rebecca2013-09-21 22:10:00.0002013-09-21 23:10:00.000
002Rebecca2013-09-21 23:10:00.0002013-09-22 07:30:00.000
which should be like this.
001Alvin 2013-09-21 06:30:00.0002013-09-21 17:50:00.000
002Rebecca2013-09-21 22:10:00.0002013-09-22 07:30:00.000
meaning no matter how many times they punch the reader still need to get the first in and the last out of the employee.
thanks.
Hi Sharon27,
I'm a little confused now about how your data works. Is it true that an "IN" punch always happens on Reader 1 and an "OUT" punch always happens on Reader 2? Which reader did Rebecca punch at 2013-09-21 23:10:00? And how does it work if an employee makes three punches - IN, IN, and OUT (or IN, OUT, and OUT)? How do you match an IN to an OUT to define a shift worked in such cases?
It seems to get much harder to define the beginning and end of a shift this way - you'd have to say that the beginning of a shift is the first IN following an OUT and the end of that shift is the last OUT preceding an IN (or the last OUT when there isn't a subsequent IN) or something like that. So if you had this series of punches, you'd break up the shifts as shown:
IN --- Shift starts ---
OUT --- Shift ends ---
IN --- Shift starts ---
IN
OUT --- Shift ends ---
IN --- Shift starts ---
OUT
OUT --- Shift ends ---
IN --- Shift starts ---
OUT --- Shift ends ---
You can't determine whether an IN punch actually marks the beginning of a shift without verifying that the preceding punch was an OUT, and you can't determine that an OUT punch actually marks the end of a shift without verifying that the next punch is an IN (or nonexistent).
Is this how your system actually works?
Jason Wolfkill
October 4, 2013 at 12:39 am
Hi wolfkillj sorry for confusing everyone. Our system has installed 2 readers at the Main door which everyone use punch as an access . thats is the reason why employee dont have only 1 or 2 punch. and yes i was thinking of shifting im trying to do that but unfortunately i dont now how to the comparison of shift schedule to the employees login and out.
thanks!
October 4, 2013 at 2:19 am
sharon27 (10/4/2013)
Hi wolfkillj sorry for confusing everyone. Our system has installed 2 readers at the Main door which everyone use punch as an access . thats is the reason why employee dont have only 1 or 2 punch. and yes i was thinking of shifting im trying to do that but unfortunately i dont now how to the comparison of shift schedule to the employees login and out.thanks!
So you have two readers which can record a stamp but you don't know if the stamp is for entry or exit, so the reader id is irrelevant?
Do you have a shift table?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 4, 2013 at 2:28 am
So you have two readers which can record a stamp but you don't know if the stamp is for entry or exit, so the reader id is irrelevant?
Do you have a shift table?[/quote]
Hi Chris. Yes we have 2 reader which save the record to the sql. i know the entry and exit of the reader. reader id is relevant, see im using that reader before to capture the first in and the last out of the employees using case.
no i dont have shift table. Luis Cazares and wolfkillj suggestion is the one that would work unfortunately i dont how to do this in sql.
thanks.
October 4, 2013 at 3:10 am
Hi Sharon,
reader 1 is IN, reader 2 is OUT?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 4, 2013 at 3:30 am
ChrisM@Work (10/4/2013)
Hi Sharon,reader 1 is IN, reader 2 is OUT?
yes sir..
October 4, 2013 at 3:32 am
sharon27 (10/4/2013)
ChrisM@Work (10/4/2013)
Hi Sharon,reader 1 is IN, reader 2 is OUT?
yes sir..
Phew! That's going to help so much. Did you try the code I posted yesterday?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 4, 2013 at 4:55 am
ChrisM@Work (10/4/2013)
sharon27 (10/4/2013)
ChrisM@Work (10/4/2013)
Hi Sharon,reader 1 is IN, reader 2 is OUT?
yes sir..
Phew! That's going to help so much. Did you try the code I posted yesterday?
Hey ChrisM@Work,
Your code doesn't address the issue that Sharon27 raised in response to my code - that of an employee punching the IN clock twice before punching the OUT clock or punching the OUT clock twice after punching the IN clock and these three punches adding up to one shift. Add this row to your sample data to see what I mean:
INSERT INTO EMPTMS (Uidfirstname, Uidlastname, reader, dtdate)
VALUES ('002','Rebecca','1','2013-09-21 23:10:000 ')
If I understood Sharon27 correctly, Rebecca should have just one row for the shift that started 2013-09-21 22:10:00 and ended 2013-09-22 07:30:00 even though she punched the clock (IN or OUT, doesn't matter) at 2013-09-21 23:10:00.
If I have time today, I'll try to work up a solution that addresses this issue.
Regards,
Jason
Jason Wolfkill
October 4, 2013 at 4:58 am
Ohhh...thanks Wolfie. My code addresses hanging IN or OUT punches but not in this manner. I'll have a play.
The data set I posted up includes your extras and a few of my own.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 4, 2013 at 6:35 am
Based in Chris' data
001Alvin12013-09-20 06:30:00.000
001Alvin22013-09-20 17:50:00.000
001Alvin12013-09-21 06:30:00.000
001Alvin22013-09-21 17:50:00.000
002Rebecca12013-09-20 22:10:00.000
002Rebecca22013-09-21 07:30:00.000
002Rebecca12013-09-21 22:10:00.000
002Rebecca12013-09-21 23:10:00.000
002Rebecca22013-09-22 07:30:00.000
002Rebecca22013-09-24 07:30:00.000
002Rebecca12013-09-25 22:10:00.000
002Rebecca22013-09-26 07:30:00.000
002Rebecca12013-09-28 22:10:00.000
003Aliyah12013-09-20 09:08:00.000
What should the output look like?
Far away is close at hand in the images of elsewhere.
Anon.
October 4, 2013 at 7:23 am
--------------------------------------------------------------------------------
-- Modified sample dataset
--------------------------------------------------------------------------------
DROP TABLE EMPTMS
CREATE TABLE EMPTMS
(Uidfirstname varchar (50),
Uidlastname varchar(50),
Reader int,
dtdate datetime);
SET DATEFORMAT YMD
INSERT INTO EMPTMS (uidfirstname, uidlastname, reader, dtdate) VALUES
('001','Alvin','1','2013-09-20 6:30:000 '),
('001','Alvin','2','2013-09-20 17:50:000 '),
('001','Alvin','1','2013-09-21 6:30:000 '),
('001','Alvin','2','2013-09-21 17:50:000 '),
('002','Rebecca','1','2013-09-20 22:05:000 '),
('002','Rebecca','2','2013-09-21 7:30:000 '),
('002','Rebecca','1','2013-09-21 22:10:000 '),
('002','Rebecca','1','2013-09-21 23:10:000 '), -- extra
('002','Rebecca','2','2013-09-22 7:30:000 '),
('002','Rebecca','2','2013-09-24 7:35:000 '), -- hanging
('002','Rebecca','1','2013-09-25 22:05:000 '),
('002','Rebecca','2','2013-09-26 7:30:000 '),
('002','Rebecca','1','2013-09-26 22:05:000 '),
('002','Rebecca','2','2013-09-27 7:30:000 '), -- extra
('002','Rebecca','2','2013-09-27 7:35:000 '),
('002','Rebecca','1','2013-09-28 22:10:000 '), -- hanging
('003','Aliyah','1','2013-09-20 9:08:000 '); -- hanging
--------------------------------------------------------------------------------
-- Proposed solution, v01
--------------------------------------------------------------------------------
WITH FiddledData AS (
SELECT e.uidfirstname, e.uidlastname, e.reader, e.dtdate,
gn = reader - ROW_NUMBER() OVER(PARTITION BY e.uidfirstname, e.uidlastname ORDER BY z.NewDate, y.marker)
-- z.NewDate, y.Marker --
FROM EMPTMS e
OUTER APPLY ( -- any other clock events within 2 hours? Use to modify grouping
SELECT TOP 1 i.dtdate
FROM EMPTMS i
WHERE i.Uidfirstname = e.Uidfirstname
AND i.Uidlastname = e.Uidlastname
AND i.dtdate <> e.dtdate
AND ABS(DATEDIFF(HOUR,i.dtdate,e.dtdate)) < 2 -- arbitrary figure
) x
CROSS APPLY (
SELECT Marker = CASE
WHEN x.dtDate IS NOT NULL THEN
CASE WHEN x.dtDate < e.dtDate THEN 1 ELSE 2 END
ELSE NULL END
) y
CROSS APPLY (
SELECT NewDate = CASE WHEN y.Marker = e.Reader THEN x.dtDate ELSE e.dtDate END
) z
),
ins AS (SELECT * FROM FiddledData WHERE reader = 1),
outs AS (SELECT * FROM FiddledData WHERE reader = 2)
SELECT
uidfirstname = ISNULL(i.uidfirstname, o.uidfirstname),
uidlastname = ISNULL(i.uidlastname, o.uidlastname),
DateIn = i.dtdate,
DateOut = o.dtdate,
MinutesWorked = DATEDIFF(minute,i.dtdate,o.dtdate)
FROM ins i
FULL OUTER JOIN outs o
ON o.Uidfirstname = i.Uidfirstname
AND o.Uidlastname = i.Uidlastname
AND o.gn = i.gn
ORDER BY ISNULL(i.uidfirstname, o.uidfirstname),
ISNULL(i.uidlastname, o.uidlastname),
ISNULL(i.dtdate,o.dtdate);
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
October 4, 2013 at 6:38 pm
David Burrows (10/4/2013)
Based in Chris' data
001Alvin12013-09-20 06:30:00.000
001Alvin22013-09-20 17:50:00.000
001Alvin12013-09-21 06:30:00.000
001Alvin22013-09-21 17:50:00.000
002Rebecca12013-09-20 22:10:00.000
002Rebecca22013-09-21 07:30:00.000
002Rebecca12013-09-21 22:10:00.000
002Rebecca12013-09-21 23:10:00.000
002Rebecca22013-09-22 07:30:00.000
002Rebecca22013-09-24 07:30:00.000
002Rebecca12013-09-25 22:10:00.000
002Rebecca22013-09-26 07:30:00.000
002Rebecca12013-09-28 22:10:00.000
003Aliyah12013-09-20 09:08:00.000
What should the output look like?
Hi the output should be like this :
001Alvin12013-09-20 06:30:00.000
001Alvin22013-09-20 17:50:00.000
001Alvin12013-09-21 06:30:00.000
001Alvin22013-09-21 17:50:00.000
002Rebecca12013-09-20 22:10:00.000
002Rebecca22013-09-21 07:30:00.000
002Rebecca12013-09-21 22:10:00.000
002Rebecca22013-09-22 07:30:00.000
002Rebecca22013-09-24 07:30:00.000
002Rebecca12013-09-25 22:10:00.000
002Rebecca22013-09-26 07:30:00.000
002Rebecca12013-09-28 22:10:00.000
003Aliyah12013-09-20 09:08:00.000
Sample Shift:
Alvin Shift 6:30 - 17:30
Rebecca Shift 22:00 - 7:00
Aliyah Shift 9:00 - 18:00
thanks...
October 4, 2013 at 6:50 pm
ChrisM@Work (10/4/2013)
---------------------------------------------------------------------------------- Modified sample dataset
--------------------------------------------------------------------------------
DROP TABLE EMPTMS
CREATE TABLE EMPTMS
(Uidfirstname varchar (50),
Uidlastname varchar(50),
Reader int,
dtdate datetime);
SET DATEFORMAT YMD
INSERT INTO EMPTMS (uidfirstname, uidlastname, reader, dtdate) VALUES
('001','Alvin','1','2013-09-20 6:30:000 '),
('001','Alvin','2','2013-09-20 17:50:000 '),
('001','Alvin','1','2013-09-21 6:30:000 '),
('001','Alvin','2','2013-09-21 17:50:000 '),
('002','Rebecca','1','2013-09-20 22:05:000 '),
('002','Rebecca','2','2013-09-21 7:30:000 '),
('002','Rebecca','1','2013-09-21 22:10:000 '),
('002','Rebecca','1','2013-09-21 23:10:000 '), -- extra
('002','Rebecca','2','2013-09-22 7:30:000 '),
('002','Rebecca','2','2013-09-24 7:35:000 '), -- hanging
('002','Rebecca','1','2013-09-25 22:05:000 '),
('002','Rebecca','2','2013-09-26 7:30:000 '),
('002','Rebecca','1','2013-09-26 22:05:000 '),
('002','Rebecca','2','2013-09-27 7:30:000 '), -- extra
('002','Rebecca','2','2013-09-27 7:35:000 '),
('002','Rebecca','1','2013-09-28 22:10:000 '), -- hanging
('003','Aliyah','1','2013-09-20 9:08:000 '); -- hanging
--------------------------------------------------------------------------------
-- Proposed solution, v01
--------------------------------------------------------------------------------
WITH FiddledData AS (
SELECT e.uidfirstname, e.uidlastname, e.reader, e.dtdate,
gn = reader - ROW_NUMBER() OVER(PARTITION BY e.uidfirstname, e.uidlastname ORDER BY z.NewDate, y.marker)
-- z.NewDate, y.Marker --
FROM EMPTMS e
OUTER APPLY ( -- any other clock events within 2 hours? Use to modify grouping
SELECT TOP 1 i.dtdate
FROM EMPTMS i
WHERE i.Uidfirstname = e.Uidfirstname
AND i.Uidlastname = e.Uidlastname
AND i.dtdate <> e.dtdate
AND ABS(DATEDIFF(HOUR,i.dtdate,e.dtdate)) < 2 -- arbitrary figure
) x
CROSS APPLY (
SELECT Marker = CASE
WHEN x.dtDate IS NOT NULL THEN
CASE WHEN x.dtDate < e.dtDate THEN 1 ELSE 2 END
ELSE NULL END
) y
CROSS APPLY (
SELECT NewDate = CASE WHEN y.Marker = e.Reader THEN x.dtDate ELSE e.dtDate END
) z
),
ins AS (SELECT * FROM FiddledData WHERE reader = 1),
outs AS (SELECT * FROM FiddledData WHERE reader = 2)
SELECT
uidfirstname = ISNULL(i.uidfirstname, o.uidfirstname),
uidlastname = ISNULL(i.uidlastname, o.uidlastname),
DateIn = i.dtdate,
DateOut = o.dtdate,
MinutesWorked = DATEDIFF(minute,i.dtdate,o.dtdate)
FROM ins i
FULL OUTER JOIN outs o
ON o.Uidfirstname = i.Uidfirstname
AND o.Uidlastname = i.Uidlastname
AND o.gn = i.gn
ORDER BY ISNULL(i.uidfirstname, o.uidfirstname),
ISNULL(i.uidlastname, o.uidlastname),
ISNULL(i.dtdate,o.dtdate);
Hi Chris first id like to thank you for the effort 🙂
this is the out put that i got:
001Alvin 2013-09-20 06:30:00.0002013-09-20 17:50:00.000680
001Alvin 2013-09-21 06:30:00.0002013-09-21 17:50:00.000680
002Rebecca2013-09-20 22:05:00.0002013-09-21 07:30:00.000565
002Rebecca2013-09-21 22:10:00.0002013-09-22 07:30:00.000560
002Rebecca2013-09-21 23:10:00.000NULL NULL
002RebeccaNULL 2013-09-24 07:35:00.000NULL
002Rebecca2013-09-25 22:05:00.0002013-09-26 07:30:00.000565
002Rebecca2013-09-26 22:05:00.0002013-09-27 07:35:00.000570
002RebeccaNULL 2013-09-27 07:30:00.000NULL ---CAN OMIT THIS
SINCE REBECCA OUT AT 7:35?
002Rebecca2013-09-28 22:10:00.000NULLNULL
003Aliyah2013-09-20 09:08:00.000NULLNULL
the code that you gave looks good, this means that i dont need to declare the shifting schedule for the employee?
thank you!
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply