Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Объединение данных по дате и ID  [new]
Lekime
Member

Откуда:
Сообщений: 5
Приветствую всех!
Имеется запрос к таблице Log:
WITH cte AS (SELECT *, 
             LEAD( [DateTime]) OVER (PARTITION BY EmployeeID ORDER BY DateTime ASC) nextDateTime, 
             LEAD([Message]) OVER (PARTITION BY EmployeeID ORDER BY DateTime ASC) nextMessage
             FROM  Log )

SELECT [EmployeeID], cast([DateTime] as date) as [Дата], [DateTime] as [Дата входа] , nextDateTime as [Дата выхода], 
CONCAT(FORMAT((DATEDIFF(MINUTE, [DateTime], nextDateTime)/60),'0#'),':',FORMAT((DATEDIFF(MINUTE, [DateTime], nextDateTime)%60),'0#')) as [Время нахождения]
FROM cte 
WHERE (nextMessage='Выход' OR nextMessage IS NULL) AND Message='Вход' AND DateTime >= '2019-08-01 00:0:00' AND DateTime <= '2019-08-31 23:59:00'


На выходе получается следующее:
EmployeeID Дата Дата входа Дата выхода Время нахождения
8DB37C6A-D856-41F3-8B6F-66EA7BB66B0A 05.08.2019 05.08.2019 21:05 05.08.2019 22:14 1:09
8DB37C6A-D856-41F3-8B6F-66EA7BB66B0A 05.08.2019 05.08.2019 22:25 06.08.2019 11:18 12:53
8E2C4C84-6802-41A6-A757-6FF011317719 06.08.2019 06.08.2019 19:47 06.08.2019 20:07 0:20
8E2C4C84-6802-41A6-A757-6FF011317719 06.08.2019 06.08.2019 21:20 06.08.2019 22:50 1:30


Подскажите пожалуйста, как объединять время нахождения по дате и EmployeeID, чтобы на выходе получалось следующее:
EmployeeID Дата Время нахождения
8DB37C6A-D856-41F3-8B6F-66EA7BB66B0A 05.08.2019 14:02
8E2C4C84-6802-41A6-A757-6FF011317719 06.08.2019 1:50
3 ноя 19, 20:00    [22009093]     Ответить | Цитировать Сообщить модератору
 Re: Объединение данных по дате и ID  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 32460
Блог
Lekime,

вам нужно изучить функции работы со временем и группировку
3 ноя 19, 20:13    [22009094]     Ответить | Цитировать Сообщить модератору
 Re: Объединение данных по дате и ID  [new]
Lekime
Member

Откуда:
Сообщений: 5
Я так понял что, сгруппировать нужно так:
GROUP BY [EmployeeID], cast([DateTime] as date)

Но как сложить время?
4 ноя 19, 17:49    [22009457]     Ответить | Цитировать Сообщить модератору
 Re: Объединение данных по дате и ID  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6680
Lekime
Я так понял что, сгруппировать нужно так:
GROUP BY [EmployeeID], cast([DateTime] as date)

Но как сложить время?

DATEDIFF
4 ноя 19, 17:52    [22009460]     Ответить | Цитировать Сообщить модератору
 Re: Объединение данных по дате и ID  [new]
Remind
Member

Откуда: UK
Сообщений: 454
Lekime,

SUM(DATEDIFF(MINUTE, '0:00:00', [Время нахождения])),
SUM(DATEDIFF(MINUTE, [DateTime], nextDateTime))
4 ноя 19, 17:57    [22009464]     Ответить | Цитировать Сообщить модератору
 Re: Объединение данных по дате и ID  [new]
Lekime
Member

Откуда:
Сообщений: 5
То есть получается так?

WITH cte AS (SELECT *, 
             LEAD( [DateTime]) OVER (PARTITION BY EmployeeID ORDER BY DateTime ASC) nextDateTime, 
             LEAD([Message]) OVER (PARTITION BY EmployeeID ORDER BY DateTime ASC) nextMessage
             FROM  Log )

