jeffshelix - Monday, February 19, 2018 12:22 PM

UPDATE t
SET Elig_Flag = P.ELIGIBILITY_FLG
FROM #TEMP_STP t
CROSS APPLY (
 SELECT P.ELIGIBILITY_FLG
 FROM [SOLICIT].[GTM_DOC_PROD] P
 INNER JOIN [SOLICIT].[GTM_DOC] d
  ON p.[SOLICITATION_ID] = d.[SOLICITATION_ID]
 WHERE P.[PROD_ID] = t.[prodid]
  AND D.[SUPPLIER_ID] = t.[Site]
     AND D.CLOSE_Date = (SELECT MAX(CLOSE_Date) FROM [SOLICIT].[GTM_DOC])
) x

Results in:  The multi-part identifier "P.ELIGIBILITY_FLG" could not be bound.

I need to learn how  to do this type of logic all the time and get inside out every time I try.
Here, in this case, i want the Eligiblity_Flag for the record with the Max Close_Date.
The Eligibility_Flag is in the PROD table and the CLOSE_DATE is in the DOC_PROD

thanks again for the help