2
Answers

Need the in & out time with total working hours

Photo of Ashutosh Jha

Ashutosh Jha

9y
876
1
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.

Answers (2)

0
Photo of Ashutosh Jha
NA 3 878 8y
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 
 
0
Photo of Pankaj  Kumar Choudhary
70 26.6k 13.5m 9y
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........