1

I hope this question provides enough detail and context, but let me know if any further information would make it easier to understand and I will try to provide. I am using MSSMS 2016. Specifically- Microsoft SQL Server Management Studio 13.0.16000.28

I am attempting to join a table of flights TWO times (two sets of columns from the same table; one set for arrival and one set for departure), to a table of moves. To do so I am performing a ROW_NUMBER() OVER(PARTITION BY... ORDER BY in three different queries (also using the results of one of those ROW_NUMBERS in 2 of the WHERE statements), and then join those 3 queries together on a unique ID. I tried joining together individual views, subqueries, and CTEs. All attempts result in NULL fields for the 1st join written in the code. If I flip them around, it is still the 1st join that gives NULLs and the 2nd one that works.

When I run the code within the CTE definitions on their own they both appear to work perfectly. The issue occurs when I try to join the results together.

I will add a sample table of the flights below. The sample shows one registration_ID traveling from station to station along with departure and arrival times. They are NOT necessarily sorted in order; the first column on the left is one that I have added in my CTE, using:

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

RN_By_Reg_Order_DPRTDTMZ DPRT_STA_CD ACT_DPRT_DTMZ ARRV_STA_CD ACT_ARRV_DTMZ REGISTRATION_ID
1 DTW 12/01/2020 13:52 DEN 12/01/2020 17:33 N12345
2 DEN 12/01/2020 18:01 MSN 12/01/2020 19:59 N12345
3 MSN 12/01/2020 20:50 DEN 12/01/2020 23:21 N12345
4 DEN 12/02/2020 02:48 CMH 12/02/2020 05:55 N12345
5 CMH 12/02/2020 13:58 DEN 12/02/2020 17:29 N12345
6 DEN 12/02/2020 18:32 EUG 12/02/2020 21:08 N12345
7 EUG 12/02/2020 23:20 SFO 12/03/2020 00:42 N12345
8 SFO 12/03/2020 02:32 SBA 12/03/2020 03:36 N12345
9 SBA 12/03/2020 14:21 SFO 12/03/2020 15:19 N12345
10 SFO 12/03/2020 19:12 TUS 12/03/2020 21:18 N12345

Next I will add a sample table of the moves below. The Registration_IDs shown in the first table of flights, can appear in this below "moves" table one or more times. For each row in the moves table here we need to join to a row in the above table twice. One row for the arrival into the tow station, and one row for departure out of the tow station.

TOW_ID TOW_STN TAIL_NBR GMT_ACT_TOW_START_DTM
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

The logic to determine which row from the flights table to pull as the "Arrival", and which row from the flights table to pull as the "Departure" is as such:

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)

Finally, I will show below what my expected output is, versus the output I am actually getting.

Expected:

TOW_ID TOW_STN TAIL_NBR GMT_ACT_TOW_START_DTM Arrival_RN_By_TowID_Order_DPRTDTMZ Arrival_DPRT_STA_CD Arrival_ACT_DPRT_DTMZ Arrival_ARRV_STA_CD Arrival_ACT_ARRV_DTMZ Arrival_REGISTRATION_ID Departure_RN_By_TowID_Order_DPRTDTMZ Departure_DPRT_STA_CD Departure_ACT_DPRT_DTMZ Departure_ARRV_STA_CD Departure_ACT_ARRV_DTMZ Departure_REGISTRATION_ID
unique tow_ID1 DEN N12345 12/02/2020 01:00 1 MSN 12/01/2020 20:50 DEN 12/01/2020 23:21 N12345 1 DEN 12/02/2020 02:48 CMH 12/02/2020 05:55 N12345
unique tow_ID2 GUM N45678 12/23/2020 00:15 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
unique tow_ID3 SFO N12345 12/03/2020 15:30 1 SBA 12/03/2020 14:21 SFO 12/03/2020 15:19 N12345 1 SFO 12/03/2020 19:12 TUS 12/03/2020 21:18 N12345

Actually Getting:

TOW_ID TOW_STN TAIL_NBR GMT_ACT_TOW_START_DTM Arrival_RN_By_TowID_Order_DPRTDTMZ Arrival_DPRT_STA_CD Arrival_ACT_DPRT_DTMZ Arrival_ARRV_STA_CD Arrival_ACT_ARRV_DTMZ Arrival_REGISTRATION_ID Departure_RN_By_TowID_Order_DPRTDTMZ Departure_DPRT_STA_CD Departure_ACT_DPRT_DTMZ Departure_ARRV_STA_CD Departure_ACT_ARRV_DTMZ Departure_REGISTRATION_ID
unique tow_ID1 DEN N12345 12/02/2020 01:00 1 NULL NULL NULL NULL NULL 1 DEN 12/02/2020 02:48 CMH 12/02/2020 05:55 N12345
unique tow_ID2 GUM N45678 12/23/2020 00:15 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL
unique tow_ID3 SFO N12345 12/03/2020 15:30 1 NULL NULL NULL NULL NULL 1 SFO 12/03/2020 19:12 TUS 12/03/2020 21:18 N12345 
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

