none
Erro no código - copiar o resultado do próximo e colar no atual somente quando for Null

    Pergunta

  • Bom dia,

    O codigo abaixo fica dando erros

    Meu sql e 2014

    Fica dando erro 

    Msg 102, Level 15, State 1, Line 100
    Incorrect syntax near 'T'.

    No IS NULL

    Msg 156, Level 15, State 1, Line 126
    Incorrect syntax near the keyword 'IS'.

    E nas virgulas depois de cada NDO no fim do IF

    NDO6 = IIF(R.NDO6  IS NULL ,( SELECT TOP 1 T.NDO6 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO6),

    Msg 102, Level 15, State 1, Line 133
    Incorrect syntax near ','.

    ;
    WITH    Dados
              AS ( SELECT   T.LegOrigin ,
                            T.LegDestination ,
                            T.FlightNumber ,
                            [NextFlightNumber] = DENSE_RANK() OVER ( PARTITION  BY T.LegOrigin ORDER BY T.FlightNumber ) ,
                            T.DepartureDate ,
                            T.Class ,
                            T.NDO0 ,
                            T.NDO1 ,
                            T.NDO2 ,
                            T.NDO3 ,
                            T.NDO4 ,
                            T.NDO5 ,
                            T.NDO6 ,
                            T.NDO7
    --T.NDO8,
    --T.NDO9,
    --T.NDO10 
                   FROM     ##amostraAUH AS T
                 )
        SELECT  R.LegOrigin ,
                R.LegDestination ,
                R.FlightNumber ,
                R.NextFlightNumber ,
                R.DepartureDate ,
                R.Class ,
                NDO0 = IIF(R.NDO0 IS NULL, ( SELECT TOP 1
                                                    T.NDO0
                                             FROM   Dados AS T
                                             WHERE  R.Class = T.Class
                                                    AND R.NextFlightNumber + 1 = T.NextFlightNumber
                                           ), R.NDO0) ,
                NDO1 = IIF(R.NDO1 IS NULL, ( SELECT TOP 1
                                                    T.NDO1
                                             FROM   Dados AS T
                                             WHERE  R.Class = T.Class
                                                    AND R.NextFlightNumber + 1 = T.NextFlightNumber
                                           ), R.NDO1) ,
                NDO2 = IIF(R.NDO2 IS NULL, ( SELECT TOP 1
                                                    T.NDO2
                                             FROM   Dados AS T
                                             WHERE  R.Class = T.Class
                                                    AND R.NextFlightNumber + 1 = T.NextFlightNumber
                                           ), R.NDO2) ,
                NDO3 = IIF(R.NDO3 IS NULL, ( SELECT TOP 1
                                                    T.NDO3
                                             FROM   Dados AS T
                                             WHERE  R.Class = T.Class
                                                    AND R.NextFlightNumber + 1 = T.NextFlightNumber
                                           ), R.NDO3) ,
                NDO4 = IIF(R.NDO4 IS NULL, ( SELECT TOP 1
                                                    T.NDO4
                                             FROM   Dados AS T
                                             WHERE  R.Class = T.Class
                                                    AND R.NextFlightNumber + 1 = T.NextFlightNumber
                                           ), R.NDO4) ,
                NDO5 = IIF(R.NDO5 IS NULL, ( SELECT TOP 1
                                                    T.NDO5
                                             FROM   Dados AS T
                                             WHERE  R.Class = T.Class
                                                    AND R.NextFlightNumber + 1 = T.NextFlightNumber
                                           ), R.NDO5) ,
                NDO6 = IIF(R.NDO6 IS NULL, ( SELECT TOP 1
                                                    T.NDO6
                                             FROM   Dados AS T
                                             WHERE  R.Class = T.Class
                                                    AND R.NextFlightNumber + 1 = T.NextFlightNumber
                                           ), R.NDO6) ,
                NDO7 = IIF(R.NDO7 IS NULL, ( SELECT TOP 1
                                                    T.NDO7
                                             FROM   Dados AS T
                                             WHERE  R.Class = T.Class
                                                    AND R.NextFlightNumber + 1 = T.NextFlightNumber
                                           ), R.NDO7)
        FROM    Dados R;

    segunda-feira, 19 de junho de 2017 14:54

Respostas

  • Olha Thais , com esse resultado acredito que conseguiremos chegar no objetivo 

    veja  que o resultado obtido pela query :

    WITH    cteVoo
              AS ( SELECT   LEFT(Segment, 3) AS LegOrigin ,
                            RIGHT(Segment, 3) AS LegDestination ,
    						FlightNumber, 
                            DepartureDate ,
                            DataVersao ,
                            NDO = DATEDIFF(DAY, DataVersao, DepartureDate) ,
                            [Y] ,
                            [A] ,
                            [B] ,
                            [C] ,
                            [D] ,
                            [E] ,
                            [F] ,
                            [G] ,
                            [H] ,
                            [I] ,
                            [J] ,
                            [K] ,
                            [L] ,
                            [M] ,
                            [N] ,
                            [O] ,
                            [P] ,
                            [Q] ,
                            [R] ,
                            [S] ,
                            [T] ,
                            [U] ,
                            [V] ,
                            [UU] ,
                            Seq = ROW_NUMBER() OVER ( PARTITION BY DepartureDate ORDER BY DataVersao DESC )
                   FROM     [DailyBookings34].[DMBookings].[AvailableUnitsHistory]
                            WITH ( NOLOCK )
                   WHERE    DepartureDate BETWEEN '20161101' AND '20161130'
                            AND DATEDIFF(DAY, DataVersao, DepartureDate) BETWEEN 0
                                                                  AND
                                                                  100
                 ),
            cteU
              AS (
                   SELECT   LegOrigin ,
    			[NextFlightNumber] = DENSE_RANK() OVER ( PARTITION  BY LegOrigin ORDER BY FlightNumber ),
                            LegDestination ,
                            DepartureDate ,
                            NDO ,
                            Class ,
                            X
                   FROM     cteVoo UNPIVOT ( X FOR Class IN ( [Y], [A], [B], [C],
                                                              [D], [E], [F], [G],
                                                              [H], [I], [J], [K],
                                                              [L], [M], [N], [O],
                                                              [P], [Q], [R], [S],
                                                              [T], [U], [V], [UU] ) ) AS U
                 ),
    			 DadosAgregados AS (	 
    	  SELECT
    	    LegOrigin ,
    		LegDestination ,
    		NextFlightNumber,
    		DepartureDate ,
            Class ,
            [0] AS NDO0 ,
            [1] AS NDO1 ,
            [2] AS NDO2 ,
            [3] AS NDO3 ,
            [4] AS NDO4 ,
            [5] AS NDO5 ,
            [6] AS NDO6 ,
            [7] AS NDO7 ,
            [8] AS NDO8 ,
            [9] AS NDO9 
    FROM    cteU PIVOT ( MAX(X) FOR NDO IN ( [0], [1], [2], [3], [4], [5], [6],
                                             [7], [8], [9], [10], [11], [12], [13],
                                             [14], [15], [16], [17], [18], [19],
                                             [20], [21], [22], [23], [24], [25],
                                             [26], [27], [28], [29], [30], [31],
                                             [32], [33], [34], [35], [36], [37],
                                             [38], [39], [40], [41], [42], [43],
                                             [44], [45], [46], [47], [48], [49],
                                             [50], [51], [52], [53], [54], [55],
                                             [56], [57], [58], [59], [60], [61],
                                             [62], [63], [64], [65], [66], [67],
                                             [68], [69], [70], [71], [72], [73],
                                             [74], [75], [76], [77], [78], [79],
                                             [80], [81], [82], [83], [84], [85],
                                             [86], [87], [88], [89], [90], [91],
                                             [92], [93], [94], [95], [96], [97],
                                             [98], [99], [100] ) ) AS P
    
    			 )
    
    SELECT * FROM DadosAgregados R

    tem essa estrutura:

    

    E o resultado obtido pela query de exemplo no post anterior

    DECLARE @Tabela TABLE
        (
          LegLogin VARCHAR(3) DEFAULT ( 'VCP' ) ,
          LegDestino VARCHAR(3) DEFAULT ( 'CNF' ) ,
          flightnumber INT DEFAULT ( 2420 ) ,
          DepartamentoData DATETIME DEFAULT ( DATEFROMPARTS(2017, 1, 13) ) ,
          Class CHAR(1) ,
          NDO0 INT NULL ,
          NDO1 INT NULL ,
          NDO2 INT NULL ,
          NDO3 INT NULL ,
          NDO4 INT NULL ,
          NDO5 INT NULL ,
          NDO6 INT NULL ,
          NDO7 INT NULL
        );
    
    INSERT  INTO @Tabela
    VALUES 
     ( DEFAULT, DEFAULT,DEFAULT, DEFAULT, 'A', 125, 125,   127,  128,  129,  130,  131,  1 ),
     ( DEFAULT, DEFAULT,DEFAULT, DEFAULT, 'B', 125, 125,   127,  128,  129,  130,  131,  1 ),
     ( DEFAULT, DEFAULT,DEFAULT, DEFAULT, 'C', 118, 118,   118,  118,  118,  118,  118,  1 ),
     ( DEFAULT, DEFAULT,DEFAULT, DEFAULT, 'D', 118, 118,   118,  118,  118,  118,  118,  1 ),
     ( DEFAULT, DEFAULT,DEFAULT, DEFAULT, 'E', 125, 125,   127,  128,  129,  130,  130,  1 ),
     ( DEFAULT, DEFAULT,DEFAULT, DEFAULT, 'F', 125, 125,   127,  128,  129,  130,  130,  1 ),
     ( DEFAULT, DEFAULT,DEFAULT, DEFAULT, 'G', 125, 125,   127,  128,  129,  130,  130,  1 ),
     ( DEFAULT, DEFAULT,2463,	 DEFAULT, 'A', NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL ),
     ( DEFAULT, DEFAULT,2463,    DEFAULT, 'B', NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL ),
     ( DEFAULT, DEFAULT,2463,    DEFAULT, 'C', NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL ),
     ( DEFAULT, DEFAULT,2463,    DEFAULT, 'D', NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL ),
     ( DEFAULT, DEFAULT,2463,    DEFAULT, 'E', NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL ),
     ( DEFAULT, DEFAULT,2463,    DEFAULT, 'F', NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL ),
     ( DEFAULT, DEFAULT,2463,    DEFAULT, 'G', NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL ),
     ( DEFAULT, DEFAULT,2487,    DEFAULT, 'A', 125, 125,   127, 128,  129, 130,   130,130 ), 
     ( DEFAULT, DEFAULT,2487,    DEFAULT, 'B', 125, 125,   127, 128,  129, 130,   130,130 ), 
     ( DEFAULT, DEFAULT,2487,    DEFAULT, 'C', 118, 118,   118, 118,  118, 118,   118,118 ), 
     ( DEFAULT, DEFAULT,2487,    DEFAULT, 'D', 118, 118,   118, 118,  118, 118,   118,118 ), 
     ( DEFAULT, DEFAULT,2487,    DEFAULT, 'E', 125, 125,   127, 128,  129, 130,   130,130 ), 
     ( DEFAULT, DEFAULT,2487,    DEFAULT, 'F', 125, 125,   127, 122,  129, 130,   130,130 ), 
     ( DEFAULT, DEFAULT,2487,    DEFAULT, 'G', 125, 125,   114, 109,  116, 130,   130,130 );
    
    
    
     WITH Dados AS (
     SELECT T.LegLogin ,
            T.LegDestino ,
            T.flightnumber ,
    		Proximoflightnumber = DENSE_RANK() OVER(PARTITION BY T.LegLogin ORDER BY T.flightnumber),
            T.DepartamentoData ,
            T.Class ,
            T.NDO0 ,
            T.NDO1 ,
            T.NDO2 ,
            T.NDO3 ,
            T.NDO4 ,
            T.NDO5 ,
            T.NDO6 ,
            T.NDO7 FROM @Tabela AS T
     )
     
     SELECT R.LegLogin ,
            R.LegDestino ,
            R.flightnumber ,
            R.Proximoflightnumber ,
            R.DepartamentoData ,
            R.Class ,
            NDO0 = IIF(R.NDO0  IS NULL ,( SELECT TOP 1 T.NDO0 FROM  Dados AS T WHERE R.Class = T.Class AND R.Proximoflightnumber + 1 =  T.Proximoflightnumber ),R.NDO0),
            NDO1 = IIF(R.NDO1  IS NULL ,( SELECT TOP 1 T.NDO1 FROM  Dados AS T WHERE R.Class = T.Class AND R.Proximoflightnumber + 1 =  T.Proximoflightnumber ),R.NDO1),
            NDO2 = IIF(R.NDO2  IS NULL ,( SELECT TOP 1 T.NDO2 FROM  Dados AS T WHERE R.Class = T.Class AND R.Proximoflightnumber + 1 =  T.Proximoflightnumber ),R.NDO2),
            NDO3 = IIF(R.NDO3  IS NULL ,( SELECT TOP 1 T.NDO3 FROM  Dados AS T WHERE R.Class = T.Class AND R.Proximoflightnumber + 1 =  T.Proximoflightnumber ),R.NDO3),
            NDO4 = IIF(R.NDO4  IS NULL ,( SELECT TOP 1 T.NDO4 FROM  Dados AS T WHERE R.Class = T.Class AND R.Proximoflightnumber + 1 =  T.Proximoflightnumber ),R.NDO4),
            NDO5 = IIF(R.NDO5  IS NULL ,( SELECT TOP 1 T.NDO5 FROM  Dados AS T WHERE R.Class = T.Class AND R.Proximoflightnumber + 1 =  T.Proximoflightnumber ),R.NDO5),
            NDO6 = IIF(R.NDO6  IS NULL ,( SELECT TOP 1 T.NDO6 FROM  Dados AS T WHERE R.Class = T.Class AND R.Proximoflightnumber + 1 =  T.Proximoflightnumber ),R.NDO6),
            NDO7 = IIF(R.NDO7  IS NULL ,( SELECT TOP 1 T.NDO7 FROM  Dados AS T WHERE R.Class = T.Class AND R.Proximoflightnumber + 1 =  T.Proximoflightnumber ),R.NDO7)
    		 FROM Dados R

    foi esse :

    logo acredito que é so inserirmos a logica para obter o próximo valor 

    WITH    cteVoo
              AS ( SELECT   LEFT(Segment, 3) AS LegOrigin ,
                            RIGHT(Segment, 3) AS LegDestination ,
    						FlightNumber, 
                            DepartureDate ,
                            DataVersao ,
                            NDO = DATEDIFF(DAY, DataVersao, DepartureDate) ,
                            [Y] ,
                            [A] ,
                            [B] ,
                            [C] ,
                            [D] ,
                            [E] ,
                            [F] ,
                            [G] ,
                            [H] ,
                            [I] ,
                            [J] ,
                            [K] ,
                            [L] ,
                            [M] ,
                            [N] ,
                            [O] ,
                            [P] ,
                            [Q] ,
                            [R] ,
                            [S] ,
                            [T] ,
                            [U] ,
                            [V] ,
                            [UU] ,
                            Seq = ROW_NUMBER() OVER ( PARTITION BY DepartureDate ORDER BY DataVersao DESC )
                   FROM     [DailyBookings34].[DMBookings].[AvailableUnitsHistory]
                            WITH ( NOLOCK )
                   WHERE    DepartureDate BETWEEN '20161101' AND '20161130'
                            AND DATEDIFF(DAY, DataVersao, DepartureDate) BETWEEN 0
                                                                  AND
                                                                  100
                 ),
            cteU
              AS (
                   SELECT   LegOrigin ,
    			[NextFlightNumber] = DENSE_RANK() OVER ( PARTITION  BY LegOrigin ORDER BY FlightNumber ),
                            LegDestination ,
                            DepartureDate ,
                            NDO ,
                            Class ,
                            X
                   FROM     cteVoo UNPIVOT ( X FOR Class IN ( [Y], [A], [B], [C],
                                                              [D], [E], [F], [G],
                                                              [H], [I], [J], [K],
                                                              [L], [M], [N], [O],
                                                              [P], [Q], [R], [S],
                                                              [T], [U], [V], [UU] ) ) AS U
                 ),
    	 DadosAgregados AS (	 
    	  SELECT
    	    LegOrigin ,
    		LegDestination ,
    		NextFlightNumber,
    		DepartureDate ,
            Class ,
            [0] AS NDO0 ,
            [1] AS NDO1 ,
            [2] AS NDO2 ,
            [3] AS NDO3 ,
            [4] AS NDO4 ,
            [5] AS NDO5 ,
            [6] AS NDO6 ,
            [7] AS NDO7 ,
            [8] AS NDO8 ,
            [9] AS NDO9 
    FROM    cteU PIVOT ( MAX(X) FOR NDO IN ( [0], [1], [2], [3], [4], [5], [6],
                                             [7], [8], [9], [10], [11], [12], [13],
                                             [14], [15], [16], [17], [18], [19],
                                             [20], [21], [22], [23], [24], [25],
                                             [26], [27], [28], [29], [30], [31],
                                             [32], [33], [34], [35], [36], [37],
                                             [38], [39], [40], [41], [42], [43],
                                             [44], [45], [46], [47], [48], [49],
                                             [50], [51], [52], [53], [54], [55],
                                             [56], [57], [58], [59], [60], [61],
                                             [62], [63], [64], [65], [66], [67],
                                             [68], [69], [70], [71], [72], [73],
                                             [74], [75], [76], [77], [78], [79],
                                             [80], [81], [82], [83], [84], [85],
                                             [86], [87], [88], [89], [90], [91],
                                             [92], [93], [94], [95], [96], [97],
                                             [98], [99], [100] ) ) AS P
    
    			 )
    
     SELECT R.LegOrigin ,
    		R.LegDestination ,
    		R.NextFlightNumber,
    		R.DepartureDate ,
            R.Class ,
    	NDO0 = IIF(R.NDO0  IS NULL ,( SELECT TOP 1 T.NDO0 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO0),
            NDO1 = IIF(R.NDO1  IS NULL ,( SELECT TOP 1 T.NDO1 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO1),
            NDO2 = IIF(R.NDO2  IS NULL ,( SELECT TOP 1 T.NDO2 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO2),
            NDO3 = IIF(R.NDO3  IS NULL ,( SELECT TOP 1 T.NDO3 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO3),
            NDO4 = IIF(R.NDO4  IS NULL ,( SELECT TOP 1 T.NDO4 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO4),
            NDO5 = IIF(R.NDO5  IS NULL ,( SELECT TOP 1 T.NDO5 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO5),
            NDO6 = IIF(R.NDO6  IS NULL ,( SELECT TOP 1 T.NDO6 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO6),
            NDO7 = IIF(R.NDO7  IS NULL ,( SELECT TOP 1 T.NDO7 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO7),
    	NDO8 = IIF(R.NDO8  IS NULL ,( SELECT TOP 1 T.NDO8 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO8),
    	NDO9 = IIF(R.NDO9  IS NULL ,( SELECT TOP 1 T.NDO9 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO9)
    		 FROM DadosAgregados R
    
    fiz a query  de

    NDO0 a
    NDO9  apenas para demostrar, mas no seu caso cabe replicar para os demais,

    Wesley Neves

    • Marcado como Resposta Thaís Sakamoto quarta-feira, 21 de junho de 2017 11:38
    quarta-feira, 21 de junho de 2017 11:34

Todas as Respostas

  • 1) posta o código completo por favor, alem disso qual e o nivel de compatibilidade do seu banco ??

    2) A tabela global ##amostraAUH existe no seu contexto, vc consegue rodar um select nela antes da CTE ??

    Wesley Neves
    wesley.si.neves@gmail.com
    Brasilia-DF
    MTA-SQL Server
    Pós-Graduando em Banco de Dados 
    "Se a resposta for útil ou ajudar ,não esqueça de marcar"


    Wesley Neves




    • Editado Wesley Neves segunda-feira, 19 de junho de 2017 15:27 complemento
    segunda-feira, 19 de junho de 2017 15:16
  • Da uma olhada nesse Script ,caso funcione e só replicar 

    WITH    cteVoo
              AS ( SELECT   LEFT(Segment, 3) AS LegOrigin ,
                            RIGHT(Segment, 3) AS LegDestination ,
          -- FlightNumber, 
                            DepartureDate ,
                            DataVersao ,
                            NDO = DATEDIFF(DAY, DataVersao, DepartureDate) ,
                            [Y] ,
                            [A] ,
                            [B] ,
                            [C] ,
                            [D] ,
                            [E] ,
                            [F] ,
                            [G] ,
                            [H] ,
                            [I] ,
                            [J] ,
                            [K] ,
                            [L] ,
                            [M] ,
                            [N] ,
                            [O] ,
                            [P] ,
                            [Q] ,
                            [R] ,
                            [S] ,
                            [T] ,
                            [U] ,
                            [V] ,
                            [UU] ,
         --  Seq= row_number() over (partition by Segment, FlightNumber, DepartureDate order by DataVersao desc)
                            Seq = ROW_NUMBER() OVER ( PARTITION BY DepartureDate ORDER BY DataVersao DESC )
                   FROM     [DailyBookings34].[DMBookings].[AvailableUnitsHistory]
                            WITH ( NOLOCK )
                   WHERE    DepartureDate BETWEEN '20161101' AND '20161130'
                            AND DATEDIFF(DAY, DataVersao, DepartureDate) BETWEEN 0
                                                                  AND
                                                                  100
           -- and Segment = 'VCPCNF' 
         --   and FlightNumber = 4046 
          --  and DepartureDate = '20170101'
                 ),
            cteU
              AS (
    --SELECT LegOrigin, LegDestination, FlightNumber, DepartureDate, NDO, Class, X
                   SELECT   LegOrigin ,
                            LegDestination ,
                            DepartureDate ,
                            NDO ,
                            Class ,
                            X
                   FROM     cteVoo UNPIVOT ( X FOR Class IN ( [Y], [A], [B], [C],
                                                              [D], [E], [F], [G],
                                                              [H], [I], [J], [K],
                                                              [L], [M], [N], [O],
                                                              [P], [Q], [R], [S],
                                                              [T], [U], [V], [UU] ) ) AS U
                 ),
            Dados
              AS ( SELECT   LegOrigin ,
                            LegDestination ,
                            DepartureDate ,
                            Class ,
                            [0] AS NDO0 ,
                            [1] AS NDO1 ,
                            [2] AS NDO2 ,
                            [3] AS NDO3 ,
                            [4] AS NDO4 ,
                            [5] AS NDO5 ,
                            [6] AS NDO6 ,
                            [7] AS NDO7 ,
                            [8] AS NDO8 ,
                            [9] AS NDO9 ,
                            [10] AS NDO10 ,
                            [11] AS NDO11 ,
                            [12] AS NDO12 ,
                            [13] AS NDO13 ,
                            [14] AS NDO14 ,
                            [15] AS NDO15 ,
                            [16] AS NDO16 ,
                            [17] AS NDO17 ,
                            [18] AS NDO18 ,
                            [19] AS NDO19 ,
                            [20] AS NDO20 ,
                            [21] AS NDO21 ,
                            [22] AS NDO22 ,
                            [23] AS NDO23 ,
                            [24] AS NDO24 ,
                            [25] AS NDO25 ,
                            [26] AS NDO26 ,
                            [27] AS NDO27 ,
                            [28] AS NDO28 ,
                            [29] AS NDO29 ,
                            [30] AS NDO30 ,
                            [31] AS NDO31 ,
                            [32] AS NDO32 ,
                            [33] AS NDO33 ,
                            [34] AS NDO34 ,
                            [35] AS NDO35 ,
                            [36] AS NDO36 ,
                            [37] AS NDO37 ,
                            [38] AS NDO38 ,
                            [39] AS NDO39 ,
                            [40] AS NDO40 ,
                            [41] AS NDO41 ,
                            [42] AS NDO42 ,
                            [43] AS NDO43 ,
                            [44] AS NDO44 ,
                            [45] AS NDO45 ,
                            [46] AS NDO46 ,
                            [47] AS NDO47 ,
                            [48] AS NDO48 ,
                            [49] AS NDO49 ,
                            [50] AS NDO50 ,
                            [51] AS NDO51 ,
                            [52] AS NDO52 ,
                            [53] AS NDO53 ,
                            [54] AS NDO54 ,
                            [55] AS NDO55 ,
                            [56] AS NDO56 ,
                            [57] AS NDO57 ,
                            [58] AS NDO58 ,
                            [59] AS NDO59 ,
                            [60] AS NDO60 ,
                            [61] AS NDO61 ,
                            [62] AS NDO62 ,
                            [63] AS NDO63 ,
                            [64] AS NDO64 ,
                            [65] AS NDO65 ,
                            [66] AS NDO66 ,
                            [67] AS NDO67 ,
                            [68] AS NDO68 ,
                            [69] AS NDO69 ,
                            [70] AS NDO70 ,
                            [71] AS NDO71 ,
                            [72] AS NDO72 ,
                            [73] AS NDO73 ,
                            [74] AS NDO74 ,
                            [75] AS NDO75 ,
                            [76] AS NDO76 ,
                            [77] AS NDO77 ,
                            [78] AS NDO78 ,
                            [79] AS NDO79 ,
                            [80] AS NDO80 ,
                            [81] AS NDO81 ,
                            [82] AS NDO82 ,
                            [83] AS NDO83 ,
                            [84] AS NDO84 ,
                            [85] AS NDO85 ,
                            [86] AS NDO86 ,
                            [87] AS NDO87 ,
                            [88] AS NDO88 ,
                            [89] AS NDO89 ,
                            [90] AS NDO90 ,
                            [91] AS NDO91 ,
                            [92] AS NDO92 ,
                            [93] AS NDO93 ,
                            [94] AS NDO94 ,
                            [95] AS NDO95 ,
                            [96] AS NDO96 ,
                            [97] AS NDO97 ,
                            [98] AS NDO98 ,
                            [99] AS NDO99 ,
                            [100] AS NDO100
                   FROM     cteU PIVOT ( MAX(X) FOR NDO IN ( [0], [1], [2], [3],
                                                             [4], [5], [6], [7],
                                                             [8], [9], [10], [11],
                                                             [12], [13], [14],
                                                             [15], [16], [17],
                                                             [18], [19], [20],
                                                             [21], [22], [23],
                                                             [24], [25], [26],
                                                             [27], [28], [29],
                                                             [30], [31], [32],
                                                             [33], [34], [35],
                                                             [36], [37], [38],
                                                             [39], [40], [41],
                                                             [42], [43], [44],
                                                             [45], [46], [47],
                                                             [48], [49], [50],
                                                             [51], [52], [53],
                                                             [54], [55], [56],
                                                             [57], [58], [59],
                                                             [60], [61], [62],
                                                             [63], [64], [65],
                                                             [66], [67], [68],
                                                             [69], [70], [71],
                                                             [72], [73], [74],
                                                             [75], [76], [77],
                                                             [78], [79], [80],
                                                             [81], [82], [83],
                                                             [84], [85], [86],
                                                             [87], [88], [89],
                                                             [90], [91], [92],
                                                             [93], [94], [95],
                                                             [96], [97], [98],
                                                             [99], [100] ) ) AS P
                 )
        SELECT  R.LegOrigin ,
                R.LegDestination ,
                R.DepartureDate ,
                R.Class ,
                NDO0 = IIF(R.NDO0 IS NULL, ( SELECT TOP 1
                                                    T.NDO0
                                             FROM   Dados AS T
                                             WHERE  R.Class = T.Class
                                                    AND R.NextFlightNumber + 1 = T.NextFlightNumber
                                           ), R.NDO0) ,
                NDO1 = IIF(R.NDO1 IS NULL, ( SELECT TOP 1
                                                    T.NDO1
                                             FROM   Dados AS T
                                             WHERE  R.Class = T.Class
                                                    AND R.NextFlightNumber + 1 = T.NextFlightNumber
                                           ), R.NDO1) ,
                NDO2 = IIF(R.NDO2 IS NULL, ( SELECT TOP 1
                                                    T.NDO2
                                             FROM   Dados AS T
                                             WHERE  R.Class = T.Class
                                                    AND R.NextFlightNumber + 1 = T.NextFlightNumber
                                           ), R.NDO2) ,
                NDO3 = IIF(R.NDO3 IS NULL, ( SELECT TOP 1
                                                    T.NDO3
                                             FROM   Dados AS T
                                             WHERE  R.Class = T.Class
                                                    AND R.NextFlightNumber + 1 = T.NextFlightNumber
                                           ), R.NDO3) ,
                NDO4 = IIF(R.NDO4 IS NULL, ( SELECT TOP 1
                                                    T.NDO4
                                             FROM   Dados AS T
                                             WHERE  R.Class = T.Class
                                                    AND R.NextFlightNumber + 1 = T.NextFlightNumber
                                           ), R.NDO4) ,
                NDO5 = IIF(R.NDO5 IS NULL, ( SELECT TOP 1
                                                    T.NDO5
                                             FROM   Dados AS T
                                             WHERE  R.Class = T.Class
                                                    AND R.NextFlightNumber + 1 = T.NextFlightNumber
                                           ), R.NDO5) ,
                NDO6 = IIF(R.NDO6 IS NULL, ( SELECT TOP 1
                                                    T.NDO6
                                             FROM   Dados AS T
                                             WHERE  R.Class = T.Class
                                                    AND R.NextFlightNumber + 1 = T.NextFlightNumber
                                           ), R.NDO6) ,
                NDO7 = IIF(R.NDO7 IS NULL, ( SELECT TOP 1
                                                    T.NDO7
                                             FROM   Dados AS T
                                             WHERE  R.Class = T.Class
                                                    AND R.NextFlightNumber + 1 = T.NextFlightNumber
                                           ), R.NDO7) ,
                NDO8 = IIF(R.NDO8 IS NULL, ( SELECT TOP 1
                                                    T.NDO8
                                             FROM   Dados AS T
                                             WHERE  R.Class = T.Class
                                                    AND R.NextFlightNumber + 1 = T.NextFlightNumber
                                           ), R.NDO8) ,
                NDO9 = IIF(R.NDO9 IS NULL, ( SELECT TOP 1
                                                    T.NDO9
                                             FROM   Dados AS T
                                             WHERE  R.Class = T.Class
                                                    AND R.NextFlightNumber + 1 = T.NextFlightNumber
                                           ), R.NDO9) 
                --R.NDO10 ,
                --R.NDO11 ,
                --R.NDO12 ,
                --R.NDO13 ,
                --R.NDO14 ,
                --R.NDO15 ,
                --R.NDO16 ,
                --R.NDO17 ,
                --R.NDO18 ,
                --R.NDO19 ,
                --R.NDO20 ,
                --R.NDO21 ,
                --R.NDO22 ,
                --R.NDO23 ,
                --R.NDO24 ,
                --R.NDO25 ,
                --R.NDO26 ,
                --R.NDO27 ,
                --R.NDO28 ,
                --R.NDO29 ,
                --R.NDO30 ,
                --R.NDO31 ,
                --R.NDO32 ,
                --R.NDO33 ,
                --R.NDO34 ,
                --R.NDO35 ,
                --R.NDO36 ,
                --R.NDO37 ,
                --R.NDO38 ,
                --R.NDO39 ,
                --R.NDO40 ,
                --R.NDO41 ,
                --R.NDO42 ,
                --R.NDO43 ,
                --R.NDO44 ,
                --R.NDO45 ,
                --R.NDO46 ,
                --R.NDO47 ,
                --R.NDO48 ,
                --R.NDO49 ,
                --R.NDO50 ,
                --R.NDO51 ,
                --R.NDO52 ,
                --R.NDO53 ,
                --R.NDO54 ,
                --R.NDO55 ,
                --R.NDO56 ,
                --R.NDO57 ,
                --R.NDO58 ,
                --R.NDO59 ,
                --R.NDO60 ,
                --R.NDO61 ,
                --R.NDO62 ,
                --R.NDO63 ,
                --R.NDO64 ,
                --R.NDO65 ,
                --R.NDO66 ,
                --R.NDO67 ,
                --R.NDO68 ,
                --R.NDO69 ,
                --R.NDO70 ,
                --R.NDO71 ,
                --R.NDO72 ,
                --R.NDO73 ,
                --R.NDO74 ,
                --R.NDO75 ,
                --R.NDO76 ,
                --R.NDO77 ,
                --R.NDO78 ,
                --R.NDO79 ,
                --R.NDO80 ,
                --R.NDO81 ,
                --R.NDO82 ,
                --R.NDO83 ,
                --R.NDO84 ,
                --R.NDO85 ,
                --R.NDO86 ,
                --R.NDO87 ,
                --R.NDO88 ,
                --R.NDO89 ,
                --R.NDO90 ,
                --R.NDO91 ,
                --R.NDO92 ,
                --R.NDO93 ,
                --R.NDO94 ,
                --R.NDO95 ,
                --R.NDO96 ,
                --R.NDO97 ,
                --R.NDO98 ,
                --R.NDO99 ,
                --R.NDO100
        FROM    Dados R; 
    

    Wesley Neves
    wesley.si.neves@gmail.com
    Brasilia-DF
    MTA-SQL Server
    Pós-Graduando em Banco de Dados 
    "Se a resposta for útil ou ajudar ,não esqueça de marcar"


    Wesley Neves

    segunda-feira, 19 de junho de 2017 20:02
  • Bom dia Thais , acredito que o erro esteja em outro ponto da query ,

    posta por favor o resultado dessa query abaixo

    WITH    cteVoo
              AS ( SELECT   LEFT(Segment, 3) AS LegOrigin ,
                            RIGHT(Segment, 3) AS LegDestination ,
          -- FlightNumber, 
                            DepartureDate ,
                            DataVersao ,
                            NDO = DATEDIFF(DAY, DataVersao, DepartureDate) ,
                            [Y] ,
                            [A] ,
                            [B] ,
                            [C] ,
                            [D] ,
                            [E] ,
                            [F] ,
                            [G] ,
                            [H] ,
                            [I] ,
                            [J] ,
                            [K] ,
                            [L] ,
                            [M] ,
                            [N] ,
                            [O] ,
                            [P] ,
                            [Q] ,
                            [R] ,
                            [S] ,
                            [T] ,
                            [U] ,
                            [V] ,
                            [UU] ,
                            Seq = ROW_NUMBER() OVER ( PARTITION BY DepartureDate ORDER BY DataVersao DESC )
                   FROM     [DailyBookings34].[DMBookings].[AvailableUnitsHistory]
                            WITH ( NOLOCK )
                   WHERE    DepartureDate BETWEEN '20161101' AND '20161130'
                            AND DATEDIFF(DAY, DataVersao, DepartureDate) BETWEEN 0 AND 100
                 )
    			 SELECT * FROM cteVoo

    Wesley Neves
    wesley.si.neves@gmail.com
    Brasilia-DF
    MTA-SQL Server
    Pós-Graduando em Banco de Dados 
    "Se a resposta for útil ou ajudar ,não esqueça de marcar"


    Wesley Neves

    terça-feira, 20 de junho de 2017 11:44
  • Bom dia Thais , acredito que o erro esteja em outro ponto da query ,

    posta por favor o resultado dessa query abaixo

    WITH    cteVoo
              AS ( SELECT   LEFT(Segment, 3) AS LegOrigin ,
                            RIGHT(Segment, 3) AS LegDestination ,
          -- FlightNumber, 
                            DepartureDate ,
                            DataVersao ,
                            NDO = DATEDIFF(DAY, DataVersao, DepartureDate) ,
                            [Y] ,
                            [A] ,
                            [B] ,
                            [C] ,
                            [D] ,
                            [E] ,
                            [F] ,
                            [G] ,
                            [H] ,
                            [I] ,
                            [J] ,
                            [K] ,
                            [L] ,
                            [M] ,
                            [N] ,
                            [O] ,
                            [P] ,
                            [Q] ,
                            [R] ,
                            [S] ,
                            [T] ,
                            [U] ,
                            [V] ,
                            [UU] ,
                            Seq = ROW_NUMBER() OVER ( PARTITION BY DepartureDate ORDER BY DataVersao DESC )
                   FROM     [DailyBookings34].[DMBookings].[AvailableUnitsHistory]
                            WITH ( NOLOCK )
                   WHERE    DepartureDate BETWEEN '20161101' AND '20161130'
                            AND DATEDIFF(DAY, DataVersao, DepartureDate) BETWEEN 0 AND 100
                 )
    			 SELECT * FROM cteVoo

    Wesley Neves
    wesley.si.neves@gmail.com
    Brasilia-DF
    MTA-SQL Server
    Pós-Graduando em Banco de Dados 
    "Se a resposta for útil ou ajudar ,não esqueça de marcar"


    Wesley Neves


    terça-feira, 20 de junho de 2017 13:00
  • Bom dia Thais , acredito que o erro esteja em outro ponto da query ,

    posta por favor o resultado dessa query abaixo

    WITH    cteVoo
              AS ( SELECT   LEFT(Segment, 3) AS LegOrigin ,
                            RIGHT(Segment, 3) AS LegDestination ,
          -- FlightNumber, 
                            DepartureDate ,
                            DataVersao ,
                            NDO = DATEDIFF(DAY, DataVersao, DepartureDate) ,
                            [Y] ,
                            [A] ,
                            [B] ,
                            [C] ,
                            [D] ,
                            [E] ,
                            [F] ,
                            [G] ,
                            [H] ,
                            [I] ,
                            [J] ,
                            [K] ,
                            [L] ,
                            [M] ,
                            [N] ,
                            [O] ,
                            [P] ,
                            [Q] ,
                            [R] ,
                            [S] ,
                            [T] ,
                            [U] ,
                            [V] ,
                            [UU] ,
                            Seq = ROW_NUMBER() OVER ( PARTITION BY DepartureDate ORDER BY DataVersao DESC )
                   FROM     [DailyBookings34].[DMBookings].[AvailableUnitsHistory]
                            WITH ( NOLOCK )
                   WHERE    DepartureDate BETWEEN '20161101' AND '20161130'
                            AND DATEDIFF(DAY, DataVersao, DepartureDate) BETWEEN 0 AND 100
                 )
    			 SELECT * FROM cteVoo

    Wesley Neves
    wesley.si.neves@gmail.com
    Brasilia-DF
    MTA-SQL Server
    Pós-Graduando em Banco de Dados 
    "Se a resposta for útil ou ajudar ,não esqueça de marcar"


    Wesley Neves


    Olha o exemplo fial que quero chegar

    Tenho dois vôos 2432 e 4956

    Porem o 2432 tem valor do NDO0 até o NDO30

    E o 4956 tem valor no NDO30 até o NDO 100

    O 4956 foi substituido pelo 2432 ao longo dos dias

    Etão preciso juntar o resultado dos dois no vôo 2432

    E tenho varios casos assim

    terça-feira, 20 de junho de 2017 13:24
  • Estou pegando o Código completo que vc postou , para saber qual e o trecho correto que  não está adequado 

    agora  novamente posta o resultado da query abaixo por favor

    WITH    cteVoo
              AS ( SELECT   LEFT(Segment, 3) AS LegOrigin ,
                            RIGHT(Segment, 3) AS LegDestination ,
                            DepartureDate ,
                            DataVersao ,
                            NDO = DATEDIFF(DAY, DataVersao, DepartureDate) ,
                            [Y] ,
                            [A] ,
                            [B] ,
                            [C] ,
                            [D] ,
                            [E] ,
                            [F] ,
                            [G] ,
                            [H] ,
                            [I] ,
                            [J] ,
                            [K] ,
                            [L] ,
                            [M] ,
                            [N] ,
                            [O] ,
                            [P] ,
                            [Q] ,
                            [R] ,
                            [S] ,
                            [T] ,
                            [U] ,
                            [V] ,
                            [UU] ,
                            Seq = ROW_NUMBER() OVER ( PARTITION BY DepartureDate ORDER BY DataVersao DESC )
                   FROM     [DailyBookings34].[DMBookings].[AvailableUnitsHistory]
                            WITH ( NOLOCK )
                   WHERE    DepartureDate BETWEEN '20161101' AND '20161130'
                            AND DATEDIFF(DAY, DataVersao, DepartureDate) BETWEEN 0
                                                                  AND
                                                                  100
                 ),
            cteU
              AS ( SELECT   LegOrigin ,
                            LegDestination ,
                            DepartureDate ,
                            NDO ,
                            Class ,
                            X
                   FROM     cteVoo UNPIVOT ( X FOR Class IN ( [Y], [A], [B], [C],
                                                              [D], [E], [F], [G],
                                                              [H], [I], [J], [K],
                                                              [L], [M], [N], [O],
                                                              [P], [Q], [R], [S],
                                                              [T], [U], [V], [UU] ) ) AS U
                 )
        SELECT  *
        FROM    cteU;
    			 

    Wesley Neves
    wesley.si.neves@gmail.com
    Brasilia-DF
    MTA-SQL Server
    Pós-Graduando em Banco de Dados 
    "Se a resposta for útil ou ajudar ,não esqueça de marcar"


    Wesley Neves


    • Editado Wesley Neves terça-feira, 20 de junho de 2017 13:25 Correção
    terça-feira, 20 de junho de 2017 13:25
  • Estou pegando o Código completo que vc postou , para saber qual e o trecho correto que  não está adequado 

    agora  novamente posta o resultado da query abaixo por favor

    WITH    cteVoo
              AS ( SELECT   LEFT(Segment, 3) AS LegOrigin ,
                            RIGHT(Segment, 3) AS LegDestination ,
                            DepartureDate ,
                            DataVersao ,
                            NDO = DATEDIFF(DAY, DataVersao, DepartureDate) ,
                            [Y] ,
                            [A] ,
                            [B] ,
                            [C] ,
                            [D] ,
                            [E] ,
                            [F] ,
                            [G] ,
                            [H] ,
                            [I] ,
                            [J] ,
                            [K] ,
                            [L] ,
                            [M] ,
                            [N] ,
                            [O] ,
                            [P] ,
                            [Q] ,
                            [R] ,
                            [S] ,
                            [T] ,
                            [U] ,
                            [V] ,
                            [UU] ,
                            Seq = ROW_NUMBER() OVER ( PARTITION BY DepartureDate ORDER BY DataVersao DESC )
                   FROM     [DailyBookings34].[DMBookings].[AvailableUnitsHistory]
                            WITH ( NOLOCK )
                   WHERE    DepartureDate BETWEEN '20161101' AND '20161130'
                            AND DATEDIFF(DAY, DataVersao, DepartureDate) BETWEEN 0
                                                                  AND
                                                                  100
                 ),
            cteU
              AS ( SELECT   LegOrigin ,
                            LegDestination ,
                            DepartureDate ,
                            NDO ,
                            Class ,
                            X
                   FROM     cteVoo UNPIVOT ( X FOR Class IN ( [Y], [A], [B], [C],
                                                              [D], [E], [F], [G],
                                                              [H], [I], [J], [K],
                                                              [L], [M], [N], [O],
                                                              [P], [Q], [R], [S],
                                                              [T], [U], [V], [UU] ) ) AS U
                 )
        SELECT  *
        FROM    cteU;
    			 

    Wesley Neves
    wesley.si.neves@gmail.com
    Brasilia-DF
    MTA-SQL Server
    Pós-Graduando em Banco de Dados 
    "Se a resposta for útil ou ajudar ,não esqueça de marcar"


    Wesley Neves


    O resultado é comprido, Vai até NDO 1

    terça-feira, 20 de junho de 2017 14:09
  • 1) posta o código completo por favor, alem disso qual e o nivel de compatibilidade do seu banco ??

    2) A tabela global ##amostraAUH existe no seu contexto, vc consegue rodar um select nela antes da CTE ??

    Wesley Neves
    wesley.si.neves@gmail.com
    Brasilia-DF
    MTA-SQL Server
    Pós-Graduando em Banco de Dados 
    "Se a resposta for útil ou ajudar ,não esqueça de marcar"


    Wesley Neves





    Existe alguma forma de colocar vários FlightNumber no Where?

    Por exemplo sei quais são os vôos que foram substituidos

    Exemplo o 2933 pelo 4044

    o 2599 pelo 7845

    e o 5987 pelo 9432

    dai abaixo algo como:

    where DepartureDate between '20161120' and '20161120' 
            AND datediff(day, DataVersao, DepartureDate) between 0 and 100
            and Segment = 'VCPCNF' 
            and FlightNumber in ('2933','4044')

     and FlightNumber in ('5987','9432')

     and FlightNumber in ('2599','7845')

    terça-feira, 20 de junho de 2017 15:35
  • Thais Seque o código resumido com algumas alterações , 

    para resolver o trecho

    IIF(R.NDO0  IS NULL ,( SELECT TOP 1 T.NDO0 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO0),

    eu preciso de obter duas informações

    1) NextFlightNumber

    2) Class

    da uma olhada nesse resultado , coloquei apenas de

     [0] AS NDO0 ,
            [1] AS NDO1 ,
            [2] AS NDO2 ,
            [3] AS NDO3 ,
            [4] AS NDO4 ,
            [5] AS NDO5 ,
            [6] AS NDO6 ,
            [7] AS NDO7 ,
            [8] AS NDO8 ,
            [9] AS NDO9 

    1)para saber se vai funcionar , se funcionar o passo seguinte e inserir o trecho do IIF na CTE DadosAgregados

    2) se não funcionar posta um script com uns 10 registros e a estrutura da tabela

    [AvailableUnitsHistory]

    assim poderemos usar usa própria query completa para chegar no resultado


    WITH    cteVoo
              AS ( SELECT   LEFT(Segment, 3) AS LegOrigin ,
                            RIGHT(Segment, 3) AS LegDestination ,
    						FlightNumber, 
                            DepartureDate ,
                            DataVersao ,
                            NDO = DATEDIFF(DAY, DataVersao, DepartureDate) ,
                            [Y] ,
                            [A] ,
                            [B] ,
                            [C] ,
                            [D] ,
                            [E] ,
                            [F] ,
                            [G] ,
                            [H] ,
                            [I] ,
                            [J] ,
                            [K] ,
                            [L] ,
                            [M] ,
                            [N] ,
                            [O] ,
                            [P] ,
                            [Q] ,
                            [R] ,
                            [S] ,
                            [T] ,
                            [U] ,
                            [V] ,
                            [UU] ,
                            Seq = ROW_NUMBER() OVER ( PARTITION BY DepartureDate ORDER BY DataVersao DESC )
                   FROM     [DailyBookings34].[DMBookings].[AvailableUnitsHistory]
                            WITH ( NOLOCK )
                   WHERE    DepartureDate BETWEEN '20161101' AND '20161130'
                            AND DATEDIFF(DAY, DataVersao, DepartureDate) BETWEEN 0
                                                                  AND
                                                                  100
                 ),
            cteU
              AS (
                   SELECT   LegOrigin ,
    			[NextFlightNumber] = DENSE_RANK() OVER ( PARTITION  BY LegOrigin ORDER BY FlightNumber ),
                            LegDestination ,
                            DepartureDate ,
                            NDO ,
                            Class ,
                            X
                   FROM     cteVoo UNPIVOT ( X FOR Class IN ( [Y], [A], [B], [C],
                                                              [D], [E], [F], [G],
                                                              [H], [I], [J], [K],
                                                              [L], [M], [N], [O],
                                                              [P], [Q], [R], [S],
                                                              [T], [U], [V], [UU] ) ) AS U
                 ),
    			 DadosAgregados AS (	 
    	  SELECT
    	    LegOrigin ,
    		LegDestination ,
    		NextFlightNumber,
    		DepartureDate ,
            Class ,
            [0] AS NDO0 ,
            [1] AS NDO1 ,
            [2] AS NDO2 ,
            [3] AS NDO3 ,
            [4] AS NDO4 ,
            [5] AS NDO5 ,
            [6] AS NDO6 ,
            [7] AS NDO7 ,
            [8] AS NDO8 ,
            [9] AS NDO9 
    FROM    cteU PIVOT ( MAX(X) FOR NDO IN ( [0], [1], [2], [3], [4], [5], [6],
                                             [7], [8], [9], [10], [11], [12], [13],
                                             [14], [15], [16], [17], [18], [19],
                                             [20], [21], [22], [23], [24], [25],
                                             [26], [27], [28], [29], [30], [31],
                                             [32], [33], [34], [35], [36], [37],
                                             [38], [39], [40], [41], [42], [43],
                                             [44], [45], [46], [47], [48], [49],
                                             [50], [51], [52], [53], [54], [55],
                                             [56], [57], [58], [59], [60], [61],
                                             [62], [63], [64], [65], [66], [67],
                                             [68], [69], [70], [71], [72], [73],
                                             [74], [75], [76], [77], [78], [79],
                                             [80], [81], [82], [83], [84], [85],
                                             [86], [87], [88], [89], [90], [91],
                                             [92], [93], [94], [95], [96], [97],
                                             [98], [99], [100] ) ) AS P
    
    			 )
    
    SELECT * FROM DadosAgregados R


    Wesley Neves

    terça-feira, 20 de junho de 2017 15:42
  • Thais Seque o código resumido com algumas alterações , 

    para resolver o trecho

    IIF(R.NDO0  IS NULL ,( SELECT TOP 1 T.NDO0 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO0),

    eu preciso de obter duas informações

    1) NextFlightNumber

    2) Class

    da uma olhada nesse resultado , coloquei apenas de

     [0] AS NDO0 ,
            [1] AS NDO1 ,
            [2] AS NDO2 ,
            [3] AS NDO3 ,
            [4] AS NDO4 ,
            [5] AS NDO5 ,
            [6] AS NDO6 ,
            [7] AS NDO7 ,
            [8] AS NDO8 ,
            [9] AS NDO9 

    1)para saber se vai funcionar , se funcionar o passo seguinte e inserir o trecho do IIF na CTE DadosAgregados

    2) se não funcionar posta um script com uns 10 registros e a estrutura da tabela

    [AvailableUnitsHistory]

    assim poderemos usar usa própria query completa para chegar no resultado


    WITH    cteVoo
              AS ( SELECT   LEFT(Segment, 3) AS LegOrigin ,
                            RIGHT(Segment, 3) AS LegDestination ,
    						FlightNumber, 
                            DepartureDate ,
                            DataVersao ,
                            NDO = DATEDIFF(DAY, DataVersao, DepartureDate) ,
                            [Y] ,
                            [A] ,
                            [B] ,
                            [C] ,
                            [D] ,
                            [E] ,
                            [F] ,
                            [G] ,
                            [H] ,
                            [I] ,
                            [J] ,
                            [K] ,
                            [L] ,
                            [M] ,
                            [N] ,
                            [O] ,
                            [P] ,
                            [Q] ,
                            [R] ,
                            [S] ,
                            [T] ,
                            [U] ,
                            [V] ,
                            [UU] ,
                            Seq = ROW_NUMBER() OVER ( PARTITION BY DepartureDate ORDER BY DataVersao DESC )
                   FROM     [DailyBookings34].[DMBookings].[AvailableUnitsHistory]
                            WITH ( NOLOCK )
                   WHERE    DepartureDate BETWEEN '20161101' AND '20161130'
                            AND DATEDIFF(DAY, DataVersao, DepartureDate) BETWEEN 0
                                                                  AND
                                                                  100
                 ),
            cteU
              AS (
                   SELECT   LegOrigin ,
    			[NextFlightNumber] = DENSE_RANK() OVER ( PARTITION  BY LegOrigin ORDER BY FlightNumber ),
                            LegDestination ,
                            DepartureDate ,
                            NDO ,
                            Class ,
                            X
                   FROM     cteVoo UNPIVOT ( X FOR Class IN ( [Y], [A], [B], [C],
                                                              [D], [E], [F], [G],
                                                              [H], [I], [J], [K],
                                                              [L], [M], [N], [O],
                                                              [P], [Q], [R], [S],
                                                              [T], [U], [V], [UU] ) ) AS U
                 ),
    			 DadosAgregados AS (	 
    	  SELECT
    	    LegOrigin ,
    		LegDestination ,
    		NextFlightNumber,
    		DepartureDate ,
            Class ,
            [0] AS NDO0 ,
            [1] AS NDO1 ,
            [2] AS NDO2 ,
            [3] AS NDO3 ,
            [4] AS NDO4 ,
            [5] AS NDO5 ,
            [6] AS NDO6 ,
            [7] AS NDO7 ,
            [8] AS NDO8 ,
            [9] AS NDO9 
    FROM    cteU PIVOT ( MAX(X) FOR NDO IN ( [0], [1], [2], [3], [4], [5], [6],
                                             [7], [8], [9], [10], [11], [12], [13],
                                             [14], [15], [16], [17], [18], [19],
                                             [20], [21], [22], [23], [24], [25],
                                             [26], [27], [28], [29], [30], [31],
                                             [32], [33], [34], [35], [36], [37],
                                             [38], [39], [40], [41], [42], [43],
                                             [44], [45], [46], [47], [48], [49],
                                             [50], [51], [52], [53], [54], [55],
                                             [56], [57], [58], [59], [60], [61],
                                             [62], [63], [64], [65], [66], [67],
                                             [68], [69], [70], [71], [72], [73],
                                             [74], [75], [76], [77], [78], [79],
                                             [80], [81], [82], [83], [84], [85],
                                             [86], [87], [88], [89], [90], [91],
                                             [92], [93], [94], [95], [96], [97],
                                             [98], [99], [100] ) ) AS P
    
    			 )
    
    SELECT * FROM DadosAgregados R


    Wesley Neves

    terça-feira, 20 de junho de 2017 17:02
  • Olha Thais , com esse resultado acredito que conseguiremos chegar no objetivo 

    veja  que o resultado obtido pela query :

    WITH    cteVoo
              AS ( SELECT   LEFT(Segment, 3) AS LegOrigin ,
                            RIGHT(Segment, 3) AS LegDestination ,
    						FlightNumber, 
                            DepartureDate ,
                            DataVersao ,
                            NDO = DATEDIFF(DAY, DataVersao, DepartureDate) ,
                            [Y] ,
                            [A] ,
                            [B] ,
                            [C] ,
                            [D] ,
                            [E] ,
                            [F] ,
                            [G] ,
                            [H] ,
                            [I] ,
                            [J] ,
                            [K] ,
                            [L] ,
                            [M] ,
                            [N] ,
                            [O] ,
                            [P] ,
                            [Q] ,
                            [R] ,
                            [S] ,
                            [T] ,
                            [U] ,
                            [V] ,
                            [UU] ,
                            Seq = ROW_NUMBER() OVER ( PARTITION BY DepartureDate ORDER BY DataVersao DESC )
                   FROM     [DailyBookings34].[DMBookings].[AvailableUnitsHistory]
                            WITH ( NOLOCK )
                   WHERE    DepartureDate BETWEEN '20161101' AND '20161130'
                            AND DATEDIFF(DAY, DataVersao, DepartureDate) BETWEEN 0
                                                                  AND
                                                                  100
                 ),
            cteU
              AS (
                   SELECT   LegOrigin ,
    			[NextFlightNumber] = DENSE_RANK() OVER ( PARTITION  BY LegOrigin ORDER BY FlightNumber ),
                            LegDestination ,
                            DepartureDate ,
                            NDO ,
                            Class ,
                            X
                   FROM     cteVoo UNPIVOT ( X FOR Class IN ( [Y], [A], [B], [C],
                                                              [D], [E], [F], [G],
                                                              [H], [I], [J], [K],
                                                              [L], [M], [N], [O],
                                                              [P], [Q], [R], [S],
                                                              [T], [U], [V], [UU] ) ) AS U
                 ),
    			 DadosAgregados AS (	 
    	  SELECT
    	    LegOrigin ,
    		LegDestination ,
    		NextFlightNumber,
    		DepartureDate ,
            Class ,
            [0] AS NDO0 ,
            [1] AS NDO1 ,
            [2] AS NDO2 ,
            [3] AS NDO3 ,
            [4] AS NDO4 ,
            [5] AS NDO5 ,
            [6] AS NDO6 ,
            [7] AS NDO7 ,
            [8] AS NDO8 ,
            [9] AS NDO9 
    FROM    cteU PIVOT ( MAX(X) FOR NDO IN ( [0], [1], [2], [3], [4], [5], [6],
                                             [7], [8], [9], [10], [11], [12], [13],
                                             [14], [15], [16], [17], [18], [19],
                                             [20], [21], [22], [23], [24], [25],
                                             [26], [27], [28], [29], [30], [31],
                                             [32], [33], [34], [35], [36], [37],
                                             [38], [39], [40], [41], [42], [43],
                                             [44], [45], [46], [47], [48], [49],
                                             [50], [51], [52], [53], [54], [55],
                                             [56], [57], [58], [59], [60], [61],
                                             [62], [63], [64], [65], [66], [67],
                                             [68], [69], [70], [71], [72], [73],
                                             [74], [75], [76], [77], [78], [79],
                                             [80], [81], [82], [83], [84], [85],
                                             [86], [87], [88], [89], [90], [91],
                                             [92], [93], [94], [95], [96], [97],
                                             [98], [99], [100] ) ) AS P
    
    			 )
    
    SELECT * FROM DadosAgregados R

    tem essa estrutura:

    

    E o resultado obtido pela query de exemplo no post anterior

    DECLARE @Tabela TABLE
        (
          LegLogin VARCHAR(3) DEFAULT ( 'VCP' ) ,
          LegDestino VARCHAR(3) DEFAULT ( 'CNF' ) ,
          flightnumber INT DEFAULT ( 2420 ) ,
          DepartamentoData DATETIME DEFAULT ( DATEFROMPARTS(2017, 1, 13) ) ,
          Class CHAR(1) ,
          NDO0 INT NULL ,
          NDO1 INT NULL ,
          NDO2 INT NULL ,
          NDO3 INT NULL ,
          NDO4 INT NULL ,
          NDO5 INT NULL ,
          NDO6 INT NULL ,
          NDO7 INT NULL
        );
    
    INSERT  INTO @Tabela
    VALUES 
     ( DEFAULT, DEFAULT,DEFAULT, DEFAULT, 'A', 125, 125,   127,  128,  129,  130,  131,  1 ),
     ( DEFAULT, DEFAULT,DEFAULT, DEFAULT, 'B', 125, 125,   127,  128,  129,  130,  131,  1 ),
     ( DEFAULT, DEFAULT,DEFAULT, DEFAULT, 'C', 118, 118,   118,  118,  118,  118,  118,  1 ),
     ( DEFAULT, DEFAULT,DEFAULT, DEFAULT, 'D', 118, 118,   118,  118,  118,  118,  118,  1 ),
     ( DEFAULT, DEFAULT,DEFAULT, DEFAULT, 'E', 125, 125,   127,  128,  129,  130,  130,  1 ),
     ( DEFAULT, DEFAULT,DEFAULT, DEFAULT, 'F', 125, 125,   127,  128,  129,  130,  130,  1 ),
     ( DEFAULT, DEFAULT,DEFAULT, DEFAULT, 'G', 125, 125,   127,  128,  129,  130,  130,  1 ),
     ( DEFAULT, DEFAULT,2463,	 DEFAULT, 'A', NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL ),
     ( DEFAULT, DEFAULT,2463,    DEFAULT, 'B', NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL ),
     ( DEFAULT, DEFAULT,2463,    DEFAULT, 'C', NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL ),
     ( DEFAULT, DEFAULT,2463,    DEFAULT, 'D', NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL ),
     ( DEFAULT, DEFAULT,2463,    DEFAULT, 'E', NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL ),
     ( DEFAULT, DEFAULT,2463,    DEFAULT, 'F', NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL ),
     ( DEFAULT, DEFAULT,2463,    DEFAULT, 'G', NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL ),
     ( DEFAULT, DEFAULT,2487,    DEFAULT, 'A', 125, 125,   127, 128,  129, 130,   130,130 ), 
     ( DEFAULT, DEFAULT,2487,    DEFAULT, 'B', 125, 125,   127, 128,  129, 130,   130,130 ), 
     ( DEFAULT, DEFAULT,2487,    DEFAULT, 'C', 118, 118,   118, 118,  118, 118,   118,118 ), 
     ( DEFAULT, DEFAULT,2487,    DEFAULT, 'D', 118, 118,   118, 118,  118, 118,   118,118 ), 
     ( DEFAULT, DEFAULT,2487,    DEFAULT, 'E', 125, 125,   127, 128,  129, 130,   130,130 ), 
     ( DEFAULT, DEFAULT,2487,    DEFAULT, 'F', 125, 125,   127, 122,  129, 130,   130,130 ), 
     ( DEFAULT, DEFAULT,2487,    DEFAULT, 'G', 125, 125,   114, 109,  116, 130,   130,130 );
    
    
    
     WITH Dados AS (
     SELECT T.LegLogin ,
            T.LegDestino ,
            T.flightnumber ,
    		Proximoflightnumber = DENSE_RANK() OVER(PARTITION BY T.LegLogin ORDER BY T.flightnumber),
            T.DepartamentoData ,
            T.Class ,
            T.NDO0 ,
            T.NDO1 ,
            T.NDO2 ,
            T.NDO3 ,
            T.NDO4 ,
            T.NDO5 ,
            T.NDO6 ,
            T.NDO7 FROM @Tabela AS T
     )
     
     SELECT R.LegLogin ,
            R.LegDestino ,
            R.flightnumber ,
            R.Proximoflightnumber ,
            R.DepartamentoData ,
            R.Class ,
            NDO0 = IIF(R.NDO0  IS NULL ,( SELECT TOP 1 T.NDO0 FROM  Dados AS T WHERE R.Class = T.Class AND R.Proximoflightnumber + 1 =  T.Proximoflightnumber ),R.NDO0),
            NDO1 = IIF(R.NDO1  IS NULL ,( SELECT TOP 1 T.NDO1 FROM  Dados AS T WHERE R.Class = T.Class AND R.Proximoflightnumber + 1 =  T.Proximoflightnumber ),R.NDO1),
            NDO2 = IIF(R.NDO2  IS NULL ,( SELECT TOP 1 T.NDO2 FROM  Dados AS T WHERE R.Class = T.Class AND R.Proximoflightnumber + 1 =  T.Proximoflightnumber ),R.NDO2),
            NDO3 = IIF(R.NDO3  IS NULL ,( SELECT TOP 1 T.NDO3 FROM  Dados AS T WHERE R.Class = T.Class AND R.Proximoflightnumber + 1 =  T.Proximoflightnumber ),R.NDO3),
            NDO4 = IIF(R.NDO4  IS NULL ,( SELECT TOP 1 T.NDO4 FROM  Dados AS T WHERE R.Class = T.Class AND R.Proximoflightnumber + 1 =  T.Proximoflightnumber ),R.NDO4),
            NDO5 = IIF(R.NDO5  IS NULL ,( SELECT TOP 1 T.NDO5 FROM  Dados AS T WHERE R.Class = T.Class AND R.Proximoflightnumber + 1 =  T.Proximoflightnumber ),R.NDO5),
            NDO6 = IIF(R.NDO6  IS NULL ,( SELECT TOP 1 T.NDO6 FROM  Dados AS T WHERE R.Class = T.Class AND R.Proximoflightnumber + 1 =  T.Proximoflightnumber ),R.NDO6),
            NDO7 = IIF(R.NDO7  IS NULL ,( SELECT TOP 1 T.NDO7 FROM  Dados AS T WHERE R.Class = T.Class AND R.Proximoflightnumber + 1 =  T.Proximoflightnumber ),R.NDO7)
    		 FROM Dados R

    foi esse :

    logo acredito que é so inserirmos a logica para obter o próximo valor 

    WITH    cteVoo
              AS ( SELECT   LEFT(Segment, 3) AS LegOrigin ,
                            RIGHT(Segment, 3) AS LegDestination ,
    						FlightNumber, 
                            DepartureDate ,
                            DataVersao ,
                            NDO = DATEDIFF(DAY, DataVersao, DepartureDate) ,
                            [Y] ,
                            [A] ,
                            [B] ,
                            [C] ,
                            [D] ,
                            [E] ,
                            [F] ,
                            [G] ,
                            [H] ,
                            [I] ,
                            [J] ,
                            [K] ,
                            [L] ,
                            [M] ,
                            [N] ,
                            [O] ,
                            [P] ,
                            [Q] ,
                            [R] ,
                            [S] ,
                            [T] ,
                            [U] ,
                            [V] ,
                            [UU] ,
                            Seq = ROW_NUMBER() OVER ( PARTITION BY DepartureDate ORDER BY DataVersao DESC )
                   FROM     [DailyBookings34].[DMBookings].[AvailableUnitsHistory]
                            WITH ( NOLOCK )
                   WHERE    DepartureDate BETWEEN '20161101' AND '20161130'
                            AND DATEDIFF(DAY, DataVersao, DepartureDate) BETWEEN 0
                                                                  AND
                                                                  100
                 ),
            cteU
              AS (
                   SELECT   LegOrigin ,
    			[NextFlightNumber] = DENSE_RANK() OVER ( PARTITION  BY LegOrigin ORDER BY FlightNumber ),
                            LegDestination ,
                            DepartureDate ,
                            NDO ,
                            Class ,
                            X
                   FROM     cteVoo UNPIVOT ( X FOR Class IN ( [Y], [A], [B], [C],
                                                              [D], [E], [F], [G],
                                                              [H], [I], [J], [K],
                                                              [L], [M], [N], [O],
                                                              [P], [Q], [R], [S],
                                                              [T], [U], [V], [UU] ) ) AS U
                 ),
    	 DadosAgregados AS (	 
    	  SELECT
    	    LegOrigin ,
    		LegDestination ,
    		NextFlightNumber,
    		DepartureDate ,
            Class ,
            [0] AS NDO0 ,
            [1] AS NDO1 ,
            [2] AS NDO2 ,
            [3] AS NDO3 ,
            [4] AS NDO4 ,
            [5] AS NDO5 ,
            [6] AS NDO6 ,
            [7] AS NDO7 ,
            [8] AS NDO8 ,
            [9] AS NDO9 
    FROM    cteU PIVOT ( MAX(X) FOR NDO IN ( [0], [1], [2], [3], [4], [5], [6],
                                             [7], [8], [9], [10], [11], [12], [13],
                                             [14], [15], [16], [17], [18], [19],
                                             [20], [21], [22], [23], [24], [25],
                                             [26], [27], [28], [29], [30], [31],
                                             [32], [33], [34], [35], [36], [37],
                                             [38], [39], [40], [41], [42], [43],
                                             [44], [45], [46], [47], [48], [49],
                                             [50], [51], [52], [53], [54], [55],
                                             [56], [57], [58], [59], [60], [61],
                                             [62], [63], [64], [65], [66], [67],
                                             [68], [69], [70], [71], [72], [73],
                                             [74], [75], [76], [77], [78], [79],
                                             [80], [81], [82], [83], [84], [85],
                                             [86], [87], [88], [89], [90], [91],
                                             [92], [93], [94], [95], [96], [97],
                                             [98], [99], [100] ) ) AS P
    
    			 )
    
     SELECT R.LegOrigin ,
    		R.LegDestination ,
    		R.NextFlightNumber,
    		R.DepartureDate ,
            R.Class ,
    	NDO0 = IIF(R.NDO0  IS NULL ,( SELECT TOP 1 T.NDO0 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO0),
            NDO1 = IIF(R.NDO1  IS NULL ,( SELECT TOP 1 T.NDO1 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO1),
            NDO2 = IIF(R.NDO2  IS NULL ,( SELECT TOP 1 T.NDO2 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO2),
            NDO3 = IIF(R.NDO3  IS NULL ,( SELECT TOP 1 T.NDO3 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO3),
            NDO4 = IIF(R.NDO4  IS NULL ,( SELECT TOP 1 T.NDO4 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO4),
            NDO5 = IIF(R.NDO5  IS NULL ,( SELECT TOP 1 T.NDO5 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO5),
            NDO6 = IIF(R.NDO6  IS NULL ,( SELECT TOP 1 T.NDO6 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO6),
            NDO7 = IIF(R.NDO7  IS NULL ,( SELECT TOP 1 T.NDO7 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO7),
    	NDO8 = IIF(R.NDO8  IS NULL ,( SELECT TOP 1 T.NDO8 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO8),
    	NDO9 = IIF(R.NDO9  IS NULL ,( SELECT TOP 1 T.NDO9 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO9)
    		 FROM DadosAgregados R
    
    fiz a query  de

    NDO0 a
    NDO9  apenas para demostrar, mas no seu caso cabe replicar para os demais,

    Wesley Neves

    • Marcado como Resposta Thaís Sakamoto quarta-feira, 21 de junho de 2017 11:38
    quarta-feira, 21 de junho de 2017 11:34
  • Olha Thais , com esse resultado acredito que conseguiremos chegar no objetivo 

    veja  que o resultado obtido pela query :

    WITH    cteVoo
              AS ( SELECT   LEFT(Segment, 3) AS LegOrigin ,
                            RIGHT(Segment, 3) AS LegDestination ,
    						FlightNumber, 
                            DepartureDate ,
                            DataVersao ,
                            NDO = DATEDIFF(DAY, DataVersao, DepartureDate) ,
                            [Y] ,
                            [A] ,
                            [B] ,
                            [C] ,
                            [D] ,
                            [E] ,
                            [F] ,
                            [G] ,
                            [H] ,
                            [I] ,
                            [J] ,
                            [K] ,
                            [L] ,
                            [M] ,
                            [N] ,
                            [O] ,
                            [P] ,
                            [Q] ,
                            [R] ,
                            [S] ,
                            [T] ,
                            [U] ,
                            [V] ,
                            [UU] ,
                            Seq = ROW_NUMBER() OVER ( PARTITION BY DepartureDate ORDER BY DataVersao DESC )
                   FROM     [DailyBookings34].[DMBookings].[AvailableUnitsHistory]
                            WITH ( NOLOCK )
                   WHERE    DepartureDate BETWEEN '20161101' AND '20161130'
                            AND DATEDIFF(DAY, DataVersao, DepartureDate) BETWEEN 0
                                                                  AND
                                                                  100
                 ),
            cteU
              AS (
                   SELECT   LegOrigin ,
    			[NextFlightNumber] = DENSE_RANK() OVER ( PARTITION  BY LegOrigin ORDER BY FlightNumber ),
                            LegDestination ,
                            DepartureDate ,
                            NDO ,
                            Class ,
                            X
                   FROM     cteVoo UNPIVOT ( X FOR Class IN ( [Y], [A], [B], [C],
                                                              [D], [E], [F], [G],
                                                              [H], [I], [J], [K],
                                                              [L], [M], [N], [O],
                                                              [P], [Q], [R], [S],
                                                              [T], [U], [V], [UU] ) ) AS U
                 ),
    			 DadosAgregados AS (	 
    	  SELECT
    	    LegOrigin ,
    		LegDestination ,
    		NextFlightNumber,
    		DepartureDate ,
            Class ,
            [0] AS NDO0 ,
            [1] AS NDO1 ,
            [2] AS NDO2 ,
            [3] AS NDO3 ,
            [4] AS NDO4 ,
            [5] AS NDO5 ,
            [6] AS NDO6 ,
            [7] AS NDO7 ,
            [8] AS NDO8 ,
            [9] AS NDO9 
    FROM    cteU PIVOT ( MAX(X) FOR NDO IN ( [0], [1], [2], [3], [4], [5], [6],
                                             [7], [8], [9], [10], [11], [12], [13],
                                             [14], [15], [16], [17], [18], [19],
                                             [20], [21], [22], [23], [24], [25],
                                             [26], [27], [28], [29], [30], [31],
                                             [32], [33], [34], [35], [36], [37],
                                             [38], [39], [40], [41], [42], [43],
                                             [44], [45], [46], [47], [48], [49],
                                             [50], [51], [52], [53], [54], [55],
                                             [56], [57], [58], [59], [60], [61],
                                             [62], [63], [64], [65], [66], [67],
                                             [68], [69], [70], [71], [72], [73],
                                             [74], [75], [76], [77], [78], [79],
                                             [80], [81], [82], [83], [84], [85],
                                             [86], [87], [88], [89], [90], [91],
                                             [92], [93], [94], [95], [96], [97],
                                             [98], [99], [100] ) ) AS P
    
    			 )
    
    SELECT * FROM DadosAgregados R

    tem essa estrutura:

    

    E o resultado obtido pela query de exemplo no post anterior

    DECLARE @Tabela TABLE
        (
          LegLogin VARCHAR(3) DEFAULT ( 'VCP' ) ,
          LegDestino VARCHAR(3) DEFAULT ( 'CNF' ) ,
          flightnumber INT DEFAULT ( 2420 ) ,
          DepartamentoData DATETIME DEFAULT ( DATEFROMPARTS(2017, 1, 13) ) ,
          Class CHAR(1) ,
          NDO0 INT NULL ,
          NDO1 INT NULL ,
          NDO2 INT NULL ,
          NDO3 INT NULL ,
          NDO4 INT NULL ,
          NDO5 INT NULL ,
          NDO6 INT NULL ,
          NDO7 INT NULL
        );
    
    INSERT  INTO @Tabela
    VALUES 
     ( DEFAULT, DEFAULT,DEFAULT, DEFAULT, 'A', 125, 125,   127,  128,  129,  130,  131,  1 ),
     ( DEFAULT, DEFAULT,DEFAULT, DEFAULT, 'B', 125, 125,   127,  128,  129,  130,  131,  1 ),
     ( DEFAULT, DEFAULT,DEFAULT, DEFAULT, 'C', 118, 118,   118,  118,  118,  118,  118,  1 ),
     ( DEFAULT, DEFAULT,DEFAULT, DEFAULT, 'D', 118, 118,   118,  118,  118,  118,  118,  1 ),
     ( DEFAULT, DEFAULT,DEFAULT, DEFAULT, 'E', 125, 125,   127,  128,  129,  130,  130,  1 ),
     ( DEFAULT, DEFAULT,DEFAULT, DEFAULT, 'F', 125, 125,   127,  128,  129,  130,  130,  1 ),
     ( DEFAULT, DEFAULT,DEFAULT, DEFAULT, 'G', 125, 125,   127,  128,  129,  130,  130,  1 ),
     ( DEFAULT, DEFAULT,2463,	 DEFAULT, 'A', NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL ),
     ( DEFAULT, DEFAULT,2463,    DEFAULT, 'B', NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL ),
     ( DEFAULT, DEFAULT,2463,    DEFAULT, 'C', NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL ),
     ( DEFAULT, DEFAULT,2463,    DEFAULT, 'D', NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL ),
     ( DEFAULT, DEFAULT,2463,    DEFAULT, 'E', NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL ),
     ( DEFAULT, DEFAULT,2463,    DEFAULT, 'F', NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL ),
     ( DEFAULT, DEFAULT,2463,    DEFAULT, 'G', NULL,NULL, NULL, NULL, NULL, NULL, NULL, NULL ),
     ( DEFAULT, DEFAULT,2487,    DEFAULT, 'A', 125, 125,   127, 128,  129, 130,   130,130 ), 
     ( DEFAULT, DEFAULT,2487,    DEFAULT, 'B', 125, 125,   127, 128,  129, 130,   130,130 ), 
     ( DEFAULT, DEFAULT,2487,    DEFAULT, 'C', 118, 118,   118, 118,  118, 118,   118,118 ), 
     ( DEFAULT, DEFAULT,2487,    DEFAULT, 'D', 118, 118,   118, 118,  118, 118,   118,118 ), 
     ( DEFAULT, DEFAULT,2487,    DEFAULT, 'E', 125, 125,   127, 128,  129, 130,   130,130 ), 
     ( DEFAULT, DEFAULT,2487,    DEFAULT, 'F', 125, 125,   127, 122,  129, 130,   130,130 ), 
     ( DEFAULT, DEFAULT,2487,    DEFAULT, 'G', 125, 125,   114, 109,  116, 130,   130,130 );
    
    
    
     WITH Dados AS (
     SELECT T.LegLogin ,
            T.LegDestino ,
            T.flightnumber ,
    		Proximoflightnumber = DENSE_RANK() OVER(PARTITION BY T.LegLogin ORDER BY T.flightnumber),
            T.DepartamentoData ,
            T.Class ,
            T.NDO0 ,
            T.NDO1 ,
            T.NDO2 ,
            T.NDO3 ,
            T.NDO4 ,
            T.NDO5 ,
            T.NDO6 ,
            T.NDO7 FROM @Tabela AS T
     )
     
     SELECT R.LegLogin ,
            R.LegDestino ,
            R.flightnumber ,
            R.Proximoflightnumber ,
            R.DepartamentoData ,
            R.Class ,
            NDO0 = IIF(R.NDO0  IS NULL ,( SELECT TOP 1 T.NDO0 FROM  Dados AS T WHERE R.Class = T.Class AND R.Proximoflightnumber + 1 =  T.Proximoflightnumber ),R.NDO0),
            NDO1 = IIF(R.NDO1  IS NULL ,( SELECT TOP 1 T.NDO1 FROM  Dados AS T WHERE R.Class = T.Class AND R.Proximoflightnumber + 1 =  T.Proximoflightnumber ),R.NDO1),
            NDO2 = IIF(R.NDO2  IS NULL ,( SELECT TOP 1 T.NDO2 FROM  Dados AS T WHERE R.Class = T.Class AND R.Proximoflightnumber + 1 =  T.Proximoflightnumber ),R.NDO2),
            NDO3 = IIF(R.NDO3  IS NULL ,( SELECT TOP 1 T.NDO3 FROM  Dados AS T WHERE R.Class = T.Class AND R.Proximoflightnumber + 1 =  T.Proximoflightnumber ),R.NDO3),
            NDO4 = IIF(R.NDO4  IS NULL ,( SELECT TOP 1 T.NDO4 FROM  Dados AS T WHERE R.Class = T.Class AND R.Proximoflightnumber + 1 =  T.Proximoflightnumber ),R.NDO4),
            NDO5 = IIF(R.NDO5  IS NULL ,( SELECT TOP 1 T.NDO5 FROM  Dados AS T WHERE R.Class = T.Class AND R.Proximoflightnumber + 1 =  T.Proximoflightnumber ),R.NDO5),
            NDO6 = IIF(R.NDO6  IS NULL ,( SELECT TOP 1 T.NDO6 FROM  Dados AS T WHERE R.Class = T.Class AND R.Proximoflightnumber + 1 =  T.Proximoflightnumber ),R.NDO6),
            NDO7 = IIF(R.NDO7  IS NULL ,( SELECT TOP 1 T.NDO7 FROM  Dados AS T WHERE R.Class = T.Class AND R.Proximoflightnumber + 1 =  T.Proximoflightnumber ),R.NDO7)
    		 FROM Dados R

    foi esse :

    logo acredito que é so inserirmos a logica para obter o próximo valor 

    WITH    cteVoo
              AS ( SELECT   LEFT(Segment, 3) AS LegOrigin ,
                            RIGHT(Segment, 3) AS LegDestination ,
    						FlightNumber, 
                            DepartureDate ,
                            DataVersao ,
                            NDO = DATEDIFF(DAY, DataVersao, DepartureDate) ,
                            [Y] ,
                            [A] ,
                            [B] ,
                            [C] ,
                            [D] ,
                            [E] ,
                            [F] ,
                            [G] ,
                            [H] ,
                            [I] ,
                            [J] ,
                            [K] ,
                            [L] ,
                            [M] ,
                            [N] ,
                            [O] ,
                            [P] ,
                            [Q] ,
                            [R] ,
                            [S] ,
                            [T] ,
                            [U] ,
                            [V] ,
                            [UU] ,
                            Seq = ROW_NUMBER() OVER ( PARTITION BY DepartureDate ORDER BY DataVersao DESC )
                   FROM     [DailyBookings34].[DMBookings].[AvailableUnitsHistory]
                            WITH ( NOLOCK )
                   WHERE    DepartureDate BETWEEN '20161101' AND '20161130'
                            AND DATEDIFF(DAY, DataVersao, DepartureDate) BETWEEN 0
                                                                  AND
                                                                  100
                 ),
            cteU
              AS (
                   SELECT   LegOrigin ,
    			[NextFlightNumber] = DENSE_RANK() OVER ( PARTITION  BY LegOrigin ORDER BY FlightNumber ),
                            LegDestination ,
                            DepartureDate ,
                            NDO ,
                            Class ,
                            X
                   FROM     cteVoo UNPIVOT ( X FOR Class IN ( [Y], [A], [B], [C],
                                                              [D], [E], [F], [G],
                                                              [H], [I], [J], [K],
                                                              [L], [M], [N], [O],
                                                              [P], [Q], [R], [S],
                                                              [T], [U], [V], [UU] ) ) AS U
                 ),
    	 DadosAgregados AS (	 
    	  SELECT
    	    LegOrigin ,
    		LegDestination ,
    		NextFlightNumber,
    		DepartureDate ,
            Class ,
            [0] AS NDO0 ,
            [1] AS NDO1 ,
            [2] AS NDO2 ,
            [3] AS NDO3 ,
            [4] AS NDO4 ,
            [5] AS NDO5 ,
            [6] AS NDO6 ,
            [7] AS NDO7 ,
            [8] AS NDO8 ,
            [9] AS NDO9 
    FROM    cteU PIVOT ( MAX(X) FOR NDO IN ( [0], [1], [2], [3], [4], [5], [6],
                                             [7], [8], [9], [10], [11], [12], [13],
                                             [14], [15], [16], [17], [18], [19],
                                             [20], [21], [22], [23], [24], [25],
                                             [26], [27], [28], [29], [30], [31],
                                             [32], [33], [34], [35], [36], [37],
                                             [38], [39], [40], [41], [42], [43],
                                             [44], [45], [46], [47], [48], [49],
                                             [50], [51], [52], [53], [54], [55],
                                             [56], [57], [58], [59], [60], [61],
                                             [62], [63], [64], [65], [66], [67],
                                             [68], [69], [70], [71], [72], [73],
                                             [74], [75], [76], [77], [78], [79],
                                             [80], [81], [82], [83], [84], [85],
                                             [86], [87], [88], [89], [90], [91],
                                             [92], [93], [94], [95], [96], [97],
                                             [98], [99], [100] ) ) AS P
    
    			 )
    
     SELECT R.LegOrigin ,
    		R.LegDestination ,
    		R.NextFlightNumber,
    		R.DepartureDate ,
            R.Class ,
    	NDO0 = IIF(R.NDO0  IS NULL ,( SELECT TOP 1 T.NDO0 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO0),
            NDO1 = IIF(R.NDO1  IS NULL ,( SELECT TOP 1 T.NDO1 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO1),
            NDO2 = IIF(R.NDO2  IS NULL ,( SELECT TOP 1 T.NDO2 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO2),
            NDO3 = IIF(R.NDO3  IS NULL ,( SELECT TOP 1 T.NDO3 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO3),
            NDO4 = IIF(R.NDO4  IS NULL ,( SELECT TOP 1 T.NDO4 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO4),
            NDO5 = IIF(R.NDO5  IS NULL ,( SELECT TOP 1 T.NDO5 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO5),
            NDO6 = IIF(R.NDO6  IS NULL ,( SELECT TOP 1 T.NDO6 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO6),
            NDO7 = IIF(R.NDO7  IS NULL ,( SELECT TOP 1 T.NDO7 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO7),
    	NDO8 = IIF(R.NDO8  IS NULL ,( SELECT TOP 1 T.NDO8 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO8),
    	NDO9 = IIF(R.NDO9  IS NULL ,( SELECT TOP 1 T.NDO9 FROM  Dados AS T WHERE R.Class = T.Class AND R.NextFlightNumber + 1 =  T.NextFlightNumber ),R.NDO9)
    		 FROM DadosAgregados R
    fiz a query  de

    NDO0 a
    NDO9  apenas para demostrar, mas no seu caso cabe replicar para os demais,

    Wesley Neves

    Obrigada :)
    quarta-feira, 21 de junho de 2017 11:38
  • Deu certo no que vc precisava ??

    obrigado pela paciência de esperar , ontem não pude responder

    Wesley Neves
    wesley.si.neves@gmail.com
    Brasilia-DF
    MTA-SQL Server


    Wesley Neves

    quarta-feira, 21 de junho de 2017 11:52
  • Deu certo no que vc precisava ??

    obrigado pela paciência de esperar , ontem não pude responder

    Wesley Neves
    wesley.si.neves@gmail.com
    Brasilia-DF
    MTA-SQL Server


    Wesley Neves

    Obrigada você. Deu certo sim, consegui tirar todos meus resultados nulos e sobrepor com o resultado do proximo sim
    quarta-feira, 21 de junho de 2017 11:57