September 30, 2013 at 2:08 am
I have a table name Employee Logs
table consist of EmpID, Empname,Logdatetime.
I can generate the first in and last out by using max and min. Unfortunately there is an employee who came in at 6 pm and comes out the next day. if i use min and max this is what i got:
EmpID Empname Logdate Timein Timeout
0001 Sample 09/20/2013 6:00pm NULL
0001 Sample 09/21/2013 NULL 9:00am
what i want to bring out is like this
EmpID Empname Logdate Timein Logdate Timeout
0001 Sample 09/20/2013 6:00am 09/21/2013 9:00am
is this possible? i hope someone could help me...thanks in advance.....
September 30, 2013 at 10:25 am
Do you have some column to identify the night shift?
Otherwise, you would have to code exactly for that employee and it will fail when another employee takes the night shift.
September 30, 2013 at 10:17 pm
Hi Luis thanks for the reply, yes i dont have a column for the shifting schedule. would you mind to give me some script sample to get the correct output?
thank you
October 1, 2013 at 12:46 am
try something like this
create table a(ei int, en varchar(255), logdate datetime, tin varchar(7), tout varchar(7))
insert into a values(0001, 'Sample', '20.09.2013', '6:00pm', NULL)
insert into a values(0001, 'Sample', '21.09.2013', NULL, '9:00am')
select
si.ei, si.en, si.logdate, si.tin, so.tout
from
(select * from a si where tout is null) si
join
(select * from a si where tin is null) so
on
si.ei = so.ei
and so.logdate = dateadd(dd, 1, si.logdate)
eienlogdatetintout
1Sample2013-09-20 00:00:00.0006:00pm9:00am
October 1, 2013 at 12:57 am
Hi Just check this one:
create table dbo.Emp
(EmpID varchar(5), Empname varchar(20), Logdate date, Timein time, Timeout time)
insert into dbo.Emp select '1001', 'Steve', '20130928', '11:30 am', '02:30 pm'
insert into dbo.Emp select '1001', 'Steve', '20130928', '03:30 pm', '05:30 pm'
insert into dbo.Emp select '1001', 'Steve', '20130928', '05:45 pm', '08:00 pm'
insert into dbo.Emp select '1002', 'Anthony', '20130928', '08:00 pm', NULL
insert into dbo.Emp select '1002', 'Anthony', '20130929', NULL, '06:00 am'
select A.EmpID, A.Logdate,
MIN(A.Timein) AS Timein,
CASE WHEN MAX(A.Timeout) IS NULL
THEN (SELECT MIN(B.Timeout) FROM dbo.Emp B
WHERE B.EmpID = A.EmpID)
ELSE MAX(A.Timeout) END AS Timeout
from dbo.Emp A
group by A.EmpID, A.Logdate
having MIN(A.Timein) IS NOT NULL
October 1, 2013 at 1:25 am
Hi SSC-Enthusiastic, thanks for your help, i tried your suggested script it was ok, unfortunately i encounter a problem. if an employee forgot to logout his/her wont come out to the out put. Another problem is if an employee time in at 8am and logout at 5pm it wont come out also to the output.
thanks.
October 1, 2013 at 1:56 am
try a full join and 'isnull' to find the missing items
create table a(ei int, en varchar(255), logdate datetime, tin varchar(7), tout varchar(7))
insert into a values(0001, 'Sample', '20.09.2013', '6:00pm', NULL)
insert into a values(0001, 'Sample', '21.09.2013', NULL, '9:00am')
insert into a values(0002, 'Sample2', '20.09.2013', '7:00am', NULL)
insert into a values(0003, 'Sample3', '20.09.2013', '8:00am', NULL)
insert into a values(0003, 'Sample3', '21.09.2013', NULL, '5:00pm')
insert into a values(0004, 'Sample4', '21.09.2013', NULL, '4:00pm')
select
isnull(si.ei, so.ei), isnull(si.en, so.en), isnull(si.logdate, so.logdate), si.tin, so.tout
from
(select * from a si where tout is null) si
full join
(select * from a si where tin is null) so
on
si.ei = so.ei
and so.logdate = dateadd(dd, 1, si.logdate)
1Sample2013-09-20 00:00:00.0006:00pm9:00am
3Sample32013-09-20 00:00:00.0008:00am5:00pm
4Sample42013-09-21 00:00:00.000NULL4:00pm
2Sample22013-09-20 00:00:00.0007:00amNULL
pls note that 'sample3' spans a complete night!
October 1, 2013 at 6:10 am
sharon.ferrer (9/30/2013)
I have a table name Employee Logstable consist of EmpID, Empname,Logdatetime.
I can generate the first in and last out by using max and min. Unfortunately there is an employee who came in at 6 pm and comes out the next day. if i use min and max this is what i got:
EmpID Empname Logdate Timein Timeout
0001 Sample 09/20/2013 6:00pm NULL
0001 Sample 09/21/2013 NULL 9:00am
what i want to bring out is like this
EmpID Empname Logdate Timein Logdate Timeout
0001 Sample 09/20/2013 6:00am 09/21/2013 9:00am
is this possible? i hope someone could help me...thanks in advance.....
If you are sure that the results you are currently getting from your query are correct, then a second processing stage will provide the final result set you are looking for. Can you provide some sample data to work with? Either in your source data format or in the output format of your existing query? This should be a CREATE TABLE statement and statement(s) to populate the 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 1, 2013 at 8:00 am
Hi sqlnaive, thank you for your reply, but unfortunately thr script found some error. The NULL was filled with the wrong timeout or timein.
thanks
October 1, 2013 at 8:13 am
Please post DDL, sample data and expected results so we can give you better responses by working on something that will actually look like your real data.
For guidance, please read the article linked on my signature.
We're willing to help but we need you to help us.
October 1, 2013 at 8:52 am
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.
October 1, 2013 at 10:06 am
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
October 2, 2013 at 6:34 pm
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.
October 3, 2013 at 12:57 am
Hi Sharon
Can you confirm which SQL Server version you are using?
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 3:55 am
Sir 2005.
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply