Суммированные смены перфорации для перекрывающихся входов и выходов тактовых импульсов

У меня есть файл, который содержит все смены времени для каждого сотрудника, например:

сотрудник время начала время окончания 123 2022-10-23 10:40:00.000 2022-10-23 14:00:00.000 123 2022-10-23 14:00:00.000 2022-10-23 14:30:00.000 123 2022-10-23 14:35:00.000 2022-10-23 17:07:00.000 541 2022-10-23 06:50:00.000 2022-10-23 12:00:00.000 541 2022-10-23 13:00:00.000 2022-10-23 15:30:00.000

Тем не менее, я хочу вернуть сводную строку для каждого сотрудника, ЕСЛИ время входа и выхода попадает в перекрывающиеся часы, например, так

сотрудник время начала время окончания 123 2022-10-23 10:40:00.000 2022-10-23 17:07:00.000 541 2022-10-23 06:50:00.000 2022-10-23 12:00:00.000 541 2022-10-23 13:00:00.000 2022-10-23 15:30:00.000

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

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

WITH  Hours AS(
    SELECT 1 AS HOUR
    UNION ALL
    SELECT HOUR + 1 FROM Hours WHERE HOUR < 24
)
SELECT 
   CAST(Start_Time AS DATE) [DATE], 
   HOUR, 
   COUNT(Emp_Int) AS [Head Count]
FROM 
   Hours
LEFT JOIN 
   TIME_SHIFTS_TABLE T on HOUR BETWEEN DATEPART(HOUR, START_TIME) AND DATEPART(HOUR, END_TIME)
GROUP BY 
   CAST(Start_Time AS DATE), HOUR
ORDER BY 
   CAST(Start_Time AS DATE), HOUR asc

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

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

lemon 18.01.2023 18:46

Спасибо за вашу рекомендацию. Я разместил еще один вопрос отдельно

honey_badgerzz 18.01.2023 19:47

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

lemon 18.01.2023 20:46
ReactJs | Supabase | Добавление данных в базу данных
ReactJs | Supabase | Добавление данных в базу данных
Это и есть ваш редактор таблиц в supabase.👇
Понимание Python и переход к SQL
Понимание Python и переход к SQL
Перед нами лабораторная работа по BloodOath:
1
3
56
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Это проблема пробелов и островов. Одним из способов решения такого рода задач является

  • сначала создайте разделы, которые вам нужны, с текущей суммой,
  • затем примените агрегацию с помощью MIN(start_time) и MAX(end_time).

Вы можете создать текущую сумму:

  • проверка, когда разница между текущим временем начала и предыдущим временем окончания превышает 59 минут, с помощью DATEDIFF, поэтому в этом случае отметьте 1
  • используйте оконную функцию SUM над этим флагом для каждого из ваших сотрудников

Затем вы можете просто запустить агрегацию на вновь созданном разделе.

WITH cte AS (
    SELECT *,
           CASE WHEN DATEDIFF(mi, LAG(end_time) OVER(PARTITION BY employee ORDER BY start_time), start_time) >= 60 
                THEN 1 ELSE 0 
           END AS change_partition
    FROM tab
), cte2 AS (
    SELECT *, SUM(change_partition) OVER(PARTITION BY employee ORDER BY start_time) AS partitions
    FROM cte
)
SELECT employee, MIN(start_time) AS start_time, MAX(end_time) AS end_time 
FROM cte2
GROUP BY employee, partitions

Посмотрите демо здесь.

Еще раз спасибо за ваш ответ! Вопрос... Я столкнулся с проблемой, когда сотрудники, которые работали около 22:00, 23:00 или 00:00, не переносятся на следующий день на 1:00, 2:00 и т. д. Я знаю, что не упомянул об этом, но я хотел бы нравится отчитываться.

honey_badgerzz 18.01.2023 17:00

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

lemon 18.01.2023 17:33

Я обновил свой вопрос и включил обновленную демонстрацию

honey_badgerzz 18.01.2023 18:21

Другие вопросы по теме