问考勤考勤SQL查询

EN

;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

   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;

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

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;