Sujith Augustine

Sujith Augustine

  • 1.5k
  • 128
  • 3.3k

SQL Server 2008 Query for attendance time In and time Out

Jan 4 2017 2:02 AM
I have a table with the below sample

EmployeeID | Employee  | PunchDateTime
 ------------------|----------------|----------------------
11       |   Sujith        | 22/12/2016 08:16:00
11       |   Sujith        | 22/12/2016 13:35:00
11       |   Sujith        | 22/12/2016 17:23:00
11       |   Sujith        | 22/12/2016 21:09:00
12       |   Tony         | 22/12/2016 14:06:00
12       |   Tony         | 22/12/2016 22:39:00
13       |   Jimmy      | 22/12/2016 08:00:00
13       |   Jimmy      | 22/12/2016 17:12:00
14       |   Nitha        | 22/12/2016 18:50:00
16       |   Juby         | 22/12/2016 09:00:00 
14       |   Nitha        | 23/12/2016 07:05:00
16       |   Juby         | 23/12/2016 09:05:00
 

I want to build a query to the below results:

EmployeeID| Employee |     DutydateTimeIn    |    DutyDateTimeOut  | WorkingHours
------------------|---------------|-------------------------------|-------------------------------|-------------------
11      |   Sujith      | 22/12/2016 08:16:00 | 22/12/2016 13:35:00 |05:19
11      |   Sujith      | 22/12/2016 17:23:00 | 22/12/2016 21:09:00 |03:46
12      |   Tony       | 22/12/2016 14:06:00 | 22/12/2016 22:39:00 |08:33
13      |   Jimmy    | 22/12/2016 08:00:00 | 22/12/2016 17:12:00 |09:12
14      |   Nitha      | 22/12/2016 18:50:00 | 23/12/2016 07:05:00 |12:15
16      |   Juby       | 22/12/2016 09:00:00 |                                        |
16      |   Juby       | 23/12/2016 09:05:00 |                                        |  

Answers (3)