Estoy comprobando el índice que falta en mi consulta y, si es así, omito la fila en SQL. Pero debido a que hay demasiadas comprobaciones, la consulta tarda mucho tiempo y recibe un error de tiempo de espera en Power Query. Mi consulta SQL
SELECT em.EqmmspvDate,
em.EqmmspvEqmId,
em.EqmmspvValue,
em.Rownumber,
em.t
FROM (
SELECT e.[eqmmspveqmid],
e.[eqmmspvdate],
e.[eqmmspvvalue],
e.t,
e.rownumber,
Lag(e.rownumber, 1, 0)
OVER(
partition BY [eqmmspveqmid]
ORDER BY Min(Datediff(hh, [eqmmspvdate], Getdate())) DESC)
AS
'previuos_rownumber'
FROM (SELECT Row_number()
OVER(
partition BY [eqmmspveqmid]
ORDER BY Min(Datediff(hh, [eqmmspvdate], Getdate())) DESC) AS
Rownumber,
[eqmmspveqmid],
[eqmmspvdate],
Lag(eqmmspvvalue, 1, 0)
OVER(
partition BY [eqmmspveqmid]
ORDER BY Min(Datediff(hh, [eqmmspvdate], Getdate())) DESC) AS
'previuos_kilometerage',
Lead(eqmmspvvalue, 1, 0)
OVER(
partition BY [eqmmspveqmid]
ORDER BY Min(Datediff(hh, [eqmmspvdate], Getdate())) DESC) AS
'next_kilometerage',
Max(Max(eqmmspvvalue))
OVER(
partition BY [eqmmspveqmid]
ORDER BY Min(Datediff(hh, [eqmmspvdate], Getdate())) DESC) AS
'Last_value',
eqmmspvvalue,
[eqmmspvdifference] AS t
FROM [ULTIMO].[dba].[equipmentmspvalue] AS e
WHERE e.eqmmspvrecstatus = 2
AND e.[eqmmspvmspid] = 'DEFAULT'
GROUP BY [eqmmspveqmid],
eqmmspvdate,
[eqmmspvvalue],
[eqmmspvdifference]
) AS e
INNER JOIN #temp AS te
ON te.rownumber = e.rownumber
WHERE e.EqmmspvValue >= e.previuos_kilometerage
GROUP BY e.[eqmmspveqmid],
e.[eqmmspvdate],
e.eqmmspvvalue,
e.t,
e.rownumber) as em
WHERE em.previuos_rownumber + 1 = em.Rownumber
AND previuos_rownumber <> 0
El bottelneck está aquí :
Lag(e.rownumber, 1, 0)
OVER(
partición BY [eqmmspveqmid]
PEDIDO POR Min(Datediff(hh, [eqmmspvdate], Getdate())) DESC)
Como
'previuos_rownumber'
¿Podemos hacer lo mismo en Dax?
saludos
Ezz
