Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

I need help Please :( Expand / Collapse
Author
Message
Posted Friday, October 4, 2013 2:28 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 1, 2014 1:22 AM
Points: 12, Visits: 43

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.
Post #1501460
Posted Friday, October 4, 2013 3:10 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 6,750, Visits: 13,896
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
Post #1501477
Posted Friday, October 4, 2013 3:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 1, 2014 1:22 AM
Points: 12, Visits: 43
ChrisM@Work (10/4/2013)
Hi Sharon,
reader 1 is IN, reader 2 is OUT?


yes sir..
Post #1501492
Posted Friday, October 4, 2013 3:32 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 6,750, Visits: 13,896
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
Post #1501493
Posted Friday, October 4, 2013 4:55 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 9:06 AM
Points: 1,061, Visits: 2,565
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
Post #1501525
Posted Friday, October 4, 2013 4:58 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 6,750, Visits: 13,896
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
Post #1501528
Posted Friday, October 4, 2013 6:35 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 6,917, Visits: 6,994
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.

Post #1501559
Posted Friday, October 4, 2013 7:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 6,750, Visits: 13,896
--------------------------------------------------------------------------------
-- 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
Post #1501593
Posted Friday, October 4, 2013 6:38 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 1, 2014 1:22 AM
Points: 12, Visits: 43
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...
Post #1501807
Posted Friday, October 4, 2013 6:50 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 1, 2014 1:22 AM
Points: 12, Visits: 43
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:


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!
Post #1501808
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse