NanaKwame Ответов: 2

Расчет регистрации и проверки записей посещаемости сотрудников.

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

Что я уже пробовал:

Create Table EmployeeAttendanceDetails
(
     EmployeeID    VarChar(10) Not Null,
     EmployeeName  VarChar(50) ,
     Designation   VarChar(50) ,
     WorkingDate   Date        Not Null,
     InputType     VarChar(10) Not Null,
     TimeInput     Time        Not Null
)
--Образцы Данных Для Тестирования
EmployeeID EmployeeName            Department         WorkingDate InputType TimeI
EMP105     Success Abena Gyasiwaa  Marketing Manager  2017-01-12  Time In   08:07
EMP101     Samuel Osei Banahene    Deputy MD          2017-01-12  Time In   08:08
EMP104     George Mensah           Supervisor         2017-01-12  Time In   08:10
EMP103     Nancy Koffour           Department Head    2017-01-12  Time In   08:10
EMP102     Andrews Appiah Sarkodie Managing Director  2017-01-12  Time In   08:12
EMP101     Samuel Osei Banahene    Deputy MD          2017-01-12  Time Out  17:35
EMP102     Andrews Appiah Sarkodie Managing Director  2017-01-12  Time Out  17:35
EMP103     Nancy Koffour           Department Head    2017-01-12  Time Out  17:37
EMP104     George Mensah           Supervisor         2017-01-12  Time Out  17:38
EMP105     Success Abena Gyasiwaa  Marketing Manager  2017-01-12  Time Out  17:40
--В конце работы я хочу выбрать записи, рассчитать часы за день и вставить записи в новую таблицу. Ниже приведена таблица и оператор select - - - -.
Create Table EmployeeAttendanceTable
(
	EmployeeID		VarChar(10),
	EmployeeName		VarChar(50),
	WorkingDate		Date,
	CheckIn			VarChar(10),
	CheckOut		VarChar(10),
	[Hours]			DECIMAL(18,2) 
)

--Select statement
Insert Into EmployeeAttendanceTable

Select I.EmployeeID,I.EmployeeName,Cast(WorkingDate As Date)As [Date],
	Convert(VarChar(10),I.TimeInput,108)As CheckIn,
	Convert(VarChar(10),O.TimeInput,108)As CheckOut,
	CAST(DATEDIFF(MINUTE,I.TimeInput,O.TimeInput)/60.0 AS DECIMAL(18,2)) [Hours]
From EmployeeAttendanceDetails I
OUTER APPLY
(Select TOP 1
	InputType,
	TimeInput
	From EmployeeAttendanceDetails t
        Where t.BranchCode = 'B101'
	And t.WorkingDate ='12 JAN 2017'
	And t.TimeInput > I.TimeInput
	Order By t.TimeInput
)O
Where I.InputType ='Time In'
And  O.InputType ='Time Out'
--My problem is that is able to calculate only the last entry for the checkin employee only.
--Result
EmployeeID EmployeeName             WorkingDate CheckIn  CheckOut   Hours
EMP102	   Andrews Appiah Sarkodie  2017-01-12	08:12:11  17:35:11   9.38

NanaKwame

@Richard MacCutchan пожалуйста, это не решило проблему. Но все равно спасибо.

2 Ответов

Рейтинг:
14

Richard MacCutchan 

WorkingDate Date,
CheckIn VarChar(10),
CheckOut VarChar(10),
[Hours] DECIMAL(18,2)

Почему вы используете типы varchar для времени? Используйте тип DateTime для регистрации и оформления заказа, что позволяет легко рассчитать общее время работы. Он также позволяет проводить рабочие периоды, которые продолжаются всю ночь. А поскольку время включает в себя дату, вам не нужны поля WorkinDate или Hours.

Рейтинг:
11

Richard Deeming

Что-то вроде этого должно сработать:

WITH cteDates As
(
    SELECT
        EmployeeID,
        EmployeeName,
        Designation,
        CASE InputType
            WHEN 'Time In' THEN 0
            ELSE 1
        END As IsTimeOut,
        WorkingDate,
        TimeInput,
        DateAdd(day, DateDiff(day, '19000101', WorkingDate), Cast(TimeInput As datetime2(0))) As LogTime,
        ROW_NUMBER() OVER (PARTITION BY EmployeeID ORDER BY WorkingDate, TimeInput) As RN
    FROM
        @AD
)
SELECT
    I.EmployeeID,
    I.EmployeeName,
    I.Designation,
    I.WorkingDate,
    I.TimeInput As CheckIn,
    O.TimeInput As CheckOut,
    Cast(DateDiff(minute, I.LogTime, O.LogTime) / 60. As decimal(18, 2)) As [Hours]
