Jump to content

Search the Community

Showing results for tags 'linhas redundantes'.



More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


Forums

  • Q&A Desenvolvimento
    • Perguntas e respostas rápidas
  • Desenvolvimento e Banco de Dados
    • HTML e CSS
    • Java
    • Javascript
    • .NET
    • PHP
    • Python
    • Ruby
    • Mobile
    • Ambientes de Desenvolvimento
    • Arquitetura e Métodos Ágeis
    • Banco de Dados
    • DevOps
    • Desenvolvimento de Games
    • E-Commerce e Pagamentos Online
    • SEO e Otimizações
    • WordPress
    • Algoritmos & Outras Tecnologias
  • Design e Produto
    • Fotografia
    • Photoshop
    • Design de interfaces e UX
    • Edição/Produção de Vídeos
    • Marketing Online
    • Desenho, Ilustração e 3D
  • Entretenimento e uso pessoal
    • Geral
    • Segurança & Malwares
    • Gadgets e wearable
    • Softwares e Apps
    • Entretenimento

Find results in...

Find results that contain...


Date Created

  • Start

    End


Last Updated

  • Start

    End


Filter by number of...

Joined

  • Start

    End


Group


Google+


Hangouts


Skype


Twitter


deviantART


Github


Flickr


LinkedIn


Pinterest


Facebook


Site Pessoal


Localização


Interesses

