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!