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

Откуда:
Сообщений: 2
Доброе время суток. Дорогие друзья, помогите, пожалуйста с такой проблемой.

Есть выгрузка из бд такого рода.

Агент Статус Начало (время вхождения в статус) Конец (время выхода из статуса)
Ваня Вышел 21.06.2019 19:55 22.06.2019 13:55
Ваня Готов 22.06.2019 13:55 22.06.2019 13:58
Ваня Готов 22.06.2019 13:58 22.06.2019 13:59
Ваня Обрабатывает 22.06.2019 13:59 22.06.2019 14:02
Ваня Перерыв 22.06.2019 14:02 22.06.2019 14:10
Ваня Готов 22.06.2019 14:10 22.06.2019 14:15
Ваня Обрабатывает 22.06.2019 14:15 22.06.2019 14:18


Необходимо сделать подсчет времени нахождения в каждом статусе и начало смены.
Все сделал кроме смен, подсчитать и сгруппировать легко. но вот столкнулся с такой проблемой. Данные записываются так как выше указал. Статус "вышел", говорит, что пользователь закрыл программу, а значит смена закончилась, что показывает колонка "начало", а колонка "Конец" говорит о том что он вышел из этого статуса (то есть вошел в систему). У тех у кого смена в одном дне, я могу выгрузить, просто так же группирую и нахожу первый и последний статус. Но вот когда у агента рабочий график допустим с 19 до 07, то тут проблема уже есть. Ведь мне надо сгруппировать по рабочему дню. А тут смена затрагивает сразу 2 дня, как мне получить нужные мне данный.

Грубо говоря, что есть сейчас. Я считаю общее время нахождения в статусах, за дату беру первый статус и время вхождения в него (То есть самый первый статус "готов" 22.06.2019 ) группирую по дате и агенту записи и если в этот день я встречаю первый и последний статус то все ок. Но если агент работал после 00:00, значит день новый и подсчет уже идет по другому, как новая запись.

Как мне надо построить логику запроса? я не могу думать на sql(

Сообщение было отредактировано: 22 июл 19, 20:35
22 июл 19, 20:24    [21932089]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно сгруппировать данные и получить правильный график  [new]
vikkiv
Member

Откуда: London
Сообщений: 2702
Vincent_low92
Как мне надо построить логику запроса? я не могу думать на sql(

SQL пока тут не причём (можно хоть в Excel и пр.)
для начала неплохо-бы определится с задачами,
т.к. например время нахождения в каждом статусе можно предположить что будет = конец-начало
а с началом смены - тут уже чисто от бизнеса зависит
как только будет определено что считать началом смены
(кроме колонны "начало" - не хватает другой логики, например необходимый "статус" и т.д.)
то и дальше можно уже решать в зависимости от определений.

с графиком - такая-же ерунда: нужно поставить цели что и как нужно отображать,
в зависимости от них будут правильные и неправильные пути реализации
22 июл 19, 21:02    [21932120]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно сгруппировать данные и получить правильный график  [new]
invm
Member

Откуда: Москва
Сообщений: 9345
set language russian;

declare @t table (agent varchar(30), state varchar(30), state_start datetime, state_end datetime);
insert into @t
values
('Ваня', 'Вышел', '21.06.2019 19:55', '22.06.2019 13:55'),
('Ваня', 'Готов', '22.06.2019 13:55', '22.06.2019 13:58'),
('Ваня', 'Готов', '22.06.2019 13:58', '22.06.2019 13:59'),
('Ваня', 'Обрабатывает', '22.06.2019 13:59', '22.06.2019 14:02'),
('Ваня', 'Перерыв', '22.06.2019 14:02', '22.06.2019 14:10'),
('Ваня', 'Готов', '22.06.2019 14:10', '22.06.2019 14:15'),
('Ваня', 'Обрабатывает', '22.06.2019 14:15', '22.06.2019 14:18'),
('Ваня', 'Обрабатывает', '23.06.2019 14:15', '23.06.2019 14:18'),

('Ваня', 'Вышел', '21.07.2020 19:55', '23.07.2020 13:55'),
('Ваня', 'Готов', '23.07.2020 13:55', '23.07.2020 13:58'),
('Ваня', 'Готов', '23.07.2020 13:58', '23.07.2020 13:59'),
('Ваня', 'Обрабатывает', '23.07.2020 13:59', '23.07.2020 14:02'),
('Ваня', 'Перерыв', '23.07.2020 14:02', '23.07.2020 14:10'),
('Ваня', 'Готов', '23.07.2020 14:10', '23.07.2020 14:15'),
('Ваня', 'Обрабатывает', '23.07.2020 14:15', '23.07.2020 14:18');

with t as
(
 select
  agent, state, state_end as session_start,
  lead(state_start, 1, '99991231') over (partition by agent order by state_end) as session_end
 from
  @t
 where
  state = 'Вышел'
)
select
 a.agent, a.session_start, b.state, sum(datediff(mi, b.state_start, b.state_end))
from
 t a join
 @t b on b.agent = a.agent and b.state_start > a.session_start and b.state_end < a.session_end
group by
 a.agent, a.session_start, b.state;
22 июл 19, 21:14    [21932126]     Ответить | Цитировать Сообщить модератору
 Re: Как правильно сгруппировать данные и получить правильный график  [new]
Vincent_low92
Member

Откуда:
Сообщений: 2
Спасибо большое!
23 июл 19, 14:51    [21932740]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить