SQL Query for time In/Out attendance

Question

I have a table with the below sample output.

    ID_Emp| Name    |   Date
   ----------------------------------
    11    |Jonh     |14/05/2014 8:16
    11    |Jonh     |14/05/2014 13:35
    11    |Jonh     |14/05/2014 17:23
    11    |Jonh     |14/05/2014 21:09
    12    |Elizabe  |14/05/2014 14:06
    12    |Elizabe  |14/05/2014 22:39
    13    |Jimmy    |14/05/2014 8:00
    13    |Jimmy    |14/05/2014 17:12

I want to build a query to achieve the below results:

    ID_Emp|Name  |Date          |TimeIn |TimeOut|Hours
   -------------------------------------------------------
    11  |Jonh    |14/05/2014    |8:16   |13:35  |5:19
    11  |Jonh    |14/05/2014    |17:23  |21:09  |3:46
    12  |Elizabe |14/05/2014    |14:06  |22:39  |8:33
    13  |Jimmy   |14/05/2014    |8:00   |17:12  |9:12

Answer 1

Try this:

;with cte as
(select *, rank() over(partition by ID_Emp order by [Date]) rn
 from attendance)

select src.ID_Emp, src.Name, convert(date, src.[Date]) as [Date],
concat(datepart(hour,src.[Date]),':',datepart(minute,src.[Date])) as [TimeIn],
concat(datepart(hour,tgt.[Date]),':',datepart(minute,tgt.[Date])) as [TimeOut],
concat(datediff(minute,src.[Date],tgt.[Date])/60,':',datediff(minute,src.[Date],tgt. [Date])%60) as [Hours]
from cte src
inner join cte tgt on src.ID_Emp = tgt.ID_Emp and src.rn + 1 = tgt.rn and src.rn % 2 = 1

Caveat: I have tested this on SQL Server 2008 R2 only, but I assume it should work on Oracle as well with appropriate modifications.

Explanation: We use the RANK function to order by the date and time for each ID_Emp. Then, we join on ID and get pairs of rows. Finally, in order to ensure that we do not select every pair of consecutive rows, we make it a requirement that the source row's rank should be odd.

Answer 2

Try this Query:

   WITH Level1
           AS (-- apply row numbers 
               SELECT ID_Emp , 
                      Name,
                      CAST(Date AS DATETIME) AS [DateTime] , 
                     ROW_NUMBER() OVER ( PARTITION BY ID_Emp 
                                  ORDER BY Date ) AS RowNum 
              FROM table1 
              ), 
        LEVEL2
           AS (-- find the last and next event type for each row 
              SELECT   A.ID_Emp , 
                       A.Name,
                       A.DateTime ,                       
                       COALESCE(NULL, 'N/A') AS LastEvent , 
                       COALESCE(NULL, 'N/A') AS NextEvent 
              FROM Level1 A 
                   LEFT JOIN Level1 LastVal 
                      ON A.ID_Emp = LastVal.ID_Emp 
                         AND A.RowNum - 1 = LastVal.RowNum 
                   LEFT JOIN Level1 NextVal 
                      ON A.ID_Emp = NextVal.ID_Emp 
                         AND A.RowNum + 1 = NextVal.RowNum ), 
        Level3 
           AS (-- reapply row numbers to row-eliminated set 
              SELECT  ID_Emp ,
                      Name,
                      DateTime ,                      
                      LastEvent , 
                      NextEvent , 
                      ROW_NUMBER() OVER ( PARTITION BY ID_Emp 
                          ORDER BY DateTime ) AS RowNBr 
              FROM Level2              
              ), 
        Level4
           AS (-- pair enter and exit rows. 
               SELECT A.ID_Emp , 
                      A.Name,
                      A.DateTime , 
                      B.DateTime AS ExitDateTime 
               FROM Level3 A 
                    JOIN Level3 B ON A.ID_Emp = B.ID_Emp 
                                     AND A.RowNBr + 1 = B.RowNBr               
               ), 
        LEVEL5 
           AS (--Calculate the work session duration 
               SELECT ID_Emp , 
                      Name,
                      DATEDIFF(second, DateTime, ExitDateTime) 
                               AS Seconds , 
                      DateTime , 
                      ExitDateTime 
               FROM Level4
              ) 


  SELECT ID_Emp , 
          Name,
          CAST([DateTime] AS Date) AS [Date],
          CONVERT(varchar(5), [DateTime], 108) as [In], 
          CONVERT(varchar(5), [ExitDateTime], 108) As Out,  
          RIGHT('0' +   CAST(Seconds / 3600 AS VARCHAR(2)), 2) + ':' 
          + RIGHT('0' +   CAST(Seconds % 3600 / 60 AS VARCHAR(2)), 2) + ':' 
          + RIGHT('0' +   CAST(Seconds % 3600 % 60 AS VARCHAR(2)), 2) 
                               AS TotalHours 
   FROM Level5;

Live Demo

Output:

---

ID_EMP  NAME    DATE        IN                  OUT                 TOTALHOURS

---

11      Jonh    2014-05-14  08:16:00.0000000    13:35:00.0000000    05:19:00
12      Elizabe 2014-05-14  14:06:00.0000000    22:39:00.0000000    08:33:00
13      immy    2014-05-14  08:00:00.0000000    17:12:00.0000000    09:12:00