The code below runs fine and brings in the correct Provider Lookup; however, if I change a."Provider Lookup" to a."Provider", it does not bring back the Provider but plugs in the "N". I can't figure out what's happening... I have confirmed that the HCPC in the WFMGMT_PRD.map_auto_provider table and zip code match to the same instance within the wfmgmt_prd.open_report_hourly
select o."zip code",
MAX(CASE WHEN INSTR(o.'provider hcpc/revenue code', a.HCPC) >= 1 and cast(cast(cast(o.'zip code' as float) as int) as varchar)= a.zip THEN a."Provider Lookup" ELSE 'N' END) "Auto-Provider"
from wfmgmt_prd.open_report_hourly o
CROSS JOIN WFMGMT_PRD.map_auto_provider a
where "Report Interval" >= (select max("Report Interval") from wfmgmt_prd.open_report_hourly)
and o."queue type" = 'Provider Staffing'
and o."intake id"='10969508'
group by o."zip code",o."provider hcpc/revenue code"
