Need the in & out time with total working hours

Ashutosh Jha

10y1.1k

Need one help. I have some record like below for around 100 members on daily basis-

Date & Time Reader User ID
3/14/2016 6:00 AM In 123
3/14/2016 7:00 AM out 123
3/14/2016 8:00 AM In 123
3/14/2016 9:00 AM out 123
3/15/2016 10:00 AM In 123
3/15/2016 11:00 AM out 123
3/15/2016 12:00 PM In 123
3/15/2016 1:00 PM out 123
3/16/2016 2:00 PM In 123
3/16/2016 3:00 PM out 123

Data is in Excel but can be put in SQL. Can somebody tell me how I can find the first In time, Last out time and the time person was In on daily basis.

Any help will be appreciated.

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Ashutosh Jha10y ago
Hi Pankaj,
Thanks for your reply and help but my requirement is to get the first in, last out and total time user was in on daily basis. The table is like below. Kindly look into this and help-
create table tbl1 (UserID numeric (18,0), Reader varchar (20), Date_Time datetime );
Insert into tbl1 values (123, 'In', '2015-08-24 06:00:00.000');
Insert into tbl1 values (123, 'Out', '2015-08-24 07:00:00.000');
Insert into tbl1 values (123, 'In', '2015-08-24 08:00:00.000');
Insert into tbl1 values (123, 'Out', '2015-08-24 09:00:00.000');
Insert into tbl1 values (123, 'In', '2015-08-24 10:00:00.000');
Insert into tbl1 values (123, 'Out', '2015-08-24 11:00:00.000');
Insert into tbl1 values (123, 'In', '2015-08-25 10:00:00.000');
Insert into tbl1 values (123, 'Out', '2015-08-25 11:00:00.000');
Insert into tbl1 values (124, 'In', '2015-08-24 06:15:00.000');
Insert into tbl1 values (124, 'Out', '2015-08-24 07:30:00.000');
Insert into tbl1 values (124, 'In', '2015-08-24 08:00:00.000');
Insert into tbl1 values (124, 'Out', '2015-08-24 09:30:00.000');
Insert into tbl1 values (124, 'In', '2015-08-24 10:00:00.000');
Insert into tbl1 values (124, 'Out', '2015-08-24 11:30:00.000');

The result should be like below-

UserID FirstIN Last Out TotalInTime Date
123 6:00 11:00 5:00 8/24
123 10:00 11:00 1:00 8/25
124 6:15 11:30 5:15 8/24
Pankaj Kumar Choudhary10y ago
Hello! Ashutosh try this query....
Query:
SELECT Tab_.UseID,MAX(Tab_.Entry_Time) AS Entry_Time,MAX(Tab_.Exit_Time) as Exit_Time FROM
(SELECT ee.UseID,FIRST_VALUE(ee.[Date&Time]) OVER(PARTITION BY ee.UseID ORDER BY ee.[Date&Time]) AS Entry_Time
,LAST_VALUE(ee.[Date&Time]) OVER(PARTITION BY ee.UseID ORDER BY ee.[Date&Time]) AS Exit_Time FROM dbo.EmpEntry ee
)Tab_
GROUP BY Tab_.UseID
My Table is:
And i get following o/p:
If you have doubt then reply........