Use CTE instead of Subquery in SQL Server

Question

I have the below query and it works well enough but I have been told that using CTEis a better route than a giant subquery. I am pretty new to the concept and have tried to get this to work by pulling out everything from the CASEstatement to the last CONVERT in the SELECT list but I have had no luck. Are there any tips for somebody trying to use CTE instead of subqueries? The reason I needed to use a subquery is that I needed to compare the dates from the CASEstatement with one of the other alias dates and cannot in WHERE

Thanks.

SQL:

USE PDX_SAP_USER
GO

SELECT *
FROM (
    SELECT E.team_member_name [EMPLOYEE], K.business_segment_desc [BUSINESS SEGMENT], G.order_status [GPS ORDER STATUS], H.po_type [PO TYPE], G.order_no [GPS ORDER NO], I.po_number [SAP PO NUMBER], I.shipping_instruct [SAP SHIP MODE], G.shipping_type [GPS SHIP MODE], CASE 
            WHEN I.shipping_instruct LIKE 'A%'
                THEN CONVERT(VARCHAR(12), (I.revised_ex_factory + 10), 101)
            WHEN I.shipping_instruct LIKE 'C%'
                THEN CONVERT(VARCHAR(12), (I.revised_ex_factory + 5), 101)
            END [DESIRED TRANSIT DATE], CONVERT(VARCHAR(12), I.revised_ex_factory, 101) [LAST CONFIRMED DATE ], CONVERT(VARCHAR(12), S.po_estimated_deliv_date, 101) [CURRENT DELIV DATE],
        -- 'days_diff'
        I.material [MATERIAL], M.description [DESCRIPTION], I.stock_category [STOCK CATEGORY], I.po_ordered_quantity [PO ORDERED QUANTITY], I.po_recvd_quantity [PO RECVD QUANTITY], I.po_balance_quantity [PO BALANCE QUANTITY], I.po_intransit_quantity [PO INTRANSIT QUANTITY], I.plant_code [PLANT], I.direct_ship_code [DS CODE], I.comment [COMMENT]
    FROM (
        SELECT order_no, order_status, shipping_type
        FROM asagdwpdx_prod.dbo.SimoxOrder1

        UNION ALL

        SELECT order_no, order_status, shipping_type
        FROM asagdwpdx_prod.dbo.SimoxOrder2

        UNION ALL

        SELECT order_no, order_status, shipping_type
        FROM asagdwpdx_prod.dbo.SimoxOrder3
        ) G
    INNER JOIN pdx_sap_user..vw_po_header H ON G.order_no = H.ahag_number
    INNER JOIN pdx_sap_user..vw_po_item I ON H.po_number = I.po_number
    INNER JOIN pdx_sap_user..vw_po_size S ON I.po_number = S.po_number
        AND I.po_item_number = S.po_item_number
    INNER JOIN pdx_sap_user..vw_mm_material M ON I.material = M.material
    INNER JOIN pdx_sap_user..vw_kd_business_segment K ON M.business_segment_code = K.business_segment_code
    INNER JOIN adi_user_maintained..scm_po_employee_name E ON I.po_number = E.po_number
    WHERE I.po_balance_quantity > 0
        AND I.del_indicator NOT IN ('L', 'S')
        AND H.PO_TYPE NOT IN ('01', 'UB')
        AND I.shipping_instruct IN ('A1', 'A2', 'A5', 'C1', 'C2', 'C3')
        AND G.order_status = '40'
    ) q1
WHERE q1.[DESIRED TRANSIT DATE] <> q1.[CURRENT DELIV DATE]
ORDER BY q1.[SAP PO NUMBER]

Answer 1

The only difference between a derived table and a CTE (other than the syntax of course) is that a CTE allows you to write recursive queries which self reference (aka Recursive CTE). It is sometimes cleaner to abstract things into a CTE ahead of time, but it's just a matter of preference, not a performance matter. Personally I tend to be a fan of using CTEs to abstract big ugly queries, but there's not really a strong argument for one over the other.

By way of a trivial example, these two statements are functionally identical

 --- using a CTE
;with numbers as
(
    select top 10 num = row_number() over (order by (select null))
    from sys.all_objects
)
select *
from numbers    

 -- Using a derived table
select *
from
(
    select top 10 num = row_number() over (order by (select null))
    from sys.all_objects
) a