0

I have the following table for Employee Attendance System 

userid  CheckTime               CheckType
1       2015-07-31 14:29:18.000     I   
1       2015-07-31 18:29:18.000     O   
1       2015-08-01 08:12:45.000     I   
1       2015-08-02 03:19:03.000     O   
1       2015-08-02 08:15:23.000     I   
1       2015-08-02 14:05:52.000     O   
1       2015-08-02 19:48:57.000     I   
1       2015-08-02 21:48:57.000     O

How can get the result as follow

userid    checkinTime             CheckOuttime

1         2015-07-31 14:29:18.000 2015-07-31 18:29:18.000
.
.
1         2015-08-01 08:12:45.000 2015-08-02 03:19:03.000
rabih kahaleh
  • 1
  • 3
  • What database are you using? Are the checkin/checkout always pairwise? – jpw Oct 29 '15 at 14:44
  • will there be only 1 checkin and only 1 checkout for a given date/userid? Will there be cases where there is a checkin but no checkout, or viceversa? – Adish Oct 29 '15 at 14:45
  • SQl server 2008. multiple check in and out for a given user in same date. and maybe checkin with no checkout. – rabih kahaleh Nov 02 '15 at 12:55

1 Answers1

0

You are likely looking for something like this:

Select UserId, Min(CheckTime) As CheckInTime, Max(CheckTime) As CheckOutTime
From EmployeeAttendance
Group By UserId, Cast(CheckTime As Date);

Update 2015-11-02

For the special case you mentioned, below is a more robust query...

Select UserId, InTime.CheckTime As CheckInTime, Min(OutTime.CheckTime) As CheckOutTime
From 
    (Select UserId, CheckTime From EmployeeAttendance Where CheckType = 'I') As InTime
    Left Join 
    (Select UserId, CheckTime From EmployeeAttendance Where CheckType = 'O') As OutTime
    On InTime.UserId = OutTime.UserId
    And InTime.CheckTime < OutTime.CheckTime
Group By UserId, InTime.CheckTime;

This query will pick the first checkout time that occurs after each checkin time.

You could take this query a step further by presenting a CheckOutTime of null when there are two consecutive check ins. It really depends on how you will use this data and how much you trust the data.

Mike
  • 3,641
  • 3
  • 29
  • 39
  • Thank you for your solution, but i have a special case when the user check in at 15/1/2015 23:00:00 and checkout 16/1/2015 06:00:00. thank you. – rabih kahaleh Nov 02 '15 at 10:39
  • Thank you Very much for this help, i will test the results and back to you. – rabih kahaleh Nov 03 '15 at 07:16
  • Dear @Mike, is it possible to show id, checkin, checkout group by date as follow.............1/1/2015.............1/2/2015.........1/3/2015.......1/4/2015 .id xxxxxx xxxxxxxx xxxxxxx xxxxxxx checkintime xxxxxx xxxxxxxx xxxxxxx xxxxxxxx checkoutime xxxxxx xxxxxxxx xxxxxxx xxxxxxxx .......thank u so much. – rabih kahaleh Nov 07 '15 at 14:37
  • Do you want to perform a pivot? If so, see [this answer](http://stackoverflow.com/a/15931734/772086). – Mike Nov 09 '15 at 16:55
  • Hello #Mike, in the query above that you sent to me, any updates to cover also when the user did not check in but check out only (i know its a user logic). thank you so much for your help. – rabih kahaleh Nov 24 '15 at 21:48