RaviSNaik Ответов: 1

Как рассчитать посещаемость по журналу перфорации?

Дорогие Все,

Позвольте мне объяснить сценарий. Компания "ABC" имеет систему доступа к отпечаткам пальцев и посещаемости, которая регистрирует все удары(будь то вход или выход), устройство не настолько умно, чтобы определить его выход или вход, оно просто записывает все удары.

Компания "АВС" работает в 4 смены 
SHIFT STARTTIME ENDTIME
    A     6:00      14:00
    B     14:00     22:00
    C     22:00     6:00
    G     9:00      6:00


Сотрудники, работающие в смену А/Б/С, могут непрерывно работать в 2 смены(в таких случаях предоставляется C-Off(оплачиваемый отпуск) или выходной день)

Сотрудник, работающий в смену, также может присутствовать на смене B в зависимости от требования, но он должен пробить на смену и снова пробить на смену B(15-минутный интервал между этими 2 ударами является обязательным правилом, чтобы система могла понять, что он также присутствует на 2-й смене)

На основе вышеописанной рабочей системы на предприятии ниже приводится журнал перфорации сотрудника(код emp 3)

EMP 	LOGDATETIME
3	01-11-2017 14:00
3	01-11-2017 14:10
3	01-11-2017 15:01
3	01-11-2017 16:01
3	01-11-2017 17:11
3	01-11-2017 22:00
3	01-11-2017 22:15
3	01-11-2017 23:15
3	02-11-2017 01:15
3	02-11-2017 06:10
3	02-11-2017 22:01
3	02-11-2017 22:15
3	02-11-2017 23:15
3	03-11-2017 01:15
3	03-11-2017 06:10
3	05-11-2017 06:00
3	05-11-2017 07:10
3	05-11-2017 08:10
3	05-11-2017 09:10
3	05-11-2017 12:10
3	05-11-2017 14:01
3	06-11-2017 14:01
3	06-11-2017 15:01
3	06-11-2017 15:01
3	06-11-2017 22:01



Теперь как вы можете видеть у него есть несколько ударов в журнале мне нужно вычислить и показать данные следующим образом:

EMPCODE       IN                  OUT                 SHIFT
    3	      01-11-2017 14:00	01-11-2017 22:00       B
    3	      01-11-2017 22:15  02-11-2017 06:10       C
    3	      02-11-2017 22:01  03-11-2017 06:10       C
    3	      05-11-2017 06:00  05-11-2017 14:01       A
    3         06-11-2017 14:01  05-11-2017 22:01       B(HALF DAY)



Правила, подлежащие рассмотрению, заключаются в следующем
1) сотрудникам разрешается приходить пораньше за 30 минут до своей смены
2) сотрудникам разрешается уходить рано, если они пришли рано и их рабочий час закончился 8 часов
3) 3 коротких удара(означает, что сотрудники могут уйти за 40 минут до окончания смены) каждый месяц.
4) работник может уйти поздно в зависимости от требования, это будет считаться текущей сменой, но сверхурочные будут рассчитываться.

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

Я прочитал несколько документов о решениях SAP/.NET, но ни один из них не охватывает вышеуказанное требование и не дает мне никаких идей, как действовать дальше. Большинство компаний имеют различные перфораторы для входа и выхода. но в этом случае одно устройство отвечает за вход/выход, что усложняет ситуацию.

Я пытаюсь получить полную логику доказательства, которая может справиться с таким случаем.


Любая помощь/идеи/документ/псевдокод будут высоко оценены.

Richard MacCutchan

Проблема действительно в том, чтобы знать, когда происходит первый удар. Принимая это за "удар", вам просто нужно чередовать временные метки с тех пор, как in (first), out, in, out ...

RaviSNaik

но настоящая проблема заключается в том,чтобы разделить их как смену, так и несколько смен в один день.

Richard MacCutchan

Да, но только вы и руководство завода можете понять, как вы распределяете время между различными сменами.

RaviSNaik

уже выделено, читайте начало поста, я думаю, что вы совершенно неправильно поняли всю тему.

1 Ответов

Рейтинг:
11

Member 13587529

Эта проблема относится к области того, что, как я полагаю, обычно называется "анализом островов и пробелов".

