AnkurJain14 Ответов: 2

Sql 2012-поиск первого входа и последнего выхода для сотрудников

Я пытаюсь создать запрос в SQL Server 2012, чтобы вычислить разницу во времени между первыми и последними свайпами для каждого сотрудника (для обычной дневной смены и ночной смены) в компании.

Требования таковы:

Рассчитать обычный сдвиг удар первый вход / последний выход
Рассчитать удар ночной смены первый вход / последний выход
Проверить обычную смену с пропавшими выбивать - если не получиться пробить отметку как null
Проверьте регулярную смену с отсутствующим пуансоном - если нет в пуансоне, отметьте как ноль
Проверьте ночную смену с отсутствующим пробойником - если нет пробойника, отметьте его как нулевой
Проверьте ночную смену с отсутствующим пуансоном - если нет в пуансоне, отметьте как ноль
Вычислите разницу во времени между первым ударом и последним

У меня нет фиксированного времени смены для каждого сотрудника, и я хочу проверить, не превышает ли разница во времени между ударами порога (15 часов), а затем рассматривать ее как пропущенный удар или же вычислить разницу в часах нормально.


текущие результаты:
EmpName	InDate	OutDate	WorkTime
User1	NULL	2015-07-28 21:05:32.000	NULL
User1	2015-07-20 11:07:29.000	2015-07-20 21:13:27.000	10:05:58
User1	2015-07-21 12:07:03.000	2015-07-21 21:04:02.000	08:56:59
User1	2015-07-22 11:48:06.000	NULL	NULL
User1	2015-07-22 13:57:58.000	2015-07-22 20:59:22.000	07:01:24
User1	2015-07-23 12:38:41.000	2015-07-23 21:03:13.000	08:24:32
User1	2015-07-24 11:51:03.000	NULL	NULL
User1	2015-07-24 14:36:55.000	2015-07-24 20:51:06.000	06:14:11
User1	2015-07-27 12:10:54.000	2015-07-27 21:04:47.000	08:53:53
User1	2015-07-28 11:24:09.000	NULL	NULL
User1	2015-07-29 12:03:09.000	2015-07-29 21:00:28.000	08:57:19
User1	2015-07-30 11:58:43.000	NULL	NULL
User1	2015-07-30 13:25:02.000	2015-07-30 21:00:20.000	07:35:18
User1	2015-07-31 12:11:36.000	2015-07-31 21:34:07.000	09:22:31
User2	2016-06-20 23:03:33.000	2016-06-21 02:36:38.000	03:33:05
User2	2016-06-21 17:02:29.000	2016-06-21 23:35:25.000	06:32:56
User2	2016-06-22 17:42:01.000	2016-06-23 02:47:18.000	09:05:17



Вышеприведенные результаты не отражают правильно, как это должно быть. Не знаю, что делать дальше, чтобы исправить эту проблему. Вы сможете распознать проблему с результатом, как только выполните следующий запрос.

предполагаемые результаты:
EmpName	InDate	OutDate	WorkTime
User1	2015-07-20 11:07:29	2015-07-20 21:13:27	10:05:58
User1	2015-07-21 12:07:03	2015-07-21 21:04:02	8:56:59
User1	2015-07-22 11:48:06	2015-07-22 20:59:22	9:11:16
User1	2015-07-23 12:38:41	2015-07-23 21:03:13	8:24:32
User1	2015-07-24 11:51:03	2015-07-24 20:51:06	9:00:03
User1	2015-07-27 12:10:54	2015-07-27 21:04:47	8:53:53
User1	2015-07-28 11:24:09	2015-07-28 21:05:32	9:41:23
User1	2015-07-29 12:03:09	2015-07-29 21:00:28	8:57:19
User1	2015-07-30 11:58:43	2015-07-30 21:00:20	9:01:37
User1	2015-07-31 12:11:36	2015-07-31 21:34:07	9:22:31
User2	2016-06-20 23:03:33	2016-06-21 02:36:38	3:33:05
User2	2016-06-21 17:02:29	2016-06-21 23:35:25	6:32:56
User2	2016-06-22 17:42:01	2016-06-23 02:47:18	9:05:17
User2	2016-06-27 11:05:11	NULL	NULL
User2	NULL	2016-06-30 18:25:34	NULL



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

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

