SELECT x.input_cd,
        x.input_num,
        x.inputdt,
        x.email_address
    ,max(pt.sale_dt) AS last_order_dt
    ,max(pt.sales_ord_id) AS last_sales_ord_id --- RESULT
FROM (
    SELECT  o.sys_cd as input_cd,
            o.host_num as input_num,
            o.EMAIL_Addr_Text AS email_address
           ,cast(coalesce(o.sales_ord_create_dttm  - INTERVAL '6' hour, host_acct_create_dt) as date) as input_dt
    FROM pfocusvw.sales_ord o
    WHERE o.host_vax_acct_num || '/' || o.host_sys_cd IN('order1', 'order2', .... , 'orderN')
    and cast(coalesce(o.salesdttm, host_dt) as date) between to_date( '2019-03-14', 'yyyy-mm-dd') and to_date( '2019-03-15', 'yyyy-mm-dd')
    ) x
LEFT JOIN live.db_tran pt ON x.email_address = pt.email_addr
INNER JOIN live.db_event pe ON pt.event_id = pe.event_id
WHERE pe.type_6_cd = 'standard'
    AND pe.host_cd IN (
        'US')
    AND pt.ovrrd_cd IN (
        'P'
        ,'H'
        ,'I'
        ,'A'
        )
    AND pt.sale_dt < x.input_dt
    AND pt.tran_type_cd IN ('PRIMARY','RESALE')
    AND (
        tkt.Name1 IS NULL
        OR (
            -- Hard  coded list that must be updated  periodically
            Name1 NOT LIKE 'A' -- Partners that served through API
            AND name1 NOT LIKE 'B'
            AND name1 NOT LIKE 'C'
            AND name1 NOT LIKE 'D'
            AND name1 NOT LIKE 'D'
            AND name1 NOT LIKE 'E'
            AND name1 NOT LIKE 'F'
            AND name1 NOT LIKE 'G'
            AND name1 NOT LIKE 'H'

            )
        )
    group by 1,2,3,4;