SELECT [EmployeeID], cast([DateTime] as date) as [Дата],
SUM(DATEDIFF(MINUTE, '0:00:00', CONCAT(FORMAT((DATEDIFF(MINUTE, [DateTime], nextDateTime)/60),'0#'),':',FORMAT((DATEDIFF(MINUTE, [DateTime], nextDateTime)%60),'0#')))),
SUM(DATEDIFF(MINUTE, [DateTime], nextDateTime))
FROM cte 
WHERE (nextMessage='Выход' OR nextMessage IS NULL) AND Message='Вход' AND DateTime >= '2019-08-01 00:0:00' AND DateTime <= '2019-08-31 23:59:00'

GROUP BY [EmployeeID], cast([DateTime] as date)


Пишет ошибку преобразования даты или времени из символьной строки.
4 ноя 19, 18:42    [22009478]     Ответить | Цитировать Сообщить модератору
 Re: Объединение данных по дате и ID  [new]
Remind
Member

Откуда: UK
Сообщений: 454
Lekime,

WITH cte AS (SELECT *, 
             LEAD( [DateTime]) OVER (PARTITION BY EmployeeID ORDER BY DateTime ASC) nextDateTime, 
             LEAD([Message]) OVER (PARTITION BY EmployeeID ORDER BY DateTime ASC) nextMessage
             FROM  Log )

SELECT [EmployeeID], cast([DateTime] as date) as [Дата],
CONCAT(FORMAT(SUM(DATEDIFF(MINUTE, [DateTime], nextDateTime)/60),'0#'),':',FORMAT(SUM(DATEDIFF(MINUTE, [DateTime], nextDateTime)%60),'0#')) as [Время нахождения]
FROM cte 
WHERE (nextMessage='Выход' OR nextMessage IS NULL) AND Message='Вход' AND DateTime >= '2019-08-01 00:0:00' AND DateTime <= '2019-08-31 23:59:00'

GROUP BY [EmployeeID], cast([DateTime] as date)

Не забудьте что для последнего входа за день у вас nextDateTime = NULL.
4 ноя 19, 19:06    [22009489]     Ответить | Цитировать Сообщить модератору
 Re: Объединение данных по дате и ID  [new]
Lekime
Member

Откуда:
Сообщений: 5
Remind,
Спасибо большое, но проблема возникла в следующем, теперь минуты складываются отдельно и часы отдельно.
Я так понимаю, что изначально лучше всё считать в минутах
SUM(DATEDIFF(minute, [DateTime], nextDateTime)) as [Время нахождения]
Но, как потом итоговый результат преобразовать в часы с минутами?
4 ноя 19, 21:34    [22009540]     Ответить | Цитировать Сообщить модератору
 Re: Объединение данных по дате и ID  [new]
Критик
Member

Откуда: Москва / Калуга
Сообщений: 32460
Блог
Lekime,

Поделить на 60?
И посмотреть в справке насчет операции %
4 ноя 19, 22:40    [22009569]     Ответить | Цитировать Сообщить модератору
 Re: Объединение данных по дате и ID  [new]
Remind
Member

Откуда: UK
Сообщений: 454
Lekime,

Правильно расставить скобки :)

WITH cte AS (SELECT *, 
             LEAD( [DateTime]) OVER (PARTITION BY EmployeeID ORDER BY DateTime ASC) nextDateTime, 
             LEAD([Message]) OVER (PARTITION BY EmployeeID ORDER BY DateTime ASC) nextMessage
             FROM  Log )

SELECT [EmployeeID], cast([DateTime] as date) as [Дата],
CONCAT(FORMAT(SUM(DATEDIFF(MINUTE, [DateTime], nextDateTime))/60,'0#'),':',FORMAT(SUM(DATEDIFF(MINUTE, [DateTime], nextDateTime))%60,'0#')) as [Время нахождения]
FROM cte 
WHERE (nextMessage='Выход' OR nextMessage IS NULL) AND Message='Вход' AND DateTime >= '2019-08-01 00:0:00' AND DateTime <= '2019-08-31 23:59:00'

GROUP BY [EmployeeID], cast([DateTime] as date)
4 ноя 19, 22:40    [22009570]     Ответить | Цитировать Сообщить модератору
 Re: Объединение данных по дате и ID  [new]
Lekime
Member

Откуда:
Сообщений: 5
Remind,

Спасибо вам огромное!
4 ноя 19, 23:35    [22009605]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить