Q:

Find my missing column in joined pivot tables

Code below works with the exception of the comment on line 2.  I need to have AN_52_ENC_CSN_ID in my results, but it disappeared when I joined the two pivot tables.  I have tried referencing it in T11, T1, T2 and with variation of its qualified name.  I imagine there is something easy I have missed, but I have worked on it for hours.
select 
    -- t11.AN_52_ENC_CSN_ID,
    DC_FLOOR,  
    DC_HOME,  
    IN_PACU, 
    IN_OR, 
    PreOP_Complete, 
    IN_PreOP,  
    Out_Of_OR, 
    Surgery_Stop, 
    Surgery_Start,
    Case_CX_POHA ,
    CASE
    WHEN Case_CX_POHA IS NOT NULL
    THEN 'YES'
    ELSE 'NO'
    END AS CX_POHA,
     CASE
    WHEN Case_CX_INTRAOP IS NOT NULL
    THEN 'YES'
    ELSE 'NO'
    END AS CX_INTRAOP,
    CASE
    WHEN DC_HOME IS NOT NULL
    THEN 'Outpatient'
    ELSE 'Inpatient'
    END AS "IP/OP",
    Induction_Time,
    Ready_Case,
    LMA_Inserted,
    LMA_Removed,
    Anesthesia_Start,
    Anesthesia_Stop,
    Intubation,
    Extubation,
    Emergence_Start,
    GREATEST(ROUND(((INDUCTION_TIME)  -   (IN_OR)) * 24 * 60), 0)             AS Induction_Promptness,
    GREATEST(ROUND(((READY_CASE)  -       (IN_OR)) * 24 * 60), 0)                 AS OR_to_Ready_for_Surgery,
    GREATEST(ROUND(((SURGERY_START) -     (READY_CASE)) * 24 * 60), 0)          AS Ready_to_Surgery_Start,
    GREATEST(ROUND(((SURGERY_STOP)  -     (SURGERY_START)) * 24 * 60), 0)       AS Surgery_Duration,
    GREATEST(ROUND(((OUT_OF_OR)  -        (SURGERY_STOP)) * 24 * 60), 0)           AS Surgery_Stop_to_Out_OR,
    GREATEST(ROUND(((OUT_OF_OR)  -        (Emergence_START)) * 24 * 60), 0)               AS Emergence_to_Out_of_OR,

    ROUND(COALESCE(((LMA_REMOVED - EMERGENCE_START) * 24 * 60), ((EXTUBATION - EMERGENCE_START) * 24 * 60)))      AS Emerg_to_extub,
    ROUND(greatest(COALESCE(((DC_FLOOR - IN_PACU) * 24 * 60), ((DC_HOME - IN_PACU) * 24 * 60)), COALESCE(((DC_HOME - IN_PACU) * 24 * 60), ((DC_FLOOR - IN_PACU) * 24 * 60)))) AS PACU_Time

from

