CTE recursiva entre duas colunas do tipo data

Question

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

 

Answer 1

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

Answer 2

Deleted

Answer 3

Deleted

Answer 4

Eu achei brilhante o gaps and islands e sua complexidade. Estarei estudando muito sobre isso. Obrigada

Answer 5

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

Answer 6

http://www.imoveisemexposicao.com.br muito obrigada pela conhecimento também, me ajudou muito a entender a minha logica e reescrever. Obrigada