초과 근무 시간과 정상 근무 시간을 계산해야 한다.

질문:

나는 시계를 가지고 있다attendance

|AttendanceresultId |   AccountID   |   Intime                  |     Outtime           |   ShiftID |       
|  1                |   1234        |   2016-06-21 06:56:00     |   NULL                |   1       |   
|  2                |   1234        |   NULL                    |   2016-06-21 17:02:00 |   1       |   
|  3                |   1234        |   2016-06-22 06:56:00     |   NULL                |   1       |   
|  4                |   1234        |   NULL                    |   2016-06-22 17:02:00 |   1       |   
|  5                |   1235        |   2016-06-21 22:55:00     |   NULL                |   3       |   
|  6                |   1235        |   NULL                    |   2016-06-22 06:00:00 |   3       |   
|  7                |   1235        |   2016-06-22 22:55:00     |   NULL                |   3       |   
|  8                |   1235        |   NULL                    |   2016-06-23 07:00:00 |   3       |       

또 다른 시계는 shift시계입니다.

|   ShiftId     |   Starttime   |   Endtime     |
|   1           |   07:00:00.00 |   16:00:00.00 |
|   3           |   23:00:00.00 |   06:00:00.00 |

나는 직원들의 총 근무 시간과 잔업 시간을 계산하고 싶다. 예를 들면,
예상 생산량,

|   AccountID   |   NormalHours |   OvertimeHours   |    
|   1234        |   18:08   Hrs |   02:04 Hrs       |   
|   1235        |   14:10   Hrs |   01:00 Hrs       |   

저는 sql 서버의 초보자입니다. 누구든지 건의를 할 수 있습니다

답안

. 저는 이를 네 부분으로 나누어 2008년에 정지 함수가 부족한 상황에 적응하도록 하겠습니다.
1) 로깅을 구성하는 데 사용되는 공통 테이블 표현식(식별 필드에 의존하지 않음)
2) 대기 함수를 사용하지 않는 총작업시간과 초과근무시간을 계산한다
3) 그리고 나는 이 분들을 정리하고 그것들을 시간과 분으로 나눈다.
4) 그런 다음 포맷합니다.
현실 세계의 데이터가 여러 곳에서 문제를 일으킬 수 있다고 생각하는 것이다.
대기 기능의 업데이트 버전 사용 안 함

WITH TimeRecords (AccountID, InOutTime, Intime, OutTime, Shift,
 RowNumber) AS
(SELECT att.AccountID, COALESCE(att.Intime,att.OutTime), 
 att.Intime, att.OutTime, att.Shift,
 ROW_NUMBER() OVER(ORDER BY att.AccountID, 
                       COALESCE(att.InTime, att.OutTime) )
 FROM dbo.attendance att)
 ,  
ShiftTime AS (
        SELECT   trecout.AccountID ,
                    trecin.InTime,
                    trecout.OutTime,
                  --  sh.EndTime ,
  -- Number of Minutes for the entire shift
                    CASE WHEN trecout.OutTime IS NOT NULL
                         THEN DATEDIFF(mi,
                                       trecin.InTime,
                                       trecout.OutTime)
                         ELSE 0
                    END AS TotalTime ,
  -- Number of Minutes for overtime
                    CASE WHEN trecout.OutTime IS NOT NULL
                         THEN DATEDIFF(mi, sh.EndTime,
                                       CAST(trecout.OutTime AS TIME))
                         ELSE 0
                    END AS OverTime
           FROM     TimeRecords trecout
           JOIN     TimeRecords trecin
               ON trecout.RowNumber -1 = trecin.RowNumber

              JOIN dbo.ShiftID sh
                  ON trecout.[Shift] = sh.ShiftID
         )
         ,
  -- Summarize the data
    ShiftTimeSum
      AS ( SELECT   st.AccountID ,
                    FLOOR(SUM(st.TotalTime - st.OverTime) / 60) AS      RegularHours ,
                    SUM(st.TotalTime - st.OverTime) % 60 AS RegularMinutes ,
                    SUM(st.OverTime) / 60 AS OverTimeHours ,
                    SUM(st.OverTime) % 60 AS OverTimeMinutes
           FROM     ShiftTime st
           GROUP BY st.AccountID
         )
 -- Now format
  SELECT  sts.AccountID ,
    CAST(sts.RegularHours AS VARCHAR(5)) + ':'
    + RIGHT(CAST(( 100 + sts.RegularMinutes ) AS CHAR(3)), 2) AS RegularTime ,
    CAST(sts.OverTimeHours AS VARCHAR(5)) + ':'
    + RIGHT(CAST(( 100 + sts.OverTimeMinutes ) AS CHAR(3)), 2) AS OverTime
  FROM    ShiftTimeSum sts;