SQL Query for time In/Out attendance

问题:

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

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

回答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

回答3:

Due duplication in some records: below is my query

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


SELECT empid , 
      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;

回答4:

Below is Oracle query with test data:

WITH EMP_tABLE AS
(
SELECT A.*, 
RANK() OVER (PARTITION BY IDD ORDER BY DATEE) RANKK
FROM (
SELECT '11' IDD,  'Jonh' NAMEE ,  TO_DATE('14/05/2014 8:16', 'DD/MM/YYYY HH24:MI') DATEE FROM DUAL
UNION 
SELECT '11' IDD,  'Jonh' NAMEE  ,  TO_DATE('14/05/2014 13:35', 'DD/MM/YYYY HH24:MI') DATEE FROM DUAL
UNION 
SELECT '11' IDD,  'Jonh' NAMEE  ,  TO_DATE('14/05/2014 17:23', 'DD/MM/YYYY HH24:MI') DATEE FROM DUAL
UNION 
SELECT '11' IDD,  'Jonh' NAMEE  ,  TO_DATE('14/05/2014 21:09', 'DD/MM/YYYY HH24:MI') DATEE FROM DUAL

UNION 
SELECT '12' IDD,  'Elizabe' NAMEE  ,  TO_DATE('14/05/2014 14:06', 'DD/MM/YYYY HH24:MI') DATEE FROM DUAL
UNION 
SELECT '12' IDD,  'Elizabe' NAMEE  ,  TO_DATE('14/05/2014 22:39', 'DD/MM/YYYY HH24:MI') DATEE FROM DUAL
UNION 
SELECT '13' IDD,  'Jimmy' NAMEE  ,  TO_DATE('11/05/2014 8:00', 'DD/MM/YYYY HH24:MI') DATEE FROM DUAL
UNION 
SELECT '13' IDD,  'Jimmy' NAMEE  ,  TO_DATE('11/05/2014 17:12', 'DD/MM/YYYY HH24:MI') DATEE FROM DUAL
) A )
SELECT AA.IDD, AA.NAMEE,
TO_CHAR(AA.DATEE, 'DD/MM/YYYY') DATE_ONLY, TO_CHAR(AA.DATEE,'HH24:MI') TIME_IN, TO_CHAR(BB.DATEE,'HH24:MI') TIME_OUT,

TRUNC (MOD ( (BB.DATEE-AA.DATEE)*24*60*60 , (24*60*60)) / (60 * 60))   || ':' ||
       TRUNC (MOD ((BB.DATEE-AA.DATEE)*24*60*60, (60*60)) / 60)             
DURATION

FROM EMP_TABLE AA, EMP_TABLE BB
WHERE
AA.IDD = BB.IDD and AA.RANKK + 1 = BB.RANKK 
and MOD(AA.RANKK , 2) = 1 ;

---

Below is query to be used by you.

REPLACE MY_TABLE, IDD, NAMEE, DATEE to your corresponding table/field names.

 WITH EMP_tABLE AS
(
SELECT my_table.*, 
RANK() OVER (PARTITION BY IDD ORDER BY DATEE) RANKK
FROM my_table
)
SELECT AA.IDD, AA.NAMEE,
TO_CHAR(AA.DATEE, 'DD/MM/YYYY') DATE_ONLY, TO_CHAR(AA.DATEE,'HH24:MI') TIME_IN, TO_CHAR(BB.DATEE,'HH24:MI') TIME_OUT,

TRUNC (MOD ( (BB.DATEE-AA.DATEE)*24*60*60 , (24*60*60)) / (60 * 60))   || ':' ||
       TRUNC (MOD ((BB.DATEE-AA.DATEE)*24*60*60, (60*60)) / 60)             
DURATION

FROM EMP_TABLE AA, EMP_TABLE BB
WHERE
AA.IDD = BB.IDD and AA.RANKK + 1 = BB.RANKK 
and MOD(AA.RANKK , 2) = 1