Исправление объединений, приводящих к NULL для запроса с использованием трех разных операций ROW_NUMBER() OVER(PARTITION BY ORDER BY

Я надеюсь, что этот вопрос содержит достаточно деталей и контекста, но дайте мне знать, если какая-либо дополнительная информация облегчит понимание, и я постараюсь предоставить. Я использую MSSMS 2016. В частности, Microsoft SQL Server Management Studio 13.0.16000.28.

Я пытаюсь присоединиться к таблице рейсов ДВА раза (два набора столбцов из одной таблицы; один набор для прибытия и один набор для отправления) к таблице ходов. Для этого я выполняю ROW_NUMBER() OVER(PARTITION BY... ORDER BY в трех разных запросах (также используя результаты одного из этих ROW_NUMBERS в 2 операторах WHERE), а затем объединяю эти 3 запроса вместе в уникальный идентификатор. Я пытался объединить отдельные представления, подзапросы и CTE. Все попытки приводят к полям NULL для 1-го соединения, написанного в коде. Если я переверну их, это все равно 1-е соединение дает NULL, а второе работает.

Когда я запускаю код в определениях CTE самостоятельно, они оба работают отлично. Проблема возникает, когда я пытаюсь объединить результаты вместе.

Ниже я добавлю примерную таблицу рейсов. В примере показано, как один Registration_ID перемещается от станции к станции, а также время отправления и прибытия. Они НЕ обязательно отсортированы по порядку; первый столбец слева — тот, который я добавил в свой CTE, используя:

ROW_NUMBER() OVER(PARTITION BY ss.REGISTRATION_ID ORDER BY ss.ACT_DPRT_DTMZ) AS RN_By_Reg_Order_DPRTDTMZ

Далее я добавлю образец таблицы ходов ниже. Идентификаторы Registration_ID, показанные в первой таблице перелетов, могут появляться в приведенной ниже таблице перемещений один или несколько раз. Для каждой строки в таблице ходов здесь нам нужно дважды присоединиться к строке в приведенной выше таблице. Один ряд на заезд на буксировочную станцию ​​и один ряд на выезд с буксирной станции.

Логика для определения того, какую строку из таблицы рейсов вытащить в качестве прибытия, а какую строку из таблицы рейсов вытащить в качестве отправления, такова:

The Arrival Information Join
flights.ARRV_STA_CD should match moves.TOW_STN
flights.REGISTRATION_ID should match moves.TAIL_NBR
flights.ACT_ARRV_DTMZ should be < moves.GMT_ACT_TOW_START_DTM
flights.ACT_ARRV_DTMZ should be THE HIGHEST/LATEST/GREATEST ONE AVAILABLE, that meets the preceding conditions (multiple flights could meet those 3 conditions, so this part guarantees the "true" arrival)

The Departure Information Join
flights.DPRT_STA_CD should match moves.TOW_STN
flights.REGISTRATION_ID should match moves.TAIL_NBR
flights.ACT_DPRT_DTMZ should be > moves.GMT_ACT_TOW_START_DTM
flights.ACT_DPRT_DTMZ should be THE LOWEST/EARLIEST/LEAST ONE AVAILABLE, that meets the preceding conditions (multiple flights could meet those 3 conditions, so this part guarantees the "true" departure)

Наконец, ниже я покажу, каков мой ожидаемый результат по сравнению с результатом, который я фактически получаю.

Ожидал:

Собственно получение:

with 
    cteSSPF as
        (select 
        ROW_NUMBER() OVER(PARTITION BY ss.REGISTRATION_ID ORDER BY ss.ACT_DPRT_DTMZ) AS RN_By_Reg_Order_DPRTDTMZ
        ,ss.DPRT_STA_CD
        ,ss.ACT_DPRT_DTMZ
        ,ss.ARRV_STA_CD
        ,ss.ACT_ARRV_DTMZ
        ,ss.EQUIP_ID
        ,ss.REGISTRATION_ID
        
        From dbo.vw_supersnapshot_post2019 ss
        
        WHERE ss.CNXL_CD<>'C'
    ),
    --End of SSPF


    cteArrv as 
        (
        SELECT a.* From
            (SELECT 

            ROW_NUMBER() OVER(PARTITION BY moves.TOW_ID ORDER BY cteSSPF.RN_By_Reg_Order_DPRTDTMZ desc) AS [Arrival_RN_By_TowID_Order_DPRTDTMZ]
            
            ,moves.[TOW_ID]
            ,moves.[TOW_STATE]
            ,moves.[TOW_TYPE]
            ,moves.[TOW_STN]
            ,moves.[TAIL_NBR]
            ,moves.[GMT_ACT_TOW_START_DTM]
            ,moves.[GMT_ACT_TOW_END_DTM]
            ,moves.[TOW_START_POSN]
            ,moves.[TOW_END_POSN]

            
            ,cteSSPF.DPRT_STA_CD as [Arrival_DPRT_STA_CD]
            ,cteSSPF.ACT_DPRT_DTMZ as [Arrival_ACT_DPRT_DTMZ]
            ,cteSSPF.ARRV_STA_CD as [Arrival_ARRV_STA_CD]
            ,cteSSPF.ACT_ARRV_DTMZ as [Arrival_ACT_ARRV_DTMZ]
            ,cteSSPF.EQUIP_ID as [Arrival_EQUIP_ID]
            ,cteSSPF.REGISTRATION_ID as [Arrival_REGISTRATION_ID]
            
            FROM [NOC_AO].[dbo].[vw_RTS_ACMoves_post2019] moves
            
            Left Join cteSSPF ON 
                (moves.TAIL_NBR = cteSSPF.REGISTRATION_ID) 
                AND (moves.TOW_STN = cteSSPF.ARRV_STA_CD) 
                AND (moves.GMT_ACT_TOW_START_DTM > cteSSPF.ACT_ARRV_DTMZ)

            ) a
        
        WHERE Arrival_RN_By_TowID_Order_DPRTDTMZ = 1 
    ),
    --End of cteArrv


    cteDept as 
        (
        SELECT a.* From
            (SELECT 
            
            ROW_NUMBER() OVER(PARTITION BY moves.TOW_ID ORDER BY cteSSPF.RN_By_Reg_Order_DPRTDTMZ asc) AS [Departure_RN_By_TowID_Order_DPRTDTMZ]
            
            ,moves.[TOW_ID]
            ,moves.[TOW_STATE]
            ,moves.[TOW_TYPE]
            ,moves.[TOW_STN]
            ,moves.[TAIL_NBR]
            ,moves.[GMT_ACT_TOW_START_DTM]
            ,moves.[GMT_ACT_TOW_END_DTM]
            ,moves.[TOW_START_POSN]
            ,moves.[TOW_END_POSN]
            

            ,cteSSPF.DPRT_STA_CD as [Departure_DPRT_STA_CD]
            ,cteSSPF.ACT_DPRT_DTMZ as [Departure_ACT_DPRT_DTMZ]
            ,cteSSPF.ARRV_STA_CD as [Departure_ARRV_STA_CD]
            ,cteSSPF.ACT_ARRV_DTMZ as [Departure_ACT_ARRV_DTMZ]
            ,cteSSPF.EQUIP_ID as [Departure_EQUIP_ID]
            ,cteSSPF.REGISTRATION_ID as [Departure_REGISTRATION_ID]
        
          FROM [NOC_AO].[dbo].[vw_RTS_ACMoves_post2019] moves
        
        Left Join cteSSPF ON 
            (moves.TAIL_NBR = cteSSPF.REGISTRATION_ID) 
            AND (moves.TOW_STN = cteSSPF.DPRT_STA_CD) 
            AND (moves.GMT_ACT_TOW_START_DTM < cteSSPF.ACT_DPRT_DTMZ)

        ) a
        
        --temp comment out to see what the numbers all look like merged?
        WHERE Departure_RN_By_TowID_Order_DPRTDTMZ = 1

    )
    --End of cteDept

SELECT
moves.[TOW_ID]
,moves.[TOW_STATE]
,moves.[TOW_TYPE]
,moves.[TOW_STN]
,moves.[TAIL_NBR]
,moves.[GMT_ACT_TOW_START_DTM]
,moves.[GMT_ACT_TOW_END_DTM]
,moves.[TOW_START_POSN]
,moves.[TOW_END_POSN]

,cteArrv.Arrival_RN_By_TowID_Order_DPRTDTMZ
,cteArrv.Arrival_DPRT_STA_CD
,cteArrv.Arrival_ACT_DPRT_DTMZ
,cteArrv.Arrival_ARRV_STA_CD
,cteArrv.Arrival_ACT_ARRV_DTMZ
,cteArrv.Arrival_EQUIP_ID
,cteArrv.Arrival_REGISTRATION_ID

,cteDept.Departure_RN_By_TowID_Order_DPRTDTMZ
,cteDept.Departure_DPRT_STA_CD
,cteDept.Departure_ACT_DPRT_DTMZ
,cteDept.Departure_ARRV_STA_CD
,cteDept.Departure_ACT_ARRV_DTMZ
,cteDept.Departure_EQUIP_ID
,cteDept.Departure_REGISTRATION_ID

From [NOC_AO].[dbo].[vw_RTS_ACMoves_post2019] moves

--Somehow these joins still aren't working even with this CTE version
--The first join for cteArrv, all the fields from it show up NULL, the second join for cteDept, all those fields appear to work
--Even stranger, if I change the order of the join statements, then now Arrv works but Dept does not (i.e. the last join is the only one that works) 
Left Join cteArrv ON
    moves.TOW_ID = cteArrv.TOW_ID

Left Join cteDept ON
    moves.TOW_ID = cteDept.TOW_ID
    
Order by moves.TAIL_NBR,moves.TOW_STN,moves.TOW_ID,moves.GMT_ACT_TOW_START_DTM OFFSET 0 ROWS

Вот версия с temptables, которые предварительно заполняют данные в образце для вас. Но теперь с этой версией запрос РАБОТАЕТ! Я думаю, причина в том, что я пытаюсь выполнить это, используя представления. Как только данные находятся в таблице, они работают нормально, но мне нужно запускать их вне представлений, потому что данные постоянно обновляются новыми строками. Любая идея, в чем разница в том, что эта версия работает, а другая - нет?

--Trying to create the temp tables first in the SQL code, and THEN use thsoe temp tables in the CTEs, and THEN use those CTEs for the "real" select and joins
--Genericizing version for troubleshooting
--Now this version works but it is using static tables, I need to use the views like from the above code because then they will update as the underlying real data table updates but the version reading from the real views is still giving false NULL data for the 1st join....

CREATE TABLE tempflights2
(
DPRT_STA_CD VARCHAR(3) NOT NULL,
ACT_DPRT_DTMZ DATETIME NOT NULL,
ARRV_STA_CD VARCHAR(3) NOT NULL,
ACT_ARRV_DTMZ DATETIME NOT NULL,
REGISTRATION_ID VARCHAR(30) NOT NULL
)
    
INSERT INTO tempflights2
VALUES 
('DTW','12/01/2020 13:52','DEN','12/01/2020 17:33','N12345'),
('DEN','12/01/2020 18:01','MSN','12/01/2020 19:59','N12345'),
('MSN','12/01/2020 20:50','DEN','12/01/2020 23:21','N12345'),
('DEN','12/02/2020 02:48','CMH','12/02/2020 05:55','N12345'),
('CMH','12/02/2020 13:58','DEN','12/02/2020 17:29','N12345'),
('DEN','12/02/2020 18:32','EUG','12/02/2020 21:08','N12345'),
('EUG','12/02/2020 23:20','SFO','12/03/2020 00:42','N12345'),
('SFO','12/03/2020 02:32','SBA','12/03/2020 03:36','N12345'),
('SBA','12/03/2020 14:21','SFO','12/03/2020 15:19','N12345'),
('SFO','12/03/2020 19:12','TUS','12/03/2020 21:18','N12345')
;





CREATE TABLE tempmoves2
(
TOW_ID VARCHAR(30) NOT NULL,
TOW_STN VARCHAR(3) NOT NULL,
TAIL_NBR VARCHAR(30) NOT NULL,
GMT_ACT_TOW_START_DTM DATETIME NOT NULL,
)

INSERT INTO tempmoves2
VALUES 
('unique tow_ID1','DEN','N12345','12/02/2020 01:00'),
('unique tow_ID2','GUM','N45678','12/23/2020 00:15'),
('unique tow_ID3','SFO','N12345','12/03/2020 15:30')
;











--Generic Version masking company specific items and genericizing
--Trying to mash together all the steps into one query using 2 CTEs (1 for arrival and 1 for departure)
--Doing this due to repeatedly running into the issue of joins not working between the two views using the row number partitian by order by
with 
    cteSSPF as
        (select 
        ROW_NUMBER() OVER(PARTITION BY ss.REGISTRATION_ID ORDER BY ss.ACT_DPRT_DTMZ) AS RN_By_Reg_Order_DPRTDTMZ
        ,ss.DPRT_STA_CD
        ,ss.ACT_DPRT_DTMZ
        ,ss.ARRV_STA_CD
        ,ss.ACT_ARRV_DTMZ
        ,ss.REGISTRATION_ID
        
        From tempflights2 ss
        
        --WHERE ss.CNXL_CD<>'C'
    ),
    --End of SSPF


    cteArrv as 
        (
        SELECT a.* From
            (SELECT 

            ROW_NUMBER() OVER(PARTITION BY moves.TOW_ID ORDER BY cteSSPF.RN_By_Reg_Order_DPRTDTMZ desc) AS [Arrival_RN_By_TowID_Order_DPRTDTMZ]
            
            ,moves.[TOW_ID]
            ,moves.[TOW_STN]
            ,moves.[TAIL_NBR]
            ,moves.[GMT_ACT_TOW_START_DTM]

            ,cteSSPF.DPRT_STA_CD as [Arrival_DPRT_STA_CD]
            ,cteSSPF.ACT_DPRT_DTMZ as [Arrival_ACT_DPRT_DTMZ]
            ,cteSSPF.ARRV_STA_CD as [Arrival_ARRV_STA_CD]
            ,cteSSPF.ACT_ARRV_DTMZ as [Arrival_ACT_ARRV_DTMZ]
            ,cteSSPF.REGISTRATION_ID as [Arrival_REGISTRATION_ID]
            
            FROM tempmoves2 moves
            
            Left Join cteSSPF ON 
                (moves.TAIL_NBR = cteSSPF.REGISTRATION_ID) 
                AND (moves.TOW_STN = cteSSPF.ARRV_STA_CD) 
                AND (moves.GMT_ACT_TOW_START_DTM > cteSSPF.ACT_ARRV_DTMZ)

            ) a
        
        WHERE Arrival_RN_By_TowID_Order_DPRTDTMZ = 1 
    ),
    --End of cteArrv


    cteDept as 
        (
        SELECT a.* From
            (SELECT 
            
            ROW_NUMBER() OVER(PARTITION BY moves.TOW_ID ORDER BY cteSSPF.RN_By_Reg_Order_DPRTDTMZ asc) AS [Departure_RN_By_TowID_Order_DPRTDTMZ]
            
            ,moves.[TOW_ID]
            ,moves.[TOW_STN]
            ,moves.[TAIL_NBR]
            ,moves.[GMT_ACT_TOW_START_DTM]

            ,cteSSPF.DPRT_STA_CD as [Departure_DPRT_STA_CD]
            ,cteSSPF.ACT_DPRT_DTMZ as [Departure_ACT_DPRT_DTMZ]
            ,cteSSPF.ARRV_STA_CD as [Departure_ARRV_STA_CD]
            ,cteSSPF.ACT_ARRV_DTMZ as [Departure_ACT_ARRV_DTMZ]
            ,cteSSPF.REGISTRATION_ID as [Departure_REGISTRATION_ID]
        
          FROM tempmoves2 moves
        
        Left Join cteSSPF ON 
            (moves.TAIL_NBR = cteSSPF.REGISTRATION_ID) 
            AND (moves.TOW_STN = cteSSPF.DPRT_STA_CD) 
            AND (moves.GMT_ACT_TOW_START_DTM < cteSSPF.ACT_DPRT_DTMZ)

        ) a
        
        --temp comment out to see what the numbers all look like merged?
        WHERE Departure_RN_By_TowID_Order_DPRTDTMZ = 1

    )
    --End of cteDept

SELECT
moves.[TOW_ID]
,moves.[TOW_STN]
,moves.[TAIL_NBR]
,moves.[GMT_ACT_TOW_START_DTM]

,cteArrv.Arrival_RN_By_TowID_Order_DPRTDTMZ
,cteArrv.Arrival_DPRT_STA_CD
,cteArrv.Arrival_ACT_DPRT_DTMZ
,cteArrv.Arrival_ARRV_STA_CD
,cteArrv.Arrival_ACT_ARRV_DTMZ
,cteArrv.Arrival_REGISTRATION_ID

,cteDept.Departure_RN_By_TowID_Order_DPRTDTMZ
,cteDept.Departure_DPRT_STA_CD
,cteDept.Departure_ACT_DPRT_DTMZ
,cteDept.Departure_ARRV_STA_CD
,cteDept.Departure_ACT_ARRV_DTMZ
,cteDept.Departure_REGISTRATION_ID

From tempmoves2 moves
Left Join cteArrv ON
    moves.TOW_ID = cteArrv.TOW_ID

Left Join cteDept ON
    moves.TOW_ID = cteDept.TOW_ID
    
Order by moves.TAIL_NBR,moves.TOW_STN,moves.TOW_ID,moves.GMT_ACT_TOW_START_DTM OFFSET 0 ROWS