Code:
SELECT NewNotes.BSI_ID
, NewNotes.Narrative
, NewNotes.Create_Date
, NewNotes.Trust_ID
,SWITCH(
[ER_Master].[erm;ID] IS NOT NULL, "ER"
, [EE_Master].[eem;ID] IS NOT NULL, "EE"
, [DEP_Master].[dpm;ID], "DEP"
, RET_MAST2.[rmID], "RET"
) AS [Entity_Type]
, SWITCH(
[ER_Master].[erm;ID] IS NOT NULL, [ER_Master].[erm;ID]
,[ER_EE_Xref_MAIN2].[eex_Employer ID] IS NOT NULL, [ER_EE_Xref_MAIN2].[eex_Employer ID]
, RET_MAST2.[rmEmployer] IS NOT NULL, RET_MAST2.[rmEmployer]
, [ER_EE_Xref_MAIN4].[eex_Employer ID] IS NOT NULL, [ER_EE_Xref_MAIN4].[eex_Employer ID]
) AS [Employer_ID]
, SWITCH(
[EE_Master].[eem;ID] IS NOT NULL, [EE_Master].[eem;ID]
, RET_MAST2.[rmEmployee] IS NOT NULL, RET_MAST2.[rmEmployee]
, [EE_DEP_Xref_MAIN2].[edx_Employee ID] IS NOT NULL, [EE_DEP_Xref_MAIN2].[edx_Employee ID]
) AS [Employee_ID]
, [DEP_Master].[dpm;ID]
, IIF (INSTR(NewNotes.Narrative, "(")>0,
IIF(INSTR(NewNotes.Narrative, ")") > 0,
IIF(ISNUMERIC(Mid(NewNotes.Narrative, (instr(NewNotes.Narrative,"(")+1), (instr(NewNotes.Narrative,")") - (instr(NewNotes.Narrative,"(")+1)))), Mid(NewNotes.Narrative, (instr(NewNotes.Narrative,"(")+1), (instr(NewNotes.Narrative,")") - (instr(NewNotes.Narrative,"(")+1))), NULL)
, null)
, null) AS [Call Category ID]
FROM ((((((((((NewNotes
-- Join to each of the entity master tables
LEFT OUTER JOIN BSFIL020 AS [ER_Master] ON NewNotes.BSI_ID = [ER_Master].[erm;ID])
LEFT OUTER JOIN BSFIL022 AS [EE_Master] ON NewNotes.BSI_ID = [EE_Master].[eem;ID])
LEFT OUTER JOIN BSFIL023 AS [DEP_Master] ON NewNotes.BSI_ID = [DEP_Master].[dpm;ID])
LEFT OUTER JOIN RET_MAST2 ON NewNotes.BSI_ID = RET_MAST2.[rmID])
-- Get's most recent dependent entry, must join again to get employee ID
LEFT OUTER JOIN
(SELECT [EE_DEP_Xref_SUB].[edx_Dependent ID], MAX([EE_DEP_Xref_SUB].[edx_Effective Date]) AS [Max_Date]
FROM BSFIL069 AS [EE_DEP_Xref_SUB]
GROUP BY [EE_DEP_Xref_SUB].[edx_Dependent ID]) AS [EE_DEP_Xref_MAIN]
ON [DEP_Master].[dpm;ID] = [EE_DEP_Xref_MAIN].[edx_Dependent ID])
-- Get employee ID from Dependents
LEFT OUTER JOIN BSFIL069 AS [EE_DEP_Xref_MAIN2] ON (([EE_DEP_Xref_MAIN].[edx_Dependent ID] = [EE_DEP_Xref_MAIN2].[edx_Dependent ID]) AND ([EE_DEP_Xref_MAIN].[Max_Date] = [EE_DEP_Xref_MAIN2].[edx_Effective Date])))
-- Get's most recent employee entry (starting from Dependent), must join again to get employer
LEFT OUTER JOIN
(SELECT [ER_EE_Xref_SUB2].[eex_Employee ID], MAX([ER_EE_Xref_SUB2].[eex_Effective Date]) AS [Max_Date]
FROM BSFIL068 AS [ER_EE_Xref_SUB2]
GROUP BY [ER_EE_Xref_SUB2].[eex_Employee ID]) AS [ER_EE_Xref_MAIN3]
ON [EE_DEP_Xref_MAIN2].[edx_Employee ID] = [ER_EE_Xref_MAIN3].[eex_Employee ID])
-- Get Employer starting from Dependent
LEFT OUTER JOIN BSFIL068 AS [ER_EE_Xref_MAIN4] ON (([ER_EE_Xref_MAIN3].[eex_Employee ID] = [ER_EE_Xref_MAIN4].[eex_Employee ID]) AND ([ER_EE_Xref_MAIN3].[Max_Date] = [ER_EE_Xref_MAIN4].[eex_Effective Date])))
-- Get's most recent employee entry (joined on Employee Master), must join again to get employer
LEFT OUTER JOIN
(SELECT [ER_EE_Xref_SUB].[eex_Employee ID], MAX([ER_EE_Xref_SUB].[eex_Effective Date]) AS [Max_Date]
FROM BSFIL068 AS [ER_EE_Xref_SUB]
GROUP BY [ER_EE_Xref_SUB].[eex_Employee ID]) AS [ER_EE_Xref_MAIN]
ON [EE_Master].[eem;ID] = [ER_EE_Xref_MAIN].[eex_Employee ID])
-- Get Employer starting from Employee Master
LEFT OUTER JOIN BSFIL068 AS [ER_EE_Xref_MAIN2] ON (([ER_EE_Xref_MAIN].[eex_Employee ID] = [ER_EE_Xref_MAIN2].[eex_Employee ID]) AND ([ER_EE_Xref_MAIN].[Max_Date] = [ER_EE_Xref_MAIN2].[eex_Effective Date])))
... As you can see, not the most maintainable code in the world.