Я работаю над тем, что будет новой функцией, но я пытаюсь понять, как определить разницу во времени между двумя строками каждый раз, когда значение появляется в двух других столбцах (prev_value, current_value). Затем разница будет преобразована в сумму для окончательного значения для каждого идентификатора.
Приведенный ниже пример представляет собой небольшую часть таблицы, поэтому, если мы используем идентификатор 194422, надеюсь, я смогу донести свою точку зрения.
Datediff, который я пытаюсь вычислить, будет между каждой записью, которая имеет Current_Value 12026 и Prev_Value 12026. Таким образом, между двумя верхними строками я ожидаю 3-минутную разницу, а между строками 3 и 4 я ожидаю 4-минутную разницу. .
Если строка содержит текущее_значение и предыдущее_значение 12026, то я бы также включил это во время, поэтому в строках 13,14,15 я ожидаю разницу в 9 минут между строками 13 и 15.
Затем я группировал и суммировал эти результаты по идентификатору.
ROWID| ID | Columnname | prev_value | current_value | OperationTime
1 |197684 | STATUSID | 1 | 12026 | 2020-02-11 13:37:00.0010
2 |197684 | STATUSID | 12026 | 1 | 2020-02-11 13:40:00.000
3 |197684 | STATUSID | 1 | 12026 | 2020-02-11 13:44:00.000
4 |197684 | STATUSID | 12026 | 1 | 2020-02-11 13:48:00.000
5 |198662 | STATUSID | 1 | 12026 | 2020-02-24 15:10:00.000
6 |198662 | STATUSID | 12026 | 1 | 2020-02-24 15:20:00.000
7 |198662 | STATUSID | 1 | 12026 | 2020-02-24 15:23:00.000
8 |198662 | STATUSID | 12026 | 1 | 2020-02-24 15:41:00.000
9 |198662 | STATUSID | 1 | 12026 | 2020-02-24 16:24:00.000
10 |198662 | STATUSID | 12026 | 1 | 2020-02-24 17:05:00.000
11 |194422 | STATUSID | 1 | 12026 | 2020-02-25 09:04:00.000
12 |194422 | STATUSID | 12026 | 8 | 2020-02-25 09:07:00.000
13 |198662 | STATUSID | 1 | 12026 | 2020-02-26 15:32:00.000
14 |198662 | STATUSID | 12026 | 12026 | 2020-02-26 15:40:00.000
15 |198662 | STATUSID | 12026 | 1 | 2020-02-26 15:41:00.000
16 |194422 | STATUSID | 1 | 12026 | 2020-03-02 16:06:00.000
17 |194422 | STATUSID | 12026 | 8 | 2020-03-02 16:15:00.000
В конечном результате тогда будет:
RowID | TimeSpent(Mins)
194422 | 9
197684 | 7
198662 | 78
Теперь я пробовал несколько разных методов, но записи либо отсутствуют, либо извлекаются неправильные времена.
Я просмотрел CTE с INNER и LEFT JOIN и OUTER JOIN на CTE, я попробовал два CTE, я просмотрел LAG, RANK и ROW_NUMBER() во всех примерах.
Приведенный ниже скрипт вроде работает, но не извлекает все мои результаты, а в этом примере отсутствует идентификатор 194422.
DECLARE @STATUSID INT
SET @STATUSID = 12026
;WITH CTE
AS (SELECT prev_value,
current_value,
OPERATIONTIME,
ROWID,
ID,
DENSE_RANK () OVER (PARTITION BY prev_value ORDER BY operationtime) AS ROWNUMBER
FROM AUDITREVIEW
WHERE columnname = 'STATUSID'
AND ( current_value = @STATUSID
OR prev_value = @STATUSID )
)
SELECT *,
DATEDIFF(MINUTE, CTE2.cte2OPERATIONTIME, CTE1.operationtime) as t
INTO #Temp
FROM
CTE AS CTE1
OUTER APPLY
(SELECT top 1
prev_value as cte2prev_value,
current_value cte2current_value,
OPERATIONTIME cte2OPERATIONTIME,
ROWID cte2rowID,
ID cte2ID,
DENSE_RANK () OVER ( ORDER BY operationtime) AS cte2ROWNUMBER
FROM CTE
WHERE CTE.ID = CTE1.ID
AND CTE.PREV_VALUE = CTE1.CURRENT_VALUE
AND CTE.ROWNUMBER < CTE1.ROWNUMBER
ORDER BY CTE.OPERATIONTIME DESC
) CTE2
--WHERE CTE1.WORKORDERID = 194422
SELECT SUM(t), ID
FROM #Temp
WHERE cte2prev_value <> @STATUSID
GROUP BY ID
DROP TABLE #Temp