Create table #TempData  (EmpName nvarchar(50),EventDateTime DateTime, TrnName nvarchar(20),TrnCode int)

Insert Into #TempData Values
 ('User1','2015-07-20 11:07:29','Entrance','0'),
 ('User1','2015-07-20 11:08:09','Exit','1'),
 ('User1','2015-07-20 21:13:27','Exit','1'),
 ('User1','2015-07-21 12:07:03','Entrance','0'),
 ('User1','2015-07-21 21:04:02','Exit','1'),
 ('User1','2015-07-22 11:48:06','Entrance','0'),
 ('User1','2015-07-22 13:37:15','Exit','1'),
 ('User1','2015-07-22 13:57:58','Entrance','0'),
 ('User1','2015-07-22 20:59:22','Exit','1'),
 ('User1','2015-07-23 12:38:41','Entrance','0'),
 ('User1','2015-07-23 17:33:43','Exit','1'),
 ('User1','2015-07-23 18:09:13','Entrance','0'),
 ('User1','2015-07-23 21:03:13','Exit','1'),
 ('User1','2015-07-24 11:51:03','Entrance','0'),
 ('User1','2015-07-24 14:19:41','Exit','1'),
 ('User1','2015-07-24 14:36:55','Entrance','0'),
 ('User1','2015-07-24 20:51:06','Exit','1'),
 ('User1','2015-07-27 12:10:54','Entrance','0'),
 ('User1','2015-07-27 17:45:36','Exit','1'),
 ('User1','2015-07-27 18:36:24','Entrance','0'),
 ('User1','2015-07-27 19:16:21','Exit','1'),
 ('User1','2015-07-27 20:01:12','Entrance','0'),
 ('User1','2015-07-27 21:04:47','Exit','1'),
 ('User1','2015-07-28 11:24:09','Entrance','0'),
 ('User1','2015-07-28 21:05:32','Exit','1'),
 ('User1','2015-07-29 12:03:09','Entrance','0'),
 ('User1','2015-07-29 18:36:07','Exit','1'),
 ('User1','2015-07-29 19:25:16','Entrance','0'),
 ('User1','2015-07-29 21:00:28','Exit','1'),
 ('User1','2015-07-30 11:58:43','Entrance','0'),
 ('User1','2015-07-30 13:09:18','Exit','1'),
 ('User1','2015-07-30 13:25:02','Entrance','0'),
 ('User1','2015-07-30 21:00:20','Exit','1'),
 ('User1','2015-07-31 12:11:36','Entrance','0'),
 ('User1','2015-07-31 19:46:47','Exit','1'),
 ('User1','2015-07-31 20:44:27','Entrance','0'),
 ('User1','2015-07-31 21:34:07','Exit','1'),
 ('User2','2016-06-20 23:03:33','Entrance','0'),
 ('User2','2016-06-21 02:36:38','Exit','1'),
 ('User2','2016-06-21 17:02:29','Entrance','0'),
 ('User2','2016-06-21 17:27:03','Entrance','0'),
 ('User2','2016-06-21 19:11:24','Exit','1'),
 ('User2','2016-06-21 19:24:41','Entrance','0'),
 ('User2','2016-06-21 23:35:25','Exit','1'),
 ('User2','2016-06-21 23:57:03','Entrance','0'),
 ('User2','2016-06-22 17:27:00','Exit','1'),
 ('User2','2016-06-22 17:42:01','Entrance','0'),
 ('User2','2016-06-22 19:37:43','Exit','1'),
 ('User2','2016-06-22 21:27:35','Entrance','0'),
 ('User2','2016-06-22 21:27:59','Exit','1'),
 ('User2','2016-06-22 21:45:47','Exit','1'),
 ('User2','2016-06-22 21:56:15','Entrance','0'),
 ('User2','2016-06-23 00:42:44','Exit','1'),
 ('User2','2016-06-23 01:03:06','Entrance','0'),
 ('User2','2016-06-23 02:47:18','Exit','1'),
 ('User2','2016-06-27 11:05:11','Entrance','0'),
 ('User2','2016-06-30 18:25:34','Exit','1')


