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 Tuesday, October 1, 2013 8:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 1, 2014 1:22 AM
Points: 12, Visits: 43
Hi SSCertifiable thanks for the comment here is the query;


CREATE TABLE EMPTMS
(Uidfirstname varchar (50),
Uid1lastname varchar(50),
Reader int,
dtdate datetime
);


INSERT INTO EMPTMS
(uidfirstname,uidlastname,reader,dtdate)
VALUES ('001','Alvin','1','2013-09-20 6:30:000 ')

INSERT INTO EMPTMS
(uidfirstname,uidlastname,reader,dtdate)
VALUES ('001','Alvin','2','2013-09-20 17:50:000 ')

INSERT INTO EMPTMS
(uidfirstname,uidlastname,reader,dtdate)
VALUES ('002','Rebecca','1','2013-09-20 22:10:000 ')


INSERT INTO EMPTMS
(uidfirstname,uidlastname,reader,dtdate)
VALUES ('003','Aliyah','1','2013-09-20 9:08:000 ')


INSERT INTO EMPTMS
(uidfirstname,uidlastname,reader,dtdate)
VALUES ('002','Rebecca','2','2013-09-21 7:30:000 ')



*VIEWS

CREATE VIEW AS TMS
(Select uidfirstname as empid, uidlastname
as empname, convert ( varchar,dtdate,101 ) as logdate,
, case when reader = 1 then
Convert ( varchar,dtdate,108 )
Else null end as timein,
case when reader =2 then
Convert (varchar,dtdate,108)
Else nullend as timeout

From emptms
Group by uidfirstname,uidlastname,dtdate,
reader
)


for final result query,

SELECT EMPID,EMPNAME,
LOGDATE, MIN(TIMEIN),
MAX(TIMEOUT)
FROM TMS


basically that is the table, i create view and then create query
for the output.


thanks.
Post #1500458
Posted Tuesday, October 1, 2013 10:06 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 12:50 PM
Points: 1,061, Visits: 2,580
Hi Sharon27,

Thanks for the DDL and consumable sample data. I added a couple more punches for Alvin and Rebecca to the sample data to test:

INSERT INTO EMPTMS
(uidfirstname,uidlastname,reader,dtdate)
VALUES ('001','Alvin','1','2013-09-21 6:30:000 ')

INSERT INTO EMPTMS
(uidfirstname,uidlastname,reader,dtdate)
VALUES ('001','Alvin','2','2013-09-21 17:50:000 ')

INSERT INTO EMPTMS
(uidfirstname,uidlastname,reader,dtdate)
VALUES ('002','Rebecca','1','2013-09-21 22:10:000 ')

INSERT INTO EMPTMS
(uidfirstname,uidlastname,reader,dtdate)
VALUES ('002','Rebecca','2','2013-09-22 7:30:000 ')

It appears from the sample data that "IN" punches occur on reader 1 and "OUT" punches occur on reader 2. If this holds true in all cases, this gives you the result you want:

SELECT c1.uidfirstname, c1.uidlastname, c1.dtdate AS logDateTimeIn, c2.dtdate AS logDateTimeOut 

FROM (SELECT uidfirstname, uidlastname, reader, dtdate, ROW_NUMBER() OVER (PARTITION BY uidfirstname, uidlastname ORDER BY dtdate, reader) as punch_number FROM dbo.EMPTMS ) c1

LEFT OUTER JOIN (SELECT uidfirstname, uidlastname, reader, dtdate, ROW_NUMBER() OVER (PARTITION BY uidfirstname, uidlastname ORDER BY dtdate, reader) as punch_number FROM dbo.EMPTMS ) c2
ON c1.uidfirstname = c2.uidfirstname
AND c1.punch_number + 1 = c2.punch_number

WHERE c1.reader = 1


If you're actually working in SQL Server 2005 or above (you posted in a forum for SQL Server 2000 and SQL Server 7), you can use a CTE to make your code a little neater:

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.uidfirstname, c1.uidlastname, c1.dtdate AS logDateTimeIn, c2.dtdate AS logDateTimeOut

FROM cte_punches c1

LEFT OUTER JOIN cte_punches c2
ON c1.uidfirstname = c2.uidfirstname
AND c1.punch_number + 1 = c2.punch_number

WHERE c1.reader = 1

This works in SQL 2005 and above, too:

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.uidfirstname, c1.uidlastname, c1.dtdate AS logDateTimeIn, c2.dtdate AS logDateTimeOut

FROM cte_punches c1

CROSS APPLY (SELECT c.dtdate FROM cte_punches c WHERE c1.uidfirstname = c.uidfirstname
AND c1.punch_number + 1 = c.punch_number) c2

WHERE c1.reader = 1

Performance may vary among the different versions, so test them all if necessary.


Jason Wolfkill
Blog: SQLSouth
Twitter: @SQLSouth
Post #1500484
Posted Wednesday, October 2, 2013 6:34 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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


001 Alvin 2013-09-21 06:30:00.000 2013-09-21 17:50:00.000
002 Rebecca 2013-09-21 22:10:00.000 2013-09-21 23:10:00.000
002 Rebecca 2013-09-21 23:10:00.000 2013-09-22 07:30:00.000

which should be like this.

001 Alvin 2013-09-21 06:30:00.000 2013-09-21 17:50:00.000
002 Rebecca 2013-09-21 22:10:00.000 2013-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.
Post #1500955
Posted Thursday, October 3, 2013 12:57 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:16 AM
Points: 6,828, Visits: 14,065
Hi Sharon

Can you confirm which SQL Server version you are using?


“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 #1501015
Posted Thursday, October 3, 2013 3:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 1, 2014 1:22 AM
Points: 12, Visits: 43
Sir 2005.
Post #1501094
Posted Thursday, October 3, 2013 5:02 AM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, September 3, 2014 11:10 AM
Points: 703, Visits: 361
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
Post #1501115
Posted Thursday, October 3, 2013 6:17 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:16 AM
Points: 6,828, Visits: 14,065
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)



“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 #1501148
Posted Thursday, October 3, 2013 7:55 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 12:50 PM
Points: 1,061, Visits: 2,580
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


001 Alvin 2013-09-21 06:30:00.000 2013-09-21 17:50:00.000
002 Rebecca 2013-09-21 22:10:00.000 2013-09-21 23:10:00.000
002 Rebecca 2013-09-21 23:10:00.000 2013-09-22 07:30:00.000

which should be like this.

001 Alvin 2013-09-21 06:30:00.000 2013-09-21 17:50:00.000
002 Rebecca 2013-09-21 22:10:00.000 2013-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
Blog: SQLSouth
Twitter: @SQLSouth
Post #1501192
Posted Friday, October 4, 2013 12:39 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Saturday, March 1, 2014 1:22 AM
Points: 12, Visits: 43
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!
Post #1501434
Posted Friday, October 4, 2013 2:19 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 3:16 AM
Points: 6,828, Visits: 14,065
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?


“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 #1501456
« Prev Topic | Next Topic »

Add to briefcase ««1234»»»

Permissions Expand / Collapse