Leila Toumi Ответов: 1

Как рассчитать продолжительность сверхурочной работы в соответствии с InOutMode с помощью sql ?

Я пытаюсь рассчитать продолжительность сверхурочной работы для каждого сотрудника.
Я использую таблицу журналов: Log_Attendance:

EnrollNumber    DateAttendance     TimesInOut                        ModeEvent   
1               12-07-2015          12-07-2015 14:00:00                 4
1               12-07-2015          12-07-2015 15:00:00                 5
1               12-07-2015          12-07-2015 17:00:00                 4
1               12-07-2015          12-07-2015 18:00:00                 4
1               12-07-2015          12-07-2015 19:00:00                 5


ModeEvent= 4 --> OverTimeOut
ModeEvent= 5 --> OverTimeIn

Мне нужно, чтобы система давала NULL, когда у сотрудника есть последовательные строки с одним и тем же ModeEvent (пример строки 3: у сотрудника есть overtimeIN, но он не отметил свой OvertimeOut)

Я попробовал этот запрос, но он дает мне неправильные значения:

SELECT  
        EnrollNumber,
        TimesInOut,
        NextDate,
        Mode,
      
    
         DATEDIFF(Minute, TimesInOut, NextDate) as duration
      
        
FROM    (   SELECT  EnrollNumber, 
                    ID,
                    TimesInOut,
                    Mode,
                    DateAttendance,
                    (   SELECT  MIN(TimesInOut) 
                        FROM    Log_Attendance T2
                        WHERE   T2.EnrollNumber = T1.EnrollNumber
                        AND     T2.TimesInOut > T1.TimesInOut
                         and T2.Mode <> T1.Mode
                         
                    ) AS NextDate
            FROM    Log_Attendance T1
           where mode in (4)
        ) AS T
        
 where mode in (4,5) and EnrollNumber=1




Пожалуйста, есть какое-нибудь решение ?

Michael_Davies

Глядя на ваш пример, предположим, что 4=In, а не Out и 5=Out, а не in...

Почему бы не подумать о том, чтобы держать вход и выход в двух отдельных полях таблицы, это значительно упростило бы выполнение вычислений и определение отсутствующих выходов. По умолчанию время выхода должно быть полночь на раннюю дату, например 01/01/1980, чтобы обозначить, что он не был подписан. Тогда все, что вам нужно,-это WHERE out<>01/01/1980, чтобы поймать все подписанные сверхурочные и изменить его на=, чтобы поймать неподписанные...

Leila Toumi

Я не могу, потому что у меня есть другой режим событий, такой как CheckIn, CheckOut, BreakIn и breakOut

Leila Toumi

спасибо за ваш ответ, но не могли бы вы привести мне пример, пожалуйста.

Michael_Davies

Я обновил свой комментарий.

У вас есть два столбца в таблице, один для тайм-аута и один для тайм-аута, установите по умолчанию тайм-аут, чтобы быть известной ранней датой, как я предлагаю.

Затем вы можете выбрать с помощью DATEDIFF эти два поля без каких-либо сложных суб-выделений.

Это также помогает при проверке выхода из системы, например, я предполагаю, что кто-то не должен быть в состоянии выйти на следующий день сверхурочный вход с предыдущего дня или любое другое правило, которое вы хотите применить в отношении сверхурочных часов. Если вы делаете это так, как у вас есть, то все ваши обновления и выбор будут сложными.

1 Ответов

Рейтинг:
2

Wendelius

Правильно ли я понял, что вы хотите NULL в случае, если есть знак IN, но не OUT?

Для такого запроса что-то вроде:

SELECT l1.*, NULL as overtime
FROM Log_Attendance l1
WHERE l1.ModeEvent = 4  -- select IN events
AND   5 <> (SELECT ModeEvent -- search for the corresponding out event
            FROM Log_Attendance l2
            WHERE l2.EnrollNumber = l1.EnrollNumber 
            AND   l2.TImesInOut = (SELECT MIN(L3.TImesInOut) -- based on the next event in time
                                   FROM Log_Attendance l3
                                   WHERE l3.EnrollNumber = l1.EnrollNumber
                                   AND L3.TImesInOut > l1.TimesInOut))

Вы можете переключить условие наоборот, если хотите иметь строки с меткой out, но не соответствующие метке in, и использовать UNION для объединения результирующих наборов.

ДОПОЛНЕНИЕ
Что-то вроде этого?
SELECT EnrollNumber,
       '' AS desription,
       DATEDIFF(hour, l2.TimesInOout, l1.TimesInOut) as time
FROM Log_Attendance l1,
     Log_Attendance l2
WHERE l1.ModeEvent = 4
AND   l2.ModeEvent = 5
AND   l1.EnrollNumber = l2.EnrollNumber
AND   l2.TImesInOut = (SELECT MIN(L3.TImesInOut) -- based on the next event in time
                       FROM Log_Attendance l3
                       WHERE l3.EnrollNumber = l1.EnrollNumber
                       AND L3.TImesInOut > l1.TimesInOut))
UNION ALL
SELECT EnrollNumber,
       'End time missing' AS desription,
       NULL as time
FROM Log_Attendance l1
WHERE l1.ModeEvent = 4  -- select IN events
AND   5 <> (SELECT ModeEvent -- search for the corresponding out event
            FROM Log_Attendance l2
            WHERE l2.EnrollNumber = l1.EnrollNumber 
            AND   l2.TImesInOut = (SELECT MIN(L3.TImesInOut) -- based on the next event in time
                                   FROM Log_Attendance l3
                                   WHERE l3.EnrollNumber = l1.EnrollNumber
                                   AND L3.TImesInOut > l1.TimesInOut))

Leila Toumi

Я хочу , чтобы он вычислял продолжительность между 2 overtimein и overtimeout ana, если у сотрудника есть вход без выхода, он отображает null в столбце overtimeduration

Wendelius

Я изменил запрос, чтобы включить записи, имеющие метки входа и выхода. Однако я не уверен в этих условиях, так как вы единственный, кто знает логику этих данных. Это означает, что вам, возможно, придется добавить дополнительные условия или изменить те, которые я написал.