Запрос:

;WITH CTE1
AS
(
    SELECT
        *,
        ROW_NUMBER() OVER (ORDER BY CAST(T.EventDateTime AS DATE)) AS RowId
    FROM
        @TempData T
), CTE2
AS
(
        SELECT 
            A.EmpName,
            A.EventDateTime,
            A.TrnName,
            A.TrnCode,
            DENSE_RANK() OVER (ORDER BY MIN(B.RowId)) [Group]
        FROM 
            CTE1 A CROSS JOIN CTE1 B
        WHERE 
            ABS(DATEDIFF(HOUR, A.EventDateTime, B.EventDateTime)) BETWEEN 0 AND 14 
        GROUP BY 
                A.EmpName,
                A.EventDateTime,
                A.TrnName,
                A.TrnCode
), CTE3
AS
(
    SELECT
        T.EmpName,      
        MIN(IIF(T.TrnCode = 0, T.EventDateTime, NULL)) InDate,
        MAX(IIF(T.TrnCode = 1, T.EventDateTime, NULL)) OutDate
    FROM
        CTE2 T
    GROUP BY
        T.EmpName,
        T.[Group]
), FinalTable
AS
(
    SELECT
        T.EmpName ,
        T.InDate,
        IIF(T.InDate > T.OutDate, NULL, T.OutDate) AS OutDate
    FROM CTE3 T 

    UNION

    SELECT
        T.EmpName ,
        IIF(T.InDate > T.OutDate, NULL, T.InDate) AS InDate,
        T.OutDate AS OutDate
    FROM CTE3 T 
)


SELECT
    F.EmpName ,
    F.InDate ,
    F.OutDate,
    DATEDIFF(SECOND, F.InDate, F.OutDate) [Second],
    CONVERT(CHAR(8),DATEADD(SECOND,DATEDIFF(SECOND,F.InDate,F.OutDate),'1900-1-1'),8) WorkTime
FROM 
    FinalTable F

[no name]

ОМГ! Слава Тебе! Форматированные входные данные? Форматированный запрос, где вы на самом деле пробовали себя? Единственное, чего не хватает, - это четко определенной проблемы, "не отражающей правильно, как должно". Пример вашего вывода поможет. Но какое облегчение видеть на самом деле, почти там, хорошо построенный вопрос по сравнению с обломками и джетами, которые мы обычно видим. Надеюсь, вы получите ответ.

AnkurJain14

Спасибо, реальная проблема с моим кодом заключается в том, что я не могу выровнять результаты. Например: для первого in punch запрос заполняет ячейку IN time, однако соответствующее OUT time не является фактическим LAST OUT time. что очень расстраивает, так как я работаю над этим сайд-проектом уже очень давно. Просто посмотрите на снимок экрана в данной ссылке, и вы поймете проблему.

2 Ответов

Рейтинг:
2

David_Wimbley

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

Таким образом, для этих двух элементов должен быть столбец DiffHours, который предоставляет эти данные

Цитата:
Рассчитать обычный сдвиг удар первый вход / последний выход
Рассчитать удар ночной смены первый вход / последний выход


Для этих столбцов ниже используйте столбец PunchStatus
Цитата:
Проверить обычную смену с пропавшими выбивать - если не получиться пробить отметку как null
Проверьте регулярную смену с отсутствующим пуансоном - если нет в пуансоне, отметьте как ноль
Проверьте ночную смену с отсутствующим пробойником - если нет пробойника, отметьте его как нулевой
Проверьте ночную смену с отсутствующим пуансоном - если нет в пуансоне, отметьте как ноль


Для этого также используйте столбец Diffhours.
Цитата:
Вычислите разницу во времени между первым ударом и последним


Затем последнее примечание для приведенного ниже, используйте столбец Thresholddanalyzation.

Цитата:
У меня нет фиксированного времени смены для каждого сотрудника, и я хочу проверить, не превышает ли разница во времени между ударами порога (15 часов), а затем рассматривать ее как пропущенный удар или же вычислить разницу в часах нормально. 




