Usuário com melhor resposta
Select com condição

Pergunta
-
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
Respostas
-
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
- Marcado como Resposta Yohrannes Santos Bigoli segunda-feira, 7 de dezembro de 2020 20:26
Todas as Respostas
-
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
- Marcado como Resposta Yohrannes Santos Bigoli segunda-feira, 7 de dezembro de 2020 20:26
-