FROM
    cteDates As I
    LEFT JOIN cteDates As O
    ON O.EmployeeID = I.EmployeeID
    And O.RN = I.RN + 1
WHERE
    I.IsTimeOut = 0
;
Выход: 
EmployeeID EmployeeName            Designation       WorkingDate CheckIn   CheckOut  Hours
EMP101     Samuel Osei Banahene    Deputy MD         2017-01-12  08:08:00  17:35:00  9.45
EMP102     Andrews Appiah Sarkodie Managing Director 2017-01-12  08:12:00  17:35:00  9.38
EMP103     Nancy Koffour           Department Head   2017-01-12  08:10:00  17:37:00  9.45
EMP104     George Mensah           Supervisor        2017-01-12  08:10:00  17:38:00  9.47
EMP105     Success Abena Gyasiwaa  Marketing Manager 2017-01-12  08:07:00  17:40:00  9.55
Записи:
Если у вас есть ночная смена, она должна правильно рассчитать часы работы, но WorkingDate в столбце будет указана дата начала смены.

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

Если сотрудник выписывается дважды без регистрации между двумя событиями, то более поздний выезд будет отменен.

Если сотрудник зарегистрировался и еще не выписался, вы получите Null в CheckOut и Hours столбцы.

Если сотрудник регистрируется дважды без регистрации между двумя событиями, предполагается, что он зарегистрировался и вернулся в одно и то же время.
Например: (время в 08:07, время в 08:37, время в 17:40) будет производить (в 08:07, из 08:37, 0,5 часа) и (в 08:37, из 17:40, 9,05 часа)
Если вы предпочитаете вернуться Null в CheckOut и Hours столбцы, которые вы можете добавить And O.IsTimeOut = 1 к LEFT JOIN условия:
FROM
    cteDates As I
    LEFT JOIN cteDates As O
    ON O.EmployeeID = I.EmployeeID
    And O.RN = I.RN + 1
    And O.IsTimeOut = 1

NanaKwame

@Richard Deeming спасибо за ваши усилия. Я ценю то время, которое вы потратили, чтобы помочь мне в этом. Я запускаю его, и он работает. Позже я тоже пришел к этому решению.

Создать Proc prcEmployeeAttendanceRecords
(
@Поле Salesdate В Область Типа Datetime
)
Как
Начать
Вставить В EmployeeAttendanceRecords

Выбрать
Т.Кодсотрудника,
Т.EmployeeName,
@Поле salesdate в область
, CheckIn = CONVERT(VARCHAR(10), t.CheckIn, 108)
, CheckOut = CONVERT(VARCHAR(10), t.CheckOut, 108)
, [Hours] = CAST(DATEDIFF(MINUTE, t.CheckIn, t.CheckOut) / 60. КАК ДЕСЯТИЧНОЕ ЧИСЛО(10,2))
От(
Выбрать
Т.Кодсотрудника,
Т.EmployeeName,
Т.WorkingDate
В отеле checkin = Т.TimeInput
, CheckOut = r.TimeInput
, Параметр rownum = функции row_number() над (раздел по T.Кодсотрудника, Р.Порядок TimeInput по 1/0)
От EmployeeAttendanceDetails t
НАРУЖНОЕ ПРИМЕНЕНИЕ (
ВЫБЕРИТЕ ТОП 1 *
От EmployeeAttendanceDetails t2
Где t2.EmployeeID = t.EmployeeID
И t2.TimeInput > t.TimeInput
И функция dateadd(DD, то 0, то функция datediff(ДД, 0, Т.TimeInput)) = функция dateadd(DD, то 0, то функция datediff(ДД, 0, Т2.TimeInput))
И t2.InputType = 'тайм-аут'
И t2.WorkingDate = @SalesDate
Заказ по t2.TimeInput
) р
Где t.InputType = 'Time In'
) Т
Где t.RowNum = 1
Конец