Ir para conteúdo

Pesquisar na Comunidade

Mostrando resultados para as tags ''linhas redundantes''.

  • Pesquisar por Tags

    Digite tags separadas por vírgulas
  • Pesquisar por Autor

Tipo de Conteúdo


Calendários

  • Comunidade iMasters

Todas as áreas do Fórum

  • Q&A Desenvolvimento
    • Perguntas e respostas rápidas
  • Desenvolvimento Web
    • Desenvolvimento frontend
    • Javascript
    • PHP
    • Ruby
    • Python
    • Java
    • .NET
    • Docker, Kubernets e outros ambientes
    • Desenvolvimento com Wordpress
    • Desenvolvimento de apps
    • Desenvolvimento ágil
    • Desenvolvimento de Games
    • Banco de Dados
    • Design e UX
    • Algoritmos & Outras Tecnologias
  • Entretenimento e uso pessoal
    • Segurança & Malwares
    • Geral
    • Boteco iMasters

Encontrar resultados em...

Encontrar resultados que...


Data de Criação

  • Início

    FIM


Data de Atualização

  • Início

    FIM


Filtrar pelo número de...

Data de Registro

  • Início

    FIM


Grupo


Google+


Hangouts


Skype


Twitter


deviantART


Github


Flickr


LinkedIn


Pinterest


Facebook


Site Pessoal


Localização


Interesses

Encontrado 1 registro

  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
×

Informação importante

Ao usar o fórum, você concorda com nossos Termos e condições.