Select com condição

Question

Olá, estou com um select aqui, ele faz a linha atual (horas) menos a linha anterior, me mostrando a diferença de um valor para o outro a cada hora.

Os valores da tabela vão crescendo a cada hora até 4000000, depois cai para 0. O problema é que nessa passagem ele me mostra o valor de -4000000 no select.

Teria alguma forma de não exibir este valor? Gostaria que a as horas continuassem exibindo normalmente, mas que esse valor são seja -4000000.

Obs: Não posso mudar os valores na tabela.

Segue o script e as imagens do resultado atual.

SELECT
	E3TimeStamp Horario,
	MAX(MGE01_4_KWh) - (SELECT TOP 1 MAX(MGE01_4_KWh)
		FROM [BDMI_EIXOLESTE].[dbo].[Tab_TotEn_UTR_04] b
		WHERE a.E3TimeStamp > b.E3TimeStamp AND E3TimeStamp >= '28-05-2020 23:00:00'
		GROUP BY E3TimeStamp
		ORDER BY b.E3TimeStamp DESC) AS MGE01,
	MAX(MGE02_4_KWh) - (SELECT TOP 1 MAX(MGE02_4_KWh)
		FROM [BDMI_EIXOLESTE].[dbo].[Tab_TotEn_UTR_04] b
		WHERE a.E3TimeStamp > b.E3TimeStamp AND E3TimeStamp >= '28-05-2020 23:00:00'
		GROUP BY E3TimeStamp
		ORDER BY b.E3TimeStamp DESC) AS MGE02,
	MAX(MGE03_4_KWh) - (SELECT TOP 1 MAX(MGE03_4_KWh)
		FROM [BDMI_EIXOLESTE].[dbo].[Tab_TotEn_UTR_04] b
		WHERE a.E3TimeStamp > b.E3TimeStamp AND E3TimeStamp >= '28-05-2020 23:00:00'
		GROUP BY E3TimeStamp
		ORDER BY b.E3TimeStamp DESC) AS MGE03
FROM [BDMI_EIXOLESTE].[dbo].[Tab_TotEn_UTR_04] a
WHERE E3TimeStamp >= '29-05-2020 00:00:00' AND E3TimeStamp <= '29-05-2020 23:59:59'
GROUP BY E3TimeStamp
ORDER BY E3TimeStamp

Esses foram os valores do select.

Valores na tabela.........

---

Yohrannes

Answer 1

Boa tarde,

Experimente fazer uns testes dessa forma para ver se é obtido o resultado esperado:

WITH CTE_ORIG AS
(
  SELECT
	E3TimeStamp Horario,
	MAX(MGE01_4_KWh) - (SELECT TOP 1 MAX(MGE01_4_KWh)
		FROM [BDMI_EIXOLESTE].[dbo].[Tab_TotEn_UTR_04] b
		WHERE a.E3TimeStamp > b.E3TimeStamp AND E3TimeStamp >= '28-05-2020 23:00:00'
		GROUP BY E3TimeStamp
		ORDER BY b.E3TimeStamp DESC) AS MGE01,
	MAX(MGE02_4_KWh) - (SELECT TOP 1 MAX(MGE02_4_KWh)
		FROM [BDMI_EIXOLESTE].[dbo].[Tab_TotEn_UTR_04] b
		WHERE a.E3TimeStamp > b.E3TimeStamp AND E3TimeStamp >= '28-05-2020 23:00:00'
		GROUP BY E3TimeStamp
		ORDER BY b.E3TimeStamp DESC) AS MGE02,
	MAX(MGE03_4_KWh) - (SELECT TOP 1 MAX(MGE03_4_KWh)
		FROM [BDMI_EIXOLESTE].[dbo].[Tab_TotEn_UTR_04] b
		WHERE a.E3TimeStamp > b.E3TimeStamp AND E3TimeStamp >= '28-05-2020 23:00:00'
		GROUP BY E3TimeStamp
		ORDER BY b.E3TimeStamp DESC) AS MGE03
  FROM [BDMI_EIXOLESTE].[dbo].[Tab_TotEn_UTR_04] a
  WHERE E3TimeStamp >= '29-05-2020 00:00:00' AND E3TimeStamp <= '29-05-2020 23:59:59'
  GROUP BY E3TimeStamp
)

SELECT
	Horario,
	CASE WHEN MGE01 < 0 THEN 4000000 - MGE01 ELSE MGE01 END AS MGE01,
	MGE02,
	MGE03
FROM CTE_ORIG
ORDER BY Horario

Espero que ajude

---

Assinatura: http://www.imoveisemexposicao.com.br

Answer 2

Consegui resolver aqui, obrigado pela dica.

---

Yohrannes