I am trying to query a table of employees where the time is generated through Door Access Control to create an attendance for payroll. I am getting the very first IN and Last Out of employee.
So, here's the query:
select * from(select distinct Value as EmployeeNumber ,UID1FirstName+' '+UID1LastName as EmployeeName, convert(varchar(18), dtDate, 1) as LogDate, convert(varchar(18), dtDate, 108) as LogTime, case when ReaderName like '%Entry%' and convert(varchar(18), dtDate, 108) < '13:00:00:00' then 'FirstIN'
when ReaderName like '%Exit%' then 'LastOut' end as Users from Log_Transactions inner join UserCredentials on UID1 = CredentialId inner join UserDefinedFields on HostUserId = UserID where CredentialId = '63' and Event = '2000' and dtDate between '2018-07-01' and '2018-07-13' and FieldNo = '1') as Userssssss PIVOT ( max(LogTime)FOR [Users] IN ([FirstIN], [LastOut])) AS Time1 GROUP BY EmployeeNumber,EmployeeName,FirstIN,LastOut,LogDate order by LogDate asc
Here is the result of the query: (I got the LastOut of the Employee but I did not got correctly the FirstIn of the date and time)
If I am getting the right data on table of First IN, it shows the first IN data of employee and I did not got the right data on the first query that I've been showed above. Please help. So, here's the result of selecting all of transactions of employee of her first IN.
MARIAN GRACERAMIREZ 2018-07-02 09:14:37.000
MARIAN GRACERAMIREZ 2018-07-02 09:51:43.000
MARIAN GRACERAMIREZ 2018-07-02 14:39:44.000
MARIAN GRACERAMIREZ 2018-07-02 16:03:26.000
MARIAN GRACERAMIREZ 2018-07-02 16:04:59.000
MARIAN GRACERAMIREZ 2018-07-02 16:29:13.000
MARIAN GRACERAMIREZ 2018-07-02 16:29:49.000
MARIAN GRACERAMIREZ 2018-07-02 17:04:44.000
MARIAN GRACERAMIREZ 2018-07-03 09:41:50.000
MARIAN GRACERAMIREZ 2018-07-03 09:43:40.000
MARIAN GRACERAMIREZ 2018-07-03 09:44:33.000
MARIAN GRACERAMIREZ 2018-07-03 11:59:30.000
MARIAN GRACERAMIREZ 2018-07-03 13:12:46.000
MARIAN GRACERAMIREZ 2018-07-03 13:19:34.000
MARIAN GRACERAMIREZ 2018-07-03 13:27:35.000
MARIAN GRACERAMIREZ 2018-07-03 14:08:11.000
MARIAN GRACERAMIREZ 2018-07-04 10:27:27.000
MARIAN GRACERAMIREZ 2018-07-04 12:16:34.000
MARIAN GRACERAMIREZ 2018-07-04 13:18:52.000
MARIAN GRACERAMIREZ 2018-07-04 13:30:04.000
MARIAN GRACERAMIREZ 2018-07-04 13:39:36.000
MARIAN GRACERAMIREZ 2018-07-04 15:56:22.000
MARIAN GRACERAMIREZ 2018-07-04 15:56:55.000
MARIAN GRACERAMIREZ 2018-07-04 16:02:23.000
MARIAN GRACERAMIREZ 2018-07-04 16:02:54.000
MARIAN GRACERAMIREZ 2018-07-04 16:08:23.000
MARIAN GRACERAMIREZ 2018-07-04 16:09:45.000
MARIAN GRACERAMIREZ 2018-07-04 17:17:43.000
MARIAN GRACERAMIREZ 2018-07-05 12:27:14.000
MARIAN GRACERAMIREZ 2018-07-05 12:28:06.000
MARIAN GRACERAMIREZ 2018-07-05 12:29:21.000
MARIAN GRACERAMIREZ 2018-07-05 14:46:04.000
MARIAN GRACERAMIREZ 2018-07-05 14:47:56.000
MARIAN GRACERAMIREZ 2018-07-05 15:20:27.000
MARIAN GRACERAMIREZ 2018-07-05 16:12:10.000
MARIAN GRACERAMIREZ 2018-07-05 17:09:27.000
MARIAN GRACERAMIREZ 2018-07-09 09:39:53.000
MARIAN GRACERAMIREZ 2018-07-09 10:03:29.000
MARIAN GRACERAMIREZ 2018-07-09 10:04:10.000
MARIAN GRACERAMIREZ 2018-07-09 10:07:21.000
MARIAN GRACERAMIREZ 2018-07-09 10:08:03.000
MARIAN GRACERAMIREZ 2018-07-09 11:56:56.000
MARIAN GRACERAMIREZ 2018-07-09 13:18:43.000
MARIAN GRACERAMIREZ 2018-07-09 13:20:22.000
MARIAN GRACERAMIREZ 2018-07-09 13:28:05.000
MARIAN GRACERAMIREZ 2018-07-09 14:20:47.000
MARIAN GRACERAMIREZ 2018-07-09 14:21:26.000
MARIAN GRACERAMIREZ 2018-07-09 14:33:45.000
MARIAN GRACERAMIREZ 2018-07-09 14:34:29.000
MARIAN GRACERAMIREZ 2018-07-09 16:31:17.000
MARIAN GRACERAMIREZ 2018-07-09 16:40:40.000
MARIAN GRACERAMIREZ 2018-07-09 17:10:25.000
MARIAN GRACERAMIREZ 2018-07-11 09:09:24.000
MARIAN GRACERAMIREZ 2018-07-11 09:16:10.000
MARIAN GRACERAMIREZ 2018-07-11 09:21:24.000
MARIAN GRACERAMIREZ 2018-07-11 10:45:03.000
MARIAN GRACERAMIREZ 2018-07-11 11:17:53.000
MARIAN GRACERAMIREZ 2018-07-11 12:02:30.000
MARIAN GRACERAMIREZ 2018-07-11 13:42:23.000
MARIAN GRACERAMIREZ 2018-07-11 13:52:32.000
MARIAN GRACERAMIREZ 2018-07-11 13:52:59.000
MARIAN GRACERAMIREZ 2018-07-11 13:54:05.000
MARIAN GRACERAMIREZ 2018-07-11 13:56:45.000
MARIAN GRACERAMIREZ 2018-07-11 14:06:13.000
MARIAN GRACERAMIREZ 2018-07-11 17:40:23.000
Sorry for my bad english. Please help. Thank yooouuuuu!
create table Door ( Employee varchar(60), Direction varchar(5), Stamp datetime )
go
insert into Door (Employee, Direction, Stamp ) values
( 'Fred', 'Entry', '2018-01-10 08:00:00.000' ),
( 'Fred', 'Exit', '2018-01-10 08:30:00.000' ),
( 'Fred', 'Entry', '2018-01-10 11:00:00.000' ),
( 'Fred', 'Exit', '2018-01-10 14:00:00.000' ),
( 'Fred', 'Entry', '2018-01-10 14:20:00.000' ),
( 'Fred', 'Exit', '2018-01-10 16:00:00.000' ),
( 'Fred', 'Entry', '2018-01-11 08:15:00.000' ),
( 'Fred', 'Exit', '2018-01-11 08:45:00.000' ),
( 'Fred', 'Entry', '2018-01-11 17:00:00.000' ),
( 'Fred', 'Exit', '2018-01-11 18:00:00.000' ),
( 'Mary', 'Entry', '2018-01-10 07:00:00.000' ),
( 'Mary', 'Exit', '2018-01-10 09:30:00.000' ),
( 'Mary', 'Entry', '2018-01-10 11:15:00.000' ),
( 'Mary', 'Exit', '2018-01-10 15:45:00.000' ),
( 'Mary', 'Entry', '2018-01-10 16:40:00.000' ),
( 'Mary', 'Exit', '2018-01-10 17:10:00.000' ),
( 'Mary', 'Entry', '2018-01-11 14:55:00.000' ),
( 'Mary', 'Exit', '2018-01-11 15:35:00.000' ),
( 'Mary', 'Entry', '2018-01-11 16:05:00.000' ),
( 'Mary', 'Exit', '2018-01-11 20:25:00.000' );
with
-- Break the timestamp into separate Date and Time
DoorA as
( select Employee, Direction,
cast(Stamp as date) as DoorDate,
cast(Stamp as time) as DoorTime from Door ),
-- Group together records for each Employee and Date.
-- This will give the earliest and latest Entry,
-- as well as earliest and latest Exit
DoorB as
( select Employee, Direction, DoorDate,
min(DoorTime) as First,
max(DoorTime) as Last from DoorA
group by Employee, Direction, DoorDate ),
-- Pull out the MIN values for the Entry records,
-- together with the MAX values for the Exit records.
DoorC as
( select Employee,DoorDate,
'First Entry' as FirstOrLast,
First as Stamp from DoorB
where Direction='Entry'
union
select Employee,DoorDate,
'Last Exit' as FirstOrLast,
Last as Stamp from DoorB
where Direction='Exit')
-- Display them nicely
select * from DoorC
order by Employee, DoorDate, FirstOrLast