Found 1 result

  1. Aloysio Coutinho

    Eliminar linhas redundantes

    Bom dia pessoAll, Tenho uma query que retorna o seguinte resultado: Como vocês podem perceber, tenho linhas redundantes que significam que estes "Jobs" foram reexecutados por algum motivo. Gostaria de trazer nesta tabela apenas a linha que contém a informação de "Rerun" de maior valor. Não estou conseguindo fazer isso. Será que vocês poderiam me ajudar? Detalhe: Todos os dados desta query estão em uma tabela. Apenas o Rerun está em outra. Estou fazendo um "inner join". A query está aí. É complexa por causa do cálculo de duração, mas meu foco está em eliminar as linhas redundantes: declare @data_inicial varchar(20) declare @data_final varchar(20) set @data_inicial = '2019-05-02 00:00:00' set @data_final = '2019-05-02 23:59:59' select H.name as [Job Name], CASE WHEN H.status_code = 140 THEN 'FAILED' WHEN H.status_code = 180 THEN 'SKIPPED' WHEN H.status_code = 190 THEN 'FINISHED' WHEN H.status_code = 200 THEN 'SUCCESS' END as Status, E.attempt_count as Rerun, CASE WHEN LEFT(CONVERT(varchar,H.start_time, 20), 20) between @verao_inicial and @verao_final THEN CONVERT(varchar(10),DATEADD(hh,-2,H.start_time),103) + ' ' + CONVERT(varchar(8),DATEADD(hh,-2,H.start_time),108) WHEN LEFT(CONVERT(varchar,H.start_time, 20), 20) not between @verao_inicial and @verao_final THEN CONVERT(varchar(10),DATEADD(hh,-3,H.start_time),103) + ' ' + CONVERT(varchar(8),DATEADD(hh,-3,H.start_time),108) END as [Data Inicial], CASE WHEN LEFT(CONVERT(varchar,H.end_time, 20), 20) between @verao_inicial and @verao_final THEN CONVERT(varchar(10),DATEADD(hh,-2,H.end_time),103) + ' ' + CONVERT(varchar(8),DATEADD(hh,-2,H.end_time),108) WHEN LEFT(CONVERT(varchar,H.end_time, 20), 20) not between @verao_inicial and @verao_final THEN CONVERT(varchar(10),DATEADD(hh,-3,H.end_time),103) + ' ' + CONVERT(varchar(8),DATEADD(hh,-3,H.end_time),108) END as [Data Final], CASE WHEN (DATEDIFF(ss, H.start_time, H.end_time) > 7200 and (DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60 > 1 and DATEDIFF(ss, H.start_time, H.end_time) % 60 > 1) THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' horas ' + Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minutos ' + Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundos' WHEN (DATEDIFF(ss, H.start_time, H.end_time) > 7200 and (DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60 > 1 and DATEDIFF(ss, H.start_time, H.end_time) % 60 = 1) THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' horas ' + Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minutos ' + Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundo' WHEN (DATEDIFF(ss, H.start_time, H.end_time) > 7200 and (DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60 > 1 and DATEDIFF(ss, H.start_time, H.end_time) % 60 = 0) THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' horas ' + Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minutos ' WHEN (DATEDIFF(ss, H.start_time, H.end_time) > 7200 and (DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60 = 1 and DATEDIFF(ss, H.start_time, H.end_time) % 60 = 0) THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' horas ' + Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minuto ' WHEN (DATEDIFF(ss, H.start_time, H.end_time) > 7200 and (DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60 = 1 and DATEDIFF(ss, H.start_time, H.end_time) % 60 > 1) THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' horas ' + Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minuto ' + Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundos' WHEN (DATEDIFF(ss, H.start_time, H.end_time) > 7200 and (DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60 = 1 and DATEDIFF(ss, H.start_time, H.end_time) % 60 = 1) THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' horas ' + Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minuto ' + Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundo' WHEN (DATEDIFF(ss, H.start_time, H.end_time) > 7200 and (DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60 = 0 and DATEDIFF(ss, H.start_time, H.end_time) % 60 > 1) THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' horas ' + Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundos' WHEN (DATEDIFF(ss, H.start_time, H.end_time) > 7200 and (DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60 = 0 and DATEDIFF(ss, H.start_time, H.end_time) % 60 = 1) THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' horas ' + Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundo' ---------------------- Hora singular WHEN (DATEDIFF(ss, H.start_time, H.end_time) >= 3600 and DATEDIFF(ss, H.start_time, H.end_time) < 7200 and ((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) > 1 and DATEDIFF(ss, H.start_time, H.end_time) % 60 > 1) THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' hora ' + Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minutos ' + Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundos' WHEN (DATEDIFF(ss, H.start_time, H.end_time) >= 3600 and DATEDIFF(ss, H.start_time, H.end_time) < 7200 and ((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) > 1 and DATEDIFF(ss, H.start_time, H.end_time) % 60 = 1) THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' hora ' + Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minutos ' + Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundo' WHEN (DATEDIFF(ss, H.start_time, H.end_time) >= 3600 and DATEDIFF(ss, H.start_time, H.end_time) < 7200 and ((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) > 1 and DATEDIFF(ss, H.start_time, H.end_time) % 60 = 0) THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' hora ' + Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minutos ' WHEN (DATEDIFF(ss, H.start_time, H.end_time) >= 3600 and DATEDIFF(ss, H.start_time, H.end_time) < 7200 and ((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) = 1 and DATEDIFF(ss, H.start_time, H.end_time) % 60 = 0) THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' hora ' + Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minuto ' WHEN (DATEDIFF(ss, H.start_time, H.end_time) >= 3600 and DATEDIFF(ss, H.start_time, H.end_time) < 7200 and ((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) = 1 and DATEDIFF(ss, H.start_time, H.end_time) % 60 > 1) THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' hora ' + Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minuto ' + Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundos' WHEN (DATEDIFF(ss, H.start_time, H.end_time) >= 3600 and DATEDIFF(ss, H.start_time, H.end_time) < 7200 and ((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) = 1 and DATEDIFF(ss, H.start_time, H.end_time) % 60 = 1) THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' hora ' + Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minuto ' + Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundo' WHEN (DATEDIFF(ss, H.start_time, H.end_time) >= 3600 and DATEDIFF(ss, H.start_time, H.end_time) < 7200 and (DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60 = 0 and DATEDIFF(ss, H.start_time, H.end_time) % 60 > 1) THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' hora ' + Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundos' WHEN (DATEDIFF(ss, H.start_time, H.end_time) >= 3600 and DATEDIFF(ss, H.start_time, H.end_time) < 7200 and (DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60 = 0 and DATEDIFF(ss, H.start_time, H.end_time) % 60 = 1) THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' hora ' + Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundo' WHEN (DATEDIFF(ss, H.start_time, H.end_time) = 3600) THEN Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) / 3600) as varchar) + ' hora ' ---------------- Minutos plural WHEN DATEDIFF(ss, H.start_time, H.end_time) >= 120 and DATEDIFF(ss, H.start_time, H.end_time) < 3600 and DATEDIFF(ss, H.start_time, H.end_time) % 60 > 1 THEN Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minutos ' + Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundos' WHEN DATEDIFF(ss, H.start_time, H.end_time) >= 120 and DATEDIFF(ss, H.start_time, H.end_time) < 3600 and DATEDIFF(ss, H.start_time, H.end_time) % 60 = 1 THEN Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minutos ' + Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundo' WHEN DATEDIFF(ss, H.start_time, H.end_time) >= 120 and DATEDIFF(ss, H.start_time, H.end_time) < 3600 and DATEDIFF(ss, H.start_time, H.end_time) % 60 = 0 THEN Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minutos ' ---------------- Minuto singular WHEN DATEDIFF(ss, H.start_time, H.end_time) > 60 and DATEDIFF(ss, H.start_time, H.end_time) < 120 and DATEDIFF(ss, H.start_time, H.end_time) % 60 > 1 THEN Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minuto ' + Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundos' WHEN DATEDIFF(ss, H.start_time, H.end_time) > 60 and DATEDIFF(ss, H.start_time, H.end_time) < 120 and DATEDIFF(ss, H.start_time, H.end_time) % 60 = 1 THEN Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minuto ' + Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundo' WHEN DATEDIFF(ss, H.start_time, H.end_time) = 60 THEN Right('00' + Cast( Floor((DATEDIFF(ss, H.start_time, H.end_time) % 3600) / 60) as varchar), 2) + ' minuto ' WHEN DATEDIFF(ss, H.start_time, H.end_time) > 1 and DATEDIFF(ss, H.start_time, H.end_time) < 60 THEN Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundos' WHEN DATEDIFF(ss, H.start_time, H.end_time) <= 1 THEN Right('00' + Cast( Floor(DATEDIFF(ss, H.start_time, H.end_time) % 60) as varchar), 2) + ' segundo' ELSE '0' END as Duração from dbo.ops_history H inner join dbo.ops_exec E on H.task_id = E.task_id where H.name like 'br_cbss%' and H.start_time is not null and LEFT(CONVERT(varchar, DATEADD(hh,-3,H.sys_updated_on), 20), 20) between @data_inicial and @data_final and H.status_code in (140,180,190,200) group by H.name, H.status_code, E.attempt_count, H.start_time, H.end_time, H.sys_updated_on order by H.sys_updated_on, E.attempt_count desc De ante mão agradeço a colaboração. Aloysio Coutinho
×

Important Information

Ao usar o fórum, você concorda com nossos Terms of Use.