Несколько объединений в одной таблице: статусы перехода
У меня есть таблица T-SQL, в которой много статусов перехода. Для каждой пары переходов (AVA_FROM -> AVA_TO) есть метка времени, и я должен рассчитать каждую продолжительность от статуса к статусу. Я не могу понять, как написать сценарий SQL для моих нужд.
Ниже я написал предложение для создания таблицы в SQL Fiddle. Каждая запись со значением в AVA_TO = R-PC является началом работы, в то время как каждая запись со значением в AVA_FROM = R-PC является остановкой работы. Все записи с одним и тем же AVA_PRIMARYID относятся к одному рабочему заданию, поэтому их необходимо суммировать. Можете ли вы помочь мне решить такую проблему?
CREATE TABLE R5AUDVALUES
(
AVA_PRIMARYID nvarchar(8),
AVA_FROM nvarchar(8),
AVA_TO nvarchar(8),
AVA_CHANGED datetime,
AVA_UPDATED nvarchar(1)
);
INSERT INTO R5AUDVALUES (AVA_PRIMARYID, AVA_FROM, AVA_TO, AVA_CHANGED, AVA_UPDATED)
VALUES ('44730', 'Q', 'R-PC', '2020-12-14 12:00:00', '+'),
('44730', 'R-PC', 'SSP4', '2020-12-14 12:15:00', '+'),
('44730', 'SSP4', 'R-PC', '2020-12-14 12:30:00', '+'),
('44730', 'R-PC', 'SSP5', '2020-12-14 12:45:00', '+'),
('44730', 'SSP5', 'R-PC', '2020-12-14 13:00:00', '+'),
('44730', 'R-PC', 'C', '2020-12-14 13:15:00', '+')
Результат должен быть примерно следующим:
WorkOrder, WorkStart, WorkStop
'44730', 2020-12-14 12:00:00', '2020-12-14 12:15:00',
'44730', 2020-12-14 12:30:00', '2020-12-14 12:45:00',
'44730', 2020-12-14 13:00:00', '2020-12-14 13:15:00'
Возможно, лид-функция может вам помочь.
Ответы 1
Если набор данных обычный, просто row_number() и присоединяйтесь.
with c as (
select AVA_PRIMARYID, AVA_FROM, AVA_TO, AVA_CHANGED, AVA_UPDATED, row_number()over (order by AVA_CHANGED) n
from R5AUDVALUES
)
select a.*, b.AVA_CHANGED, datediff(minute, a.AVA_CHANGED, b.AVA_CHANGED) minutes
from c a
join c b on a.n+1 = b.n and a.AVA_TO = 'R-PC' and b.AVA_FROM = 'R-PC'
В противном случае вам может потребоваться сначала выполнить очистку данных.
Спасибо, это работает, но у меня есть еще 2 проблемы: оператор "with" не работает в моем механизме отчетов и row_number() тоже. В моем механизме отчетов есть «графический» «счетчик», но я не знаю, то же ли это.
@over, отметьте свой вопрос специальными тегами. Мое решение для sql-server.
Хорошо, моя база данных - sql-сервер, и она работает в SQL Fiddle, как я и ожидал, но по какой-то странной причине оператор with не работает в моем механизме отчетов... не знаю почему.
@over, если ваш движок отправляет with в пакете, предыдущая команда в пакете должна заканчиваться точкой с запятой. Попробуйте ;with ..
Несколько дней назад я уже тестировал ";с..." и "с", так как другой пользователь в другой теме дал мне предложение "с", и я подумал, что точка с запятой была опечаткой. Поскольку этот запрос не работал, я начал пробовать очень легкий базовый оператор with (с точкой с запятой и без нее в качестве начального символа), и в результате Cognos Advanced Reporting выдавал только загадочные ошибки.
@over, я рекомендую вам открыть еще один вопрос о поведении Cognos Advanced Reporting.
Я не сдался и погуглил о странном поведении Cognos. Наконец-то я нашел «скрытый» совет по настройке движка отчетов и… вуаля. Теперь ваш запрос работает. Большое спасибо.
Я не могу понять, как работает "двойной псевдоним" (т.е. вы написали ... from c a... или join c b...). Это первый раз, когда я вижу в операторе запроса. Можете ли вы указать мне дополнительную информацию о ? Спасибо
Посмотрите, например, эту тему stackoverflow.com/questions/7383753/…
Я проверил больше заявления, которое вы мне предоставили, но есть некоторые проблемы; Я добавил больше записей с другим AVA_PRYMARYID и датой и временем между предыдущими AVA_PRYMARY; результат получается смешанным, более того, мне нужно отфильтровать определенный AVA_PRIMARYID, а я не смог. Я добавил следующие записи в свой предыдущий набор данных: ('44740', 'Q', 'R-PC', '2020-12-14 13:01:00', '+'), ('44740', 'R-PC', 'C', '2020-12-14 13:45:00', '+') См. также вашу скрипку db<>
Я также не могу понять, где находится определение "a.*"... Я знаю, что это связано с R5AUDVALUES, но я не вижу, где это определение. Спасибо.
Я играл с «order by» в функции row_number() и получил правильный результат, в любом случае я до сих пор не могу понять, где находится определение таблицы «a» в операторе sql. Пока.
Отредактируйте свой вопрос и покажите нужные результаты.