Need the in & out time with total working hours

Question

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.

Ashutosh Jha · Answer

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 Choudhary · Answer

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........

Need the in & out time with total working hours

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (2)