none
CTE recursiva entre duas colunas do tipo data RRS feed

  • Discussão Geral

  • Olá Pessoal boa tarde,

     Preciso de uma ajuda na montagem da minha lógica através de um cursor, um while que eu consiga trazer o resultado esperado.

    Tenho uma tabela com as seguitnes colunas: 

    SELECT [Cod_Empresa]
          ,[Nr_Pedido]
          ,[Nr_Semana]
          ,[Nº]
          ,[data_registro]
          ,[Início_Operação]
          ,[Fim_Operação]
          ,[Cod_Origem]
          ,[Cod_Destino]
          ,[Horário_Partida]
          ,[Horário_Chegada]
    
              FROM [Teste].[dbo].[tb_dev1]

    E me tras como resultado uma amostra de dados como esta:

    Cod_Empresa Nr_Voo Nr_Semana Nº_SIROS data_registro Início_Operação Fim_Operação Cod_Origem Cod_Destino Horário_Partida Horário_Chegada
    AZU 2411 1 AZU-000563551 7/5/2018 7/12/2018 7/16/2018 SBCF SBGR 11:00:00 AM 12:30:00 PM
    AZU 2411 1 AZU-000591077 7/12/2018 7/23/2018 8/18/2018 SBCF SBGR 11:00:00 AM 12:30:00 PM
    AZU 2411 1 AZU-000591079 7/12/2018 8/20/2018 9/2/2018 SBCF SBGR 11:00:00 AM 12:30:00 PM
    AZU 2411 1 AZU-000591080 7/12/2018 9/3/2018 9/9/2018 SBCF SBGR 11:00:00 AM 12:30:00 PM
    AZU 2411 1 AZU-000591081 7/12/2018 9/10/2018 10/7/2018 SBCF SBGR 11:00:00 AM 12:30:00 PM
    AZU 2411 1 AZU-000591082 7/12/2018 10/8/2018 10/14/2018 SBCF SBGR 11:00:00 AM 12:30:00 PM
    AZU 2411 1 AZU-000591083 7/12/2018 10/15/2018 10/27/2018 SBCF SBGR 11:00:00 AM 12:30:00 PM
    AZU 2411 1 AZU-000585534 7/12/2018 10/28/2018 11/3/2018 SBCF SBGR 11:00:00 AM 12:30:00 PM
    AZU 2411 1 AZU-000585535 7/12/2018 11/4/2018 2/16/2019 SBCF SBGR 10:00:00 AM 11:30:00 AM
    AZU 2411 1 AZU-000585536 7/12/2018 2/17/2019 3/30/2019 SBCF SBGR 11:00:00 AM 12:30:00 PM
    AZU 2411 1 AZU-000583854 7/12/2018 3/31/2019 7/19/2019 SBCF SBGR 11:00:00 AM 12:30:00 PM

    Observem na imagem nas colunas datas estão pintadas em blocos, divididos em 5 blocos através de cinco cores para ilustrar.

    Isto porque os dados vem dividido num arquivo, mas ao analisar percebemos que é a mesma informação dentro de um período. 

    Exemplo o bloco laranja, que inicia em 20/08/2018 e termia em 03/11/2018, se divide em 6 linhas mas preciso apresentar uma única, um unico resultado. 

    Preciso analisar entre o inicio e o fim destas datas, o que fazem sentido dentro de um mesmo bloco e apresentar apenas uma linha deste bloco.

    Para comparar tenho sempre o Nr_semana que é 1 a 7, o Nr_voo, o horario de partida e horario de chegada

    Mas o que ainda não está fazendo sentido para escrever no código é como analisar o inicio e o fim da operação em um só bloco.  

    Eu estou tentando utilizar um cursor e variaveis

    DECLARE @DataIni AS Date  
    DECLARE @DataFim AS Date 
    DECLARE @Departure as Time
    DECLARE @Arrival as Time
    DECLARE @FlNum as varchar
    Declare @cursor as int
     
    Set @cursor= 1
    
    --Set @DataFim= DateAdd(day,7,@DataIni)
    --Set @DataFim= '20180701'
    
    
    
    
    
    	  WHILE @cursor = 1
    
    		SELECT TOP 1000 [Cod_Empresa]
    		  ,[Nr_Voo]
    		  ,[Nr_Semana]
    		  ,[Nº_SIROS]
    		  ,[data_registro]
    		  ,[Início_Operação]
    		  ,[Fim_Operação]
    		  ,[Cod_Origem]
    		  ,[Cod_Destino]
    		  ,[Horário_Partida]
    		  ,[Horário_Chegada]
    		  ,[Capture_Datetime]
    	  FROM [Planejamento].[dbo].[tb_Siros]
    	  WHERE
    			[Nr_Semana] = 1
    			and Cod_Empresa = 'AZU'
    			and Cod_Origem = 'SBCF'
    			and Cod_Destino = 'SBGR'
    			and [Nr_Voo] = @FlNum

    Mas dai em diante não saio do lugar.

    Alguém conseguiu entender, consegue me auxiliar?

    Percebi um padrão da coluna de fim_operação que quando se soma um, o resultado é o valor da coluna de data de inicio da linha seguinte. 

    algo como:

    + fim_Operação   Início_Operação
    1 7/17/2018 7/12/2018
    1 8/19/2018 7/23/2018
    1 9/3/2018 8/20/2018
    1 9/10/2018 9/3/2018
    1 10/8/2018 9/10/2018
    1 10/15/2018 10/8/2018
    1 10/28/2018 10/15/2018
    1 11/4/2018 10/28/2018
    1 2/17/2019 11/4/2018
    1 3/31/2019 2/17/2019
    1 7/20/2019 3/31/2019

    Talvez um IF dentro do cursor resolveria

    Algo como If fim_operação + 1 = data_inicio da próxima linha for igual eu paro de processar e pego a data max, senão eu continuo até sair fora do bloco e imprimir o resultado

    

    Obrigadaaa

    Thais

     



    sexta-feira, 13 de julho de 2018 16:16