(

WITH TABLE_PATIENT AS
(   
SELECT 
CLARITY.OR_LOG_CASE_TIMES.TRACKING_TIME_IN,
CLARITY.F_AN_RECORD_SUMMARY.AN_52_ENC_CSN_ID,
CLARITY.OR_LOG_CASE_TIMES.TRACKING_EVENT_C 

FROM 
CLARITY.F_AN_RECORD_SUMMARY
INNER JOIN CLARITY.OR_LOG
ON CLARITY.F_AN_RECORD_SUMMARY.LOG_ID = CLARITY.OR_LOG.LOG_ID
INNER JOIN CLARITY.OR_LOG_CASE_TIMES
ON CLARITY.OR_LOG.LOG_ID = CLARITY.OR_LOG_CASE_TIMES.LOG_ID
INNER JOIN CLARITY.ZC_OR_PAT_EVENTS
ON CLARITY.OR_LOG_CASE_TIMES.TRACKING_EVENT_C  = CLARITY.ZC_OR_PAT_EVENTS.TRACKING_EVENT_C
WHERE CLARITY.OR_LOG.LOC_ID IN (4060020, 4060021)

group by F_AN_RECORD_SUMMARY.AN_52_ENC_CSN_ID,
CLARITY.OR_LOG_CASE_TIMES.TRACKING_TIME_IN,
CLARITY.OR_LOG_CASE_TIMES.TRACKING_EVENT_C
)

SELECT 
*
FROM TABLE_PATIENT 
PIVOT
(
    min(TRACKING_TIME_IN)
    for TRACKING_EVENT_C IN (310 as DC_FLOOR, 340 as DC_HOME ,380 AS IN_PACU, 120 as IN_OR, 100 as PreOP_Complete, 60 as IN_PreOP, 160 as Out_Of_OR, 150 as Surgery_Stop, 130 as Surgery_Start) 

)T2 

-- END TRACKING TIMES

-- BEGIN EVENT TIMES

inner Join
(

WITH TABLE_PATIENT2 AS
(   
SELECT 
CLARITY.ED_IEV_EVENT_INFO.EVENT_TIME,
CLARITY.F_AN_RECORD_SUMMARY.AN_52_ENC_CSN_ID,
CLARITY.ED_IEV_EVENT_INFO.EVENT_TYPE 

 FROM CLARITY.OR_LOG
    INNER JOIN CLARITY.F_AN_RECORD_SUMMARY
    ON CLARITY.OR_LOG.LOG_ID = CLARITY.F_AN_RECORD_SUMMARY.LOG_ID
    INNER JOIN CLARITY.V_LOG_BASED
    ON CLARITY.OR_LOG.LOG_ID = CLARITY.V_LOG_BASED.LOG_ID
    INNER JOIN CLARITY.PATIENT
    ON CLARITY.F_AN_RECORD_SUMMARY.AN_PAT_ID = CLARITY.PATIENT.PAT_ID
    INNER JOIN CLARITY.ED_IEV_PAT_INFO
    ON CLARITY.F_AN_RECORD_SUMMARY.AN_52_ENC_CSN_ID = CLARITY.ED_IEV_PAT_INFO.PAT_CSN
    INNER JOIN CLARITY.ED_IEV_EVENT_INFO
    ON CLARITY.ED_IEV_PAT_INFO.EVENT_ID      = CLARITY.ED_IEV_EVENT_INFO.EVENT_ID
    WHERE CLARITY.OR_LOG.LOC_ID             IN (4060020, 4060021)
    AND (CLARITY.ED_IEV_EVENT_INFO.EVENT_STATUS_C is null or CLARITY.ED_IEV_EVENT_INFO.EVENT_STATUS_C = 1)

group by F_AN_RECORD_SUMMARY.AN_52_ENC_CSN_ID,
CLARITY.ED_IEV_EVENT_INFO.EVENT_TIME,
CLARITY.ED_IEV_EVENT_INFO.EVENT_TYPE
)

SELECT *

FROM TABLE_PATIENT2
PIVOT
(
    min(EVENT_TIME)
    for EVENT_TYPE IN (1170082 AS Case_CX_POHA, 1170083 as Case_CX_IntraOp, 1170007 as Induction_Time, 1170009 as Ready_Case, 117100180 as LMA_Inserted, 117100179 as LMA_Removed, 1170001 as Anesthesia_Start, 1170002 as Anesthesia_Stop, 1170102 as Intubation, 1170112 as Extubation, 1170113 as Emergence_Start)
)
)T3
On T3.AN_52_ENC_CSN_ID = T2.AN_52_ENC_CSN_ID

)t11

where t11.Case_CX_POHA is not null or (((ANESTHESIA_STOP - ANESTHESIA_START) *60 *24) > 0)

A:

I guess the problem is that the name is ambiguous for SQL. In T3, try :
select AN_52_ENC_CSN_ID AS AN_52,
EVENT_TIME,
EVENT_TYPE
FROM TABLE_PATIENT2
PIVOT

etc after the pivot. you have two colums named AN_52_ENC_CSN_ID from two different tables (T2 and T3) and you select things from the join of the two of them T11. Try then in your line 2 T11.AN_52_ENC_CSN_ID, or T11.AN_52, both should work. Remember to change your ON clause in the inner join of t2 and t3 as 
ON T3.AN_52 = T2.AN_52_ENC_CSN_ID

Tell me if it works !

