none
Select com condição RRS feed

  • 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

    segunda-feira, 7 de dezembro de 2020 17:49

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

    segunda-feira, 7 de dezembro de 2020 18:49

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

    segunda-feira, 7 de dezembro de 2020 18:49
  • Consegui resolver aqui, obrigado pela dica.

    Yohrannes

    segunda-feira, 7 de dezembro de 2020 20:26