Вычислить разницу во времени для нескольких строк с помощью SQL Server 2008
У меня есть 2 таблицы, 01 - текущий статус, а 01 - статус завершения. Я хочу рассчитать разницу во времени для двух строк с одинаковыми PO_NO, MANAGEMENT_NO, PROCESS_NAME. У каждого PROCESS_NAME есть СТАТУС (начало / конец).
ID INDEXNO PO_NO ITEM_CD MANAGEMENT_NO SEQ PROCESS_NAME STATUS Time_Occurrence TimeDiff (Minute)
43 126690 GV12762 332393961 616244 6 RFID Start 17-03-18 13:28 NULL
44 126690 GV12762 332393961 616244 6 RFID Finish 17-03-18 13:29 0
49 141646 GV14859 7E7060100 619005 2 Imprint Start 19-03-18 13:23 NULL
50 141646 GV14859 7E7060100 619005 2 Imprint Finish 19-03-18 13:30 7
48 141646 GV14859 7E7060100 619005 1 R.M.Requisition Start 19-03-18 13:18 NULL
56 141646 GV14859 7E7060100 619005 1 R.M.Requisition Finish 19-03-18 15:54 156
Ожидаемый результат: столбец TimeDiff (Minute)
select PO_NO, [MANAGEMENT_NO],[STATUS] [Time_Occurrence],
datediff(minute, (isnull((select [Time_Occurrence] from [TBL_FINISH_STATUS] t1 where t1.id=t2.id-1), dateadd(dd, 0, datediff(dd, 0, getdate())))), [Time_Occurrence])TimeDiff
from [PROC_MN].[dbo].[TBL_FINISH_STATUS] t2
ORDER BY PO_NO,MANAGEMENT_NO,ITEM_CD,Time_Occurrence
С приведенным выше запросом результат далеко не соответствует ожидаемому результату.
Может ли кто-нибудь мне помочь? Примечание: столбец ID (48,56) SEQ 1 из PO_NO: GV14859
Ответы 2
Не совсем понятно, чего вы ожидаете в результате. Глядя на ваш образец данных, дизайн с самого начала выглядит некорректным. Для базы данных SQL слишком много избыточности. Возможно, у вас нет никакого контроля над существующей базой данных. Во всяком случае, это можно решить N разными способами, и если моя память не ошибочна, функции LEAD \ LAG не существовали в SQL server 2008 (но row_number существует как другое решение). Я попытался создать что-то, что даже совместимо со старыми версиями, но не уверен, что в результате вы имели в виду именно это:
DECLARE @myTable TABLE([ID] INT,
[INDEXNO] INT,
[PO_NO] VARCHAR(7),
[ITEM_CD] VARCHAR(10),
[MANAGEMENT_NO] INT,
[SEQ] INT,
[PROCESS_NAME] VARCHAR(15),
[STATUS] VARCHAR(6),
[Time_Occurrence] DATETIME,
[TimeDiff] VARCHAR(4));
INSERT INTO @myTable([ID], [INDEXNO], [PO_NO], [ITEM_CD], [MANAGEMENT_NO], [SEQ], [PROCESS_NAME], [STATUS], [Time_Occurrence], [TimeDiff])
VALUES(43, 126690, 'GV12762', '332393961', 616244, 6, 'RFID', 'Start', '20180317 13:28', NULL),
(44, 126690, 'GV12762', '332393961', 616244, 6, 'RFID', 'Finish', '20180317 13:29', '0'),
(49, 141646, 'GV14859', '7E7060100', 619005, 2, 'Imprint', 'Start', '20180319 13:23', NULL),
(50, 141646, 'GV14859', '7E7060100', 619005, 2, 'Imprint', 'Finish', '20180319 13:30', '7'),
(48, 141646, 'GV14859', '7E7060100', 619005, 1, 'R.M.Requisition', 'Start', '20180318 13:18', NULL),
(56, 141646, 'GV14859', '7E7060100', 619005, 1, 'R.M.Requisition', 'Finish', '20180318 15:54', '156');
SELECT * FROM @myTable;
WITH
Starters AS (
SELECT ID, PO_NO, [MANAGEMENT_NO], [PROCESS_NAME], [Time_Occurrence]
FROM @myTable
WHERE STATUS='Start'
),
Finishers AS (
SELECT ID, PO_NO, [MANAGEMENT_NO], [PROCESS_NAME], [Time_Occurrence]
FROM @myTable
WHERE STATUS='Finish'
)
SELECT s.PO_NO, s.MANAGEMENT_NO, s.PROCESS_NAME,
s.Time_Occurrence as [Start], f.Time_Occurrence as [End],
DATEDIFF(MINUTE, s.Time_Occurrence, f.Time_Occurrence) AS TIMEdiff
FROM Starters s
LEFT JOIN Finishers f ON s.PO_NO=f.PO_NO
AND s.MANAGEMENT_NO=f.MANAGEMENT_NO
AND f.PROCESS_NAME=s.PROCESS_NAME;
Если я понимаю, что вы хотите, то это кажется простым запросом:
select INDEXNO, PO_NO, ITEM_CD, MANAGEMENT_NO, SEQ,
datediff(minute,
min(case when status = 'Start' then Time_Occurrence end),
max(case when status = 'Finish' then Time_Occurrence end)
) as timediff
from t
group by INDEXNO, PO_NO, ITEM_CD, MANAGEMENT_NO, SEQ;
Здесь - это скрипт SQL.
Предложение: вместо простого копирования и вставки данных, если вы предоставите им согласованные разделители (например, запятую), будет намного проще создавать и тестировать данные для всех.