WITH TimeClock (EmpName,
	TrnName,
	TrnCode,CurrentDate, PriorDate,PriorTransaction) AS  ( 
SELECT 
	EmpName,
	TrnName,
	TrnCode,
	A.EventDateTime AS CurrentDate, 
	(SELECT TOP 1 B.EventDateTime FROM #TempData AS B WHERE B.EventDateTime < A.EventDateTime AND A.EmpName  = B.EmpName ORDER BY B.EventDateTime DESC) AS PriorDate,
	(SELECT TOP 1 B.TrnName FROM #TempData AS B WHERE B.EventDateTime < A.EventDateTime AND A.EmpName  = B.EmpName ORDER BY B.EventDateTime DESC) AS PriorTransaction
FROM #TempData AS A)

SELECT 
	EmpName,
	TrnName,
	TrnCode,
	CurrentDate, 
	PriorDate,
	PriorTransaction,
	--Anaylzing
	CASE 
		WHEN TimeClock.PriorTransaction = TimeClock.TrnName AND TimeClock.PriorTransaction = 'EXIT' THEN 'MISSED PUNCH OUT'
		WHEN TimeClock.PriorTransaction = TimeClock.TrnName AND TimeClock.PriorTransaction = 'ENTRANCE' THEN 'MISSED PUNCH IN'
		ELSE '' END AS PunchStatus,
	DATEDIFF(HOUR, TimeClock.PriorDate, TimeClock.CurrentDate) AS DiffHours,

	CASE WHEN DATEDIFF(HOUR, TimeClock.PriorDate, TimeClock.CurrentDate) > 15 THEN 'TREAT AS OUT' ELSE 'CALC NORMALLY' END AS ThresholdAnalyzation
FROM TimeClock ORDER BY TimeClock.CurrentDate


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

AnkurJain14

Привет, Дэвид, Спасибо за быстрый ответ, но результаты-это не то, что я ищу. Для вашей помощи, пожалуйста, обратитесь к приведенной ниже ссылке для получения результатов, которые у меня есть на данный момент, и я ищу небольшую оптимизацию в коде, чтобы исправить результат.
http://i.stack.imgur.com/yvqby.png
Пожалуйста, обратитесь к вышесказанному и, пожалуйста, помогите придумать какую-то логику, чтобы исправить результаты. Я обновил схему с большим количеством записей, чтобы охватить и другие сценарии.

Рейтинг:
1

Wendelius

Извините, я совершенно неправильно понял ваше требование. Спасибо за новые примеры данных.

Может быть, что-то вроде следующего? Он не вычисляет рабочее время, так как его легко добавить, если данные в остальном верны?

with boundaries as (
 select  *
 from #TempData td1
 where td1.trncode = 0
 and  not exists (select 1 
                   from #TempData td2
				   where td2.empname = td1.empname
				   and   td2.trncode = td1.trncode
				   and   td2.EventDateTime < td1.EventDateTime
				   and   td2.EventDateTime > dateadd(hour, -15, td1.EventDateTime))
union
select * 
from #TempData td1
where  td1.trncode = 1
and not exists (select 1 
                   from #TempData td2
				   where td2.empname = td1.empname
				   and   td2.trncode = td1.trncode
				   and   td2.EventDateTime > td1.EventDateTime
				   and   td2.EventDateTime < dateadd(hour, 15, td1.EventDateTime))
union
select * 
from #TempData td1
where  not exists (select 1 
                   from #TempData td2
				   where td2.empname = td1.empname
				   and   td2.EventDateTime < td1.EventDateTime)
union
select * 
from #TempData td1
where  not exists (select 1 
                   from #TempData td2
				   where td2.empname = td1.empname
				   and   td2.EventDateTime > td1.EventDateTime)
)
select b1.empname,b1.EventDateTime, b2.EventDateTime
from boundaries b1, boundaries b2
where b1.TrnCode = 0
and b2.EmpName = b1.EmpName
and b2.trncode = 1
and b2.EventDateTime > b1.EventDateTime
and b2.EventDateTime < DATEADD(hour, 15, b1.EventDateTime)
union all
select b1.empname,b1.EventDateTime, null
from boundaries b1
where b1.TrnCode = 0
and not exists (select 1 
                  from boundaries b2
                  where b2.EmpName = b1.EmpName
                  and b2.trncode = 1
                  and b2.EventDateTime > b1.EventDateTime
                  and b2.EventDateTime < DATEADD(hour, 15, b1.EventDateTime))
union all
select b1.empname,null, b1.EventDateTime
from boundaries b1
where b1.TrnCode = 1
and not exists (select 1 
                  from boundaries b2
                  where b2.EmpName = b1.EmpName
                  and b2.trncode = 0
                  and b2.EventDateTime < b1.EventDateTime
                  and b2.EventDateTime > DATEADD(hour, -15, b1.EventDateTime))

Результаты, которые я вижу, таковы
User1	2015-07-20 11:07:29.000	2015-07-20 21:13:27.000
User1	2015-07-21 12:07:03.000	2015-07-21 21:04:02.000
User1	2015-07-22 11:48:06.000	2015-07-22 20:59:22.000
User1	2015-07-23 12:38:41.000	2015-07-23 21:03:13.000
User1	2015-07-24 11:51:03.000	2015-07-24 20:51:06.000
User1	2015-07-27 12:10:54.000	2015-07-27 21:04:47.000
User1	2015-07-28 11:24:09.000	2015-07-28 21:05:32.000
User1	2015-07-29 12:03:09.000	2015-07-29 21:00:28.000
User1	2015-07-30 11:58:43.000	2015-07-30 21:00:20.000
User1	2015-07-31 12:11:36.000	2015-07-31 21:34:07.000
User2	2016-06-20 23:03:33.000	2016-06-21 02:36:38.000
User2	2016-06-21 17:02:29.000	2016-06-21 23:35:25.000
User2	2016-06-22 17:42:01.000	2016-06-23 02:47:18.000
User2	2016-06-27 11:05:11.000	NULL
User2	NULL	                2016-06-30 18:25:34.000

AnkurJain14

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

Wendelius

Это приятно слышать :)

AnkurJain14

Привет @Mika,
Я снова изо всех сил пытаюсь получить правильные результаты из нижеприведенных данных.
('UserX', 2015-08-06 10:05:49 ,'Вход', '0'),
('UserX', 2015-08-06 18:03:05 ,'Выход", "1"),
('UserX', 2015-08-06 18:28:27 ,'Вход', '0'),
('UserX', 2015-08-06 20:36:37 ,'Выход", "1"),
('UserX', 2015-08-07 07:53:34 ,'Вход', '0'),
('UserX', 2015-08-07 07:55:12 ,'Вход', '0'),
('UserX', 2015-08-07 08:17:35 ,'Выход", "1"),
('UserX', 2015-08-07 08:19:13 ,'Вход', '0'),
('UserX', 2015-08-07 08:20:20 ,'Выход", "1"),
('UserX', 2015-08-07 08:26:53 ,'Вход', '0'),
('UserX', 2015-08-07 08:28:49 ,'Выход", "1"),
('UserX', 2015-08-07 08:30:14 ,'Вход', '0'),
('UserX', 2015-08-07 08:30:39 ,'Выход", "1"),
('UserX', 2015-08-07 08:43:07 ,'Выход", "1"),
('UserX', 2015-08-07 09:17:22 ,'Вход', '0'),
('UserX', 2015-08-07 13:09:26 ,'Выход", "1"),
('UserX', 2015-08-07 14:00:27 ,'Вход', '0'),
('UserX', 2015-08-07 17:15:16 ,'Выход", "1")

Используя Ваш запрос, ниже приведен результат для приведенных выше данных.
UserX 2015-08-06 10:05:49 NULL
UserX NULL 2015-08-07 17:15:16

Я пытался внести изменения в условие dateadd, которое вы использовали в каждом запросе select, но это не меняет вышеприведенного результата. Проблема заключается в том, что CTE(top 2 select queries) не выбирает данные на основе используемых фильтров. Не могли бы вы предложить что-нибудь, чтобы решить эту проблему, не влияя на другие данные?