An Inner join in an Outer Join leading to peformance shortfalls, what is a different approach?

Question

I have a view in one of my databases that is retrieving the previous and current case officers(think person) from a few tables and views. The issue is these records are only linked by the end date(saoh.Date_TO) being the same as another case officers start date (saoh.Date_FROM).

To create a join between these records I am currently doing an outer join with an inner join inside it. (This can be seen in the script below). The issue is the view has ~3 million records. This means to then query this view is taking an extremely long time (2-3 hours).

Does anyone have any suggestions on how to improve the fundamental design of the SQL below.

Further Information
Environment: MSSQL server 2008
Other Info: Snapshot_Period is a tool for reporting and not linked to the case officer dates.

   ALTER VIEW [dbo].[vw_Stage_Estate_Case_Officer_Source] AS
    SELECT  sp.SNAPSHOT_PERIOD_START_DATETIME,
            sp.SNAPSHOT_PERIOD_END_DATETIME,
            aes.APPLICATION_RID,
            aes.ESTATE_RID,
            aes.TRUSTEE_NUMBER,
            aes.TRUSTEE_TYPE,
            aes.TEAM_CODE,
            saoh.POSITION,
            saoh.DATE_FROM,
            saoh.DATE_TO,
            saoh.ERROR_CONDITION,
            saoch.USER_ID as PRIOR_CASE_OFFICER
    FROM    Stage_App_Estate_Statuses aes
            /*Standard snapshot period new join for MonthlyITS and yearlyTIS*/
            INNER JOIN  Stage_Snapshot_Period_New sp ON
                        change_date < sp.SNAPSHOT_PERIOD_END_DATETIME and
                        sp.SNAPSHOT_PERIOD_IS_FINALISED_INDICATOR = 'No'and 
                       (sp.SNAPSHOT_PERIOD_TYPE_NAME = 'MonthlyITS' or sp.SNAPSHOT_PERIOD_TYPE_NAME = 'YearlyITS')
            /*This should be inner joining to the staging table that links Case officers to team codes by region*/
            INNER JOIN  [DEV_STAGING].[dbo].[STAGE_STAF_ACTION_OFFICERS] saoh ON
                        aes.TEAM_CODE = saoh.POSITION AND LEFT(aes.ESTATE_RID,3) = LEFT(saoh.ACTION_OFFICER_RID,3)
            /*This should be inner joining to App_Estate_Statues again to get the previous UserID*/
            LEFT OUTER JOIN (SELECT staf.USER_ID,
                                staf.DATE_FROM, 
                                staf.DATE_TO,
                                a.TEAM_CODE,
                                a.ESTATE_RID
                        FROM [DEV_STAGING].[dbo].[STAGE_STAF_ACTION_OFFICERS] staf
                        INNER JOIN  Stage_App_Estate_Statuses a ON
                                    a.TEAM_CODE = staf.POSITION) saoch  ON
                         saoh.DATE_TO = saoch.DATE_FROM AND saoch.ESTATE_RID = aes.ESTATE_RID

    GO

Table Definitions Snapshot Period: Irrelevant / Must stay as is.
Stage App Estate Statuses:

CREATE TABLE [dbo].[Stage_App_Estate_Statuses](
    [ESTATE_STATUS_RID] [nvarchar](20) NOT NULL,
    [ESTATE_RID] [nvarchar](30) NULL,
    [CATEGORY] [decimal](1, 0) NULL,
    [CHANGE_DATE] [datetime2](0) NULL,
    [CHANGE_TYPE] [nvarchar](3) NULL,
    [STATUS] [nvarchar](1) NULL,
    [TEAM_CODE] [nvarchar](4) NULL,
    [TRUSTEE_NUMBER] [decimal](22, 0) NULL,
    [TRUSTEE_TYPE] [nvarchar](10) NULL,
    [APPLICATION_RID] [nvarchar](30) NULL,
    [DML_TYPE] [nvarchar](1) NOT NULL,
    [AUDIT_KEY] [int] NOT NULL
) ON [PRIMARY]

Stage_STAF_ACTION_OFFICERS

CREATE TABLE [dbo].[Stage_STAF_ACTION_OFFICERS](
    [ACTION_OFFICER_RID] [nvarchar](15) NOT NULL,
    [POSITION] [nvarchar](13) NULL,
    [DATE_FROM] [date] NULL,
    [DATE_TO] [date] NULL,
    [USER_ID] [nvarchar](32) NULL,
    [ERROR_CONDITION] [nvarchar](100) NULL,
    [EXTRACTED_DATE] [date] NULL,
    [DML_TYPE] [nvarchar](1) NULL,
    [AUDIT_KEY] [int] NOT NULL
) ON [PRIMARY]

Answer 1

Its hard not having anything to test against so just treat this as a starting point

SELECT
    sp.SNAPSHOT_PERIOD_START_DATETIME,
    sp.SNAPSHOT_PERIOD_END_DATETIME,
    aes.APPLICATION_RID,
    aes.ESTATE_RID,
    aes.TRUSTEE_NUMBER,
    aes.TRUSTEE_TYPE,
    aes.TEAM_CODE,
    saoh.POSITION,
    saoh.DATE_FROM,
    saoh.DATE_TO,
    saoh.ERROR_CONDITION,
    (select USER_ID
        from [DEV_STAGING].[dbo].[STAGE_STAF_ACTION_OFFICERS] sao
        where sao.DATE_FROM = saoh.DATE_TO
            and (select ESTATE_RID from Stage_App_Estate_Statuses where TEAM_CODE = sao.POSITION) = aes.ESTATE_RID
    )as PRIOR_CASE_OFFICER --Even if this approach doesnt help, keep the original join as outer because there might not be a prior_case_officer
FROM
    Stage_App_Estate_Statuses aes
    /This should be inner joining to the staging table that links Case officers to team codes by region/
    INNER JOIN [DEV_STAGING].[dbo].[STAGE_STAF_ACTION_OFFICERS] saoh 
        ON aes.TEAM_CODE = saoh.POSITION 
        AND LEFT(aes.ESTATE_RID,3) = LEFT(saoh.ACTION_OFFICER_RID,3)
            /Standard snapshot period new join for MonthlyITS and yearlyTIS/
    INNER JOIN (select

                    SNAPSHOT_PERIOD_START_DATETIME,
                    SNAPSHOT_PERIOD_END_DATETIME,

                from Stage_Snapshot_Period_New
                where 
                    SNAPSHOT_PERIOD_IS_FINALISED_INDICATOR = 'No'
                    and SNAPSHOT_PERIOD_TYPE_NAME in ('MonthlyITS','YearlyITS')
                ) sp
        ON change_date < sp.SNAPSHOT_PERIOD_END_DATETIME