Here is the version with the temptables that prepopulate the data in the sample for you. But now with this version, the query DOES WORK! I think the reason though is that I am trying to execute this using views. Once the data is in a table it works fine, but I need to run it off of views because the data updates with new rows constantly. Any idea why the difference that this version works but the other one reading off of views doesn't?

--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
Kc Ryan
  • 11
  • 3
  • You can improve this question by removing most of the text and adding sample data, expected results and actual results, as well as providing a [mre] i.e. DDL+DML which produces the issue :) good luck with anyone trying to wade though that and made sense of it. – Dale K Jan 20 '21 at 20:27
  • Thanks for the feedback Dale, I've trimmed down my explanatory text. I am looking through the SQL to try to trim it down but to be honest I don't see many opportunities to shorten this... since I'm using 3 CTEs I think the need of having the three CTEs code as well as the "real" SQL joining them together is making it go a bit long.... – Kc Ryan Jan 20 '21 at 20:43
  • Still... I bet you don't need most of those columns to reproduce the issue. Create some temp tables with the minimum required columns, populate them with a couple of rows of data, and post that along with your expected results. Its very hard for many of us to visualise your expected results from a paragraph of text. – Dale K Jan 20 '21 at 20:51
  • Thanks again @DaleK. I did also just trim out a few fields from each CTE and the main select statement that weren't part of the logic. – Kc Ryan Jan 20 '21 at 20:52
  • @DaleK Copy that on the expected results and temp tables working on that part now – Kc Ryan Jan 20 '21 at 20:52
  • @DaleK thanks again for all of the feedback earlier. Sorry it took so long to clean up the question but I think I've done a decent job of adding in the sample tables to clarify what I'm trying to do and showing expected versus actual etc... Let me know if you think anything else might help improve further? I hope this makes more sense now... It seems like this should be easy I'm so perplexed at how the last JOIN works fine, but the first one doesn't, and they are structured similarly. – Kc Ryan Jan 21 '21 at 02:04
  • If you were to add the sample data as DDL+DML (create temp table, insert into temp table - as I originally suggested :)) I would copy and paste it out and take a look. But I'm not typing it all back in :) – Dale K Jan 21 '21 at 02:06
  • Ah I'd actually googled DDL+DML lol. I found this https://stackoverflow.com/questions/2578194/what-are-ddl-and-dml but I didn't understand how to translate this answer into instructions for how to create a temp table, insert into a temp table, and somehow make said table appear here on StackOverflow inside of this question. So instead I clicked those little helpful dropdowns in the question editor and it explained how you're supposed to do tables; apologies I figured that must be what you meant. Might you happen to have a URL to instructions on how to do it the right way? – Kc Ryan Jan 21 '21 at 14:33
  • Ohhh actually, I think I might be understanding now. I think the idea is to actually create the table within the SQL code first as one step? And then use those tables in the CTEs and SELECT instead? – Kc Ryan Jan 21 '21 at 14:35
  • Okay so I added the temp table creation part at the top of the code , I think this is what you meant as now all the sample data should easily populate without having to type all that sample table data in. BUT NOW this version actually works.... I think it has to do with the ROW NUMBER part and using it on a view, instead of a regular table. But my problem is that I have to do it on the view because the underlying table will update a lot and the view will update with it each time, but a static table will NOT. Hoping for any insights on how to make it work with the view version too if possible. – Kc Ryan Jan 21 '21 at 17:28
  • Nice job! That makes it easy for anyone to copy and paste the SQL out and work on the problem. Just a note, a temporary table always starts with `#` - you have created actual tables. So views just encapsulate SQL, which is replaced at execution time. Therefore the fact that you use a view normally shouldn't make any difference. You must actually have used slightly different SQL here from your real (view) situation. So if you haven't done so, copy and paste the view SQL into the appropriate place in your example SQL. On the other hand that tells you exactly where to look for the issue. – Dale K Jan 21 '21 at 19:50

1 Answers1

0

not sure if this helps... Your query seems to be a bit of a monster. When I am working on a hugely complex query I sometimes use table variables (to inspect the results of CTE during the process).

So you could re-write the first section of your code like this to inspect the first CTE...

--declare your table variable    
declare @MyTableVar as (
        RN_By_Reg_Order_DPRTDTMZ INT
        DPRT_STA_CD VARCHAR(3),
        ACT_DPRT_DTMZ DATETIME,
        ARRV_STA_CD VARCHAR(3),
        ACT_ARRV_DTMZ DATETIME,
        REGISTRATION_ID VARCHAR(30) 
     )

--insert results into your table variable
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
into @MyTableVar        
        From tempflights2 ss

--now you can inspect the contents of your @MyTableVar
select * from @MyTableVar

--and you can continue on with your monster query...
with 
    cteSSPF as
        (
    select * from @MyTableVar
    ),
    --End of SSPF

--etc etc etc

BUT, when you are finished I would recommend restoring the CTE the way you had it, because SQL server may have trouble optimizing this syntax for best performance.

user15282382
  • 31
  • 1
  • 6