Если это реальная система, над которой вы работаете (а не исследовательский проект), имейте в виду, что это трудная проблема даже для экспертов, не говоря уже о тех, кто должен спросить, с чего начать.

Ваши боссы должны быть предупреждены, что нет никакого практического решения, которое было бы полностью автоматическим (только частичное решение, которое имеет дело с однозначными случаями, а затем помечает необычные/исключительные случаи для рассмотрения человеком).

Кроме того, если столбец EMP ссылается на индивидуальный идентификатор сотрудника, имейте в виду, что в приведенном примере сотрудник 3 показан как выполняющий две смены подряд в течение 1/11/2017 и 2/11/2017.

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

Вот пример того, как вы начнете решать эту проблему в SQL, предполагая, что существует таблица под названием "punch_log" с двумя столбцами "emp" и "logdatetime".

WITH 

step1 AS
(
	SELECT
		emp
		,ROW_NUMBER() OVER (PARTITION BY emp ORDER BY logdatetime) AS employee_punch_num
		,logdatetime
		,LEAD(logdatetime) OVER (PARTITION BY emp ORDER BY logdatetime) AS next_logdatetime

	FROM 
		punch_log
)

,step2 AS
(
	SELECT 
		*
		,CAST(DATEDIFF(MINUTE, logdatetime, next_logdatetime) AS FLOAT)/60 AS length_between_punches

	FROM 
		step1
)

,step3 AS
(
	SELECT 
		*
		,IIF(length_between_punches IS NULL, NULL, IIF(length_between_punches > 12 /*ASSUMING ANY TIME DIFFERENCE BETWEEN PUNCHES OVER 12 HOURS IS REST*/, 'REST', 'WORK')) AS work_rest_ind

	FROM 
		step2
)

,step4 AS
(
	SELECT 
		*
		,employee_punch_num - ROW_NUMBER() OVER (PARTITION BY emp, work_rest_ind ORDER BY emp, employee_punch_num) AS work_rest_punch_num

	FROM 
		step3
)

,step5 AS
(
	SELECT
		*
		,DENSE_RANK() OVER (PARTITION BY emp, work_rest_ind ORDER BY work_rest_punch_num) AS work_rest_period_index
	FROM
		step4
)

,step6 AS
(
	SELECT
		*
		,MIN(logdatetime) OVER (PARTITION BY emp, work_rest_ind, work_rest_period_index) AS work_rest_period_begin
		,MAX(next_logdatetime) OVER (PARTITION BY emp, work_rest_ind, work_rest_period_index) AS work_rest_period_end
	FROM
		step5
	
)

,step7 AS
(
	SELECT
		*
		,ROUND(CAST(DATEDIFF(MINUTE, work_rest_period_begin, work_rest_period_end) AS FLOAT)/60,2) AS period_length_hrs
		,ROW_NUMBER() OVER (PARTITION BY emp, work_rest_ind, work_rest_period_begin, work_rest_period_end ORDER BY (SELECT NULL)) AS duplicate_row_num
	FROM
		step6
)

SELECT 
	emp
	,work_rest_ind
	,CAST(FORMAT(work_rest_period_begin, 'yyyy-MM-dd hh:mm') AS NCHAR(16)) AS period_begin
	,CAST(FORMAT(work_rest_period_end, 'yyyy-MM-dd hh:mm') AS NCHAR(16)) AS period_end
	,period_length_hrs 
	
FROM
	step7

WHERE
	duplicate_row_num = 1

ORDER BY 
	emp
	,work_rest_period_begin


Исходя из ваших данных, это дает следующие результаты:

emp         work_rest_ind period_begin     period_end       period_length_hrs
----------- ------------- ---------------- ---------------- ----------------------
3           WORK          2017-11-01 02:00 2017-11-02 06:10 16.17
3           REST          2017-11-02 06:10 2017-11-02 10:01 15.85
3           WORK          2017-11-02 10:01 2017-11-03 06:10 8.15
3           REST          2017-11-03 06:10 2017-11-05 06:00 47.83
3           WORK          2017-11-05 06:00 2017-11-05 02:01 8.02
3           REST          2017-11-05 02:01 2017-11-06 02:01 24
3           WORK          2017-11-06 02:01 2017-11-06 10:01 8
3           NULL          2017-11-06 10:01 NULL             NULL