Todas as Respostas

  • Boa tarde,

    Experimente fazer uns testes com o script abaixo onde é utilizada uma CTE recursiva para verificar a sequencia dos períodos para agrupamento posterior:

    set dateformat mdy;
    
    declare @Tabela table
    (
        Cod_Empresa varchar(10),
        Nr_Voo int,
        Nr_Semana int,
        Inicio_Operacao	date,
        Fim_Operacao date,
        Horario_Partida time,
        Horario_Chegada time
    );
    
    insert into @Tabela values
    ('AZU', 2411, 1, '7/12/2018', '7/16/2018', '11:00:00', '12:30:00'),
    ('AZU', 2411, 1, '7/23/2018', '8/18/2018', '11:00:00', '12:30:00'),
    ('AZU', 2411, 1, '8/20/2018', '9/2/2018', '11:00:00', '12:30:00'),
    ('AZU', 2411, 1, '9/3/2018', '9/9/2018', '11:00:00', '12:30:00'),
    ('AZU', 2411, 1, '9/10/2018', '10/7/2018', '11:00:00', '12:30:00'),
    ('AZU', 2411, 1, '10/8/2018', '10/14/2018', '11:00:00', '12:30:00'),
    ('AZU', 2411, 1, '10/15/2018', '10/27/2018', '11:00:00', '12:30:00'),
    ('AZU', 2411, 1, '10/28/2018', '11/3/2018', '11:00:00', '12:30:00'),
    ('AZU', 2411, 1, '11/4/2018', '2/16/2019', '10:00:00', '11:30:00'),
    ('AZU', 2411, 1, '2/17/2019', '3/30/2019', '11:00:00', '12:30:00'),
    ('AZU', 2411, 1, '3/31/2019', '7/19/2019', '11:00:00', '12:30:00');
    
    with
        CTE_RN as
        (
            select
                Cod_Empresa,
                Nr_Voo,
                Nr_Semana,
                Inicio_Operacao,
                Fim_Operacao,
                Horario_Partida,
                Horario_Chegada,
                row_number() over 
                    (partition by Cod_Empresa, Nr_Voo
                     order by Inicio_Operacao, Fim_Operacao) as RN
            from @Tabela
        ),
        
        CTE_Rec as
        (
            select 
                *,
                Inicio_Operacao as Inicio_Operacao_Grupo
            from CTE_RN
            where
                RN = 1
                
            union all
            
            select
                c.*,
                case when 
                    c.Cod_Empresa = r.Cod_Empresa and 
                    c.Nr_Voo = r.Nr_Voo and
                    c.Nr_Semana = r.Nr_Semana and
                    c.Horario_Partida = r.Horario_Partida and
                    c.Horario_Chegada = r.Horario_Chegada and
                    c.Inicio_Operacao = dateadd(day, 1, r.Fim_Operacao)
                    then r.Inicio_Operacao_Grupo
                    else c.Inicio_Operacao
                end                 
            from CTE_Rec as r
            inner join CTE_RN as c
                on c.RN = r.RN + 1
        )
    
    select 
        Cod_Empresa,
        Nr_Voo,
        Nr_Semana,
        Inicio_Operacao_Grupo as Inicio_Operacao,
        max(Fim_Operacao) as Fim_Operacao,
        Horario_Partida,
        Horario_Chegada
    from CTE_Rec
    group by
        Cod_Empresa,
        Nr_Voo,
        Nr_Semana ,
        Inicio_Operacao_Grupo,
        Horario_Partida,
        Horario_Chegada
    

    Espero que ajude


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

    sexta-feira, 13 de julho de 2018 19:22
  • Deleted
    sexta-feira, 13 de julho de 2018 22:27
  • Deleted
    sábado, 14 de julho de 2018 23:57
  • Eu achei brilhante o gaps and islands e sua complexidade. Estarei estudando muito sobre isso. Obrigada
    segunda-feira, 16 de julho de 2018 12:14
  • Oii José, mais uma vez obrigada pela ajuda e principalmente explicação, me ajudou muito a me encontrar na lógica e código. Obrigada
    segunda-feira, 16 de julho de 2018 12:15
  • http://www.imoveisemexposicao.com.br muito obrigada pela conhecimento também, me ajudou muito a entender a minha logica e reescrever. Obrigada
    segunda-feira, 16 de julho de 2018 12:24