Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


I need help Please :(


I need help Please :(

Author
Message
sharon27
sharon27
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 89
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.
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8957 Visits: 19016
Hi Sharon,
reader 1 is IN, reader 2 is OUT?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
sharon27
sharon27
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 89
ChrisM@Work (10/4/2013)
Hi Sharon,
reader 1 is IN, reader 2 is OUT?


yes sir..
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8957 Visits: 19016
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?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
wolfkillj
wolfkillj
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1104 Visits: 2582
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
Blog: SQLSouth
Twitter: @SQLSouth
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8957 Visits: 19016
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.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
David Burrows
David Burrows
SSCertifiable
SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)SSCertifiable (8K reputation)

Group: General Forum Members
Points: 7963 Visits: 9411
Based in Chris' data


001 Alvin 1 2013-09-20 06:30:00.000
001 Alvin 2 2013-09-20 17:50:00.000
001 Alvin 1 2013-09-21 06:30:00.000
001 Alvin 2 2013-09-21 17:50:00.000
002 Rebecca 1 2013-09-20 22:10:00.000
002 Rebecca 2 2013-09-21 07:30:00.000
002 Rebecca 1 2013-09-21 22:10:00.000
002 Rebecca 1 2013-09-21 23:10:00.000
002 Rebecca 2 2013-09-22 07:30:00.000
002 Rebecca 2 2013-09-24 07:30:00.000
002 Rebecca 1 2013-09-25 22:10:00.000
002 Rebecca 2 2013-09-26 07:30:00.000
002 Rebecca 1 2013-09-28 22:10:00.000
003 Aliyah 1 2013-09-20 09:08:00.000


What should the output look like?


Far away is close at hand in the images of elsewhere.

Anon.


ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)SSCrazy Eights (9K reputation)

Group: General Forum Members
Points: 8957 Visits: 19016
--------------------------------------------------------------------------------
-- 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);




“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

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
Exploring Recursive CTEs by Example Dwain Camps
sharon27
sharon27
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 89
David Burrows (10/4/2013)
Based in Chris' data


001 Alvin 1 2013-09-20 06:30:00.000
001 Alvin 2 2013-09-20 17:50:00.000
001 Alvin 1 2013-09-21 06:30:00.000
001 Alvin 2 2013-09-21 17:50:00.000
002 Rebecca 1 2013-09-20 22:10:00.000
002 Rebecca 2 2013-09-21 07:30:00.000
002 Rebecca 1 2013-09-21 22:10:00.000
002 Rebecca 1 2013-09-21 23:10:00.000
002 Rebecca 2 2013-09-22 07:30:00.000
002 Rebecca 2 2013-09-24 07:30:00.000
002 Rebecca 1 2013-09-25 22:10:00.000
002 Rebecca 2 2013-09-26 07:30:00.000
002 Rebecca 1 2013-09-28 22:10:00.000
003 Aliyah 1 2013-09-20 09:08:00.000


What should the output look like?



Hi the output should be like this :


001 Alvin 1 2013-09-20 06:30:00.000
001 Alvin 2 2013-09-20 17:50:00.000
001 Alvin 1 2013-09-21 06:30:00.000
001 Alvin 2 2013-09-21 17:50:00.000
002 Rebecca 1 2013-09-20 22:10:00.000
002 Rebecca 2 2013-09-21 07:30:00.000
002 Rebecca 1 2013-09-21 22:10:00.000
002 Rebecca 2 2013-09-22 07:30:00.000
002 Rebecca 2 2013-09-24 07:30:00.000
002 Rebecca 1 2013-09-25 22:10:00.000
002 Rebecca 2 2013-09-26 07:30:00.000
002 Rebecca 1 2013-09-28 22:10:00.000
003 Aliyah 1 2013-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...
sharon27
sharon27
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 89
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 Smile
this is the out put that i got:



001 Alvin 2013-09-20 06:30:00.000 2013-09-20 17:50:00.000 680
001 Alvin 2013-09-21 06:30:00.000 2013-09-21 17:50:00.000 680
002 Rebecca 2013-09-20 22:05:00.000 2013-09-21 07:30:00.000 565
002 Rebecca 2013-09-21 22:10:00.000 2013-09-22 07:30:00.000 560
002 Rebecca 2013-09-21 23:10:00.000 NULL NULL
002 Rebecca NULL 2013-09-24 07:35:00.000 NULL
002 Rebecca 2013-09-25 22:05:00.000 2013-09-26 07:30:00.000 565
002 Rebecca 2013-09-26 22:05:00.000 2013-09-27 07:35:00.000 570
002 Rebecca NULL 2013-09-27 07:30:00.000 NULL ---CAN OMIT THIS
SINCE REBECCA OUT AT 7:35?
002 Rebecca 2013-09-28 22:10:00.000 NULL NULL
003 Aliyah 2013-09-20 09:08:00.000 NULL NULL



the code that you gave looks good, this means that i dont need to declare the shifting schedule for the employee?

thank you!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search