Pasted

Select distinct base.AppID as Id, base.ExtID__c, 
        case 
            when 3 < len( base.feedback_status__C ) then
                case 
                    when base.feedback_status__C in ( 'Not Started', 'Started' ) then 'Completed – No Report' 
                    else  base.feedback_status__C 
                end
            else base.FORCEBRAIN__STATUS__C + '/' + base.FORCEBRAIN__OVERALL_APPT_STATUS_TEXT__C 
        end as Interview_1_Status__c,
        base.Interview_Type__c,
        Cast( Left( dbo.TrimToNull( base.Interview_Location__c ), 200 ) as varchar( 200 ) ) as Interview_Location__c,
        Int.Interviewer__c,
        base.Interview_1_Date_Time__c as Interview_1_Date_Time__c,
        case 
            when base.CURRENT_STATUS__C IN ( 
                    'Denied', 'Deferred', 'Accepted', 'Deposit Required', 'Offer', 'Accepted Offer', 'Withdraw', 'Not Entering', 
                    'Contingent Offer', 'Denied from Waitlist', 'WaitList', 'Enrollment Form Required', 'Waiting on Contingency', 
                    'Send to Committee' 
                ) then base.CURRENT_STATUS__C 
            else 
                case 
                    when 
                        case 
                            when 3 < len( base.feedback_status__C ) then 
                                case 
                                    when base.feedback_status__C in ( 'Not Started', 'Started' ) then 'Completed - No Report' 
                                    else base.feedback_status__C 
                                end
                            else base.FORCEBRAIN__STATUS__C + '/' + base.FORCEBRAIN__OVERALL_APPT_STATUS_TEXT__C 
                        end = 'Completed' then 'Send to Committee' 
                    else CURRENT_STATUS__C 
                end 
        end as Current_Status__c
    from ( 
        Select C1.FEEDBACK_STATUS__C, B1.AppID, B1.APPLY_YEAR__C, B1.CURRENT_STATUS__C, B1.ExtID__c, B1.FIRSTNAME, B1.FORCEBRAIN__APPOINTMENT_STATUS__C, 
                B1.FORCEBRAIN__FIRST_NAME__C, B1.FORCEBRAIN__IS_PROVIDER__C, B1.FORCEBRAIN__LAST_NAME__C, B1.FORCEBRAIN__LEAD__C, 
                B1.FORCEBRAIN__LOCATION_SUMO__C, B1.FORCEBRAIN__LOCATIONADDRESS__C, B1.FORCEBRAIN__OVERALL_APPT_STATUS_TEXT__C, B1.FORCEBRAIN__ROOM_SUMO__C, 
                B1.FORCEBRAIN__STATUS__C, B1.ID, B1.Interview_1_Date_Time__c, B1.Interview_Location__c, B1.Interview_Type__c, B1.LASTNAME, B1.LeadID, 
                B1.NAME, B1.PROGRAM_C__C, B1.Ranker, B1.[FORCEBRAIN__DESCRIPTION__C]
            from (
                select d.ID AS AppID, a.FORCEBRAIN__DESCRIPTION__C, d.APPLY_YEAR__C, 
                        d.PROGRAM_C__C, d.CURRENT_STATUS__C, d.ExtID__c, a.ID, 
                        b.FORCEBRAIN__APPOINTMENT_STATUS__C, b.FORCEBRAIN__LEAD__C, c.FIRSTNAME, 
                        c.LASTNAME, c.ID AS LeadID, a.FORCEBRAIN__LOCATION_TEXT__C AS Interview_Type__c, 
                        a.FORCEBRAIN__LOCATION_SUMO__C, a.FORCEBRAIN__STARTDATETIME__C AS Interview_1_Date_Time__c, 
                        a.FORCEBRAIN__STATUS__C, a.FORCEBRAIN__OVERALL_APPT_STATUS_TEXT__C, 
                        a.FORCEBRAIN__LOCATIONADDRESS__C, 
                        RANK () over( partition by d.ID order by convert( date, a.FORCEBRAIN__START_DATE_TIME_TEXT__C ) desc ) as Ranker,
                        REPLACE( REPLACE( REPLACE( a.FORCEBRAIN__LOCATIONADDRESS__C, '<br>', '' ), ',', '' ), '  ', ' ' ) 
                            + CASE 
                                WHEN 1 < LEN( e.NAME ) THEN ' / Room: ' + e.NAME 
                                ELSE '' 
                            END 
                            + CASE 
                                WHEN a.FORCEBRAIN__LOCATION_TEXT__C = 'Skype' THEN ' / Skype: ' + a.[FORCEBRAIN__DESCRIPTION__C] 
                                ELSE '' 
                            END 
                        AS Interview_Location__c, 
                        e.NAME, a.FORCEBRAIN__ROOM_SUMO__C, b.FORCEBRAIN__IS_PROVIDER__C, 
                        b.FORCEBRAIN__FIRST_NAME__C, b.FORCEBRAIN__LAST_NAME__C, 
                        a.FORCEBRAIN__EVENTTYPE__C
                    from dbo.AppointmentSUMO_tbl as a
                    inner join dbo.AppointmentParticipant_tbl as b ON (
                        ( a.ID = b.FORCEBRAIN__EVENT__C )
                    )
                    inner join dbo.Lead_tbl as c ON (
                        ( b.FORCEBRAIN__LEAD__C = c.ID )
                    )
                    inner join dbo.ADM_Applications as d ON (
                        ( c.ID = d.APPLICANT_LEAD__C )
                    )
                    left outer join dbo.SUMORoom_tbl as e ON (
                        ( a.FORCEBRAIN__ROOM_SUMO__C = e.ID )
                    )
                    where (
                        ( d.PROGRAM_C__C IN ( 'a0vA0000001UIRBIA4', 'a0vA0000001UIRLIA4' ) ) 
                        and (d.EXTERNAL_STATUS__C not in ( 'closed', 'complete', '', 'accepted', 'inactive', 'deposit required', 'incomplete', 'In Progress' ) ) 
                        and (a.FORCEBRAIN__EVENTTYPE__C IN ( 'a3vA0000000kAKqIAM', 'a3vA0000000kAKvIAM', 'a3vA0000000kAL0IAM' ) )
                    )
        ) as B1
        left outer join (
            select c.ADMISSIONS_APPLICATION_ID__C, c.FEEDBACK_STATUS__C
                from dbo.Program_tbl as a
                RIGHT OUTER JOIN dbo.ADM_Applications as b ON a.ID = b.PROGRAM_C__C 
                RIGHT OUTER JOIN dbo.CandidateFeedback_tbl as c ON b.ID = c.ADMISSIONS_APPLICATION_ID__C
                where (
                    ( c.RECORDTYPEID = '012A00000012dSmIAI' )
                    and ( b.EXTERNAL_STATUS__C not in ( 'Accepted', 'Closed', 'Complete', 'Denied', 'Inactive', 'In Progress' ) ) 
                    and ( a.ID in ( 'a0vA0000001UIRBIA4', 'a0vA0000001UIRLIA4' ) )
                )
        ) as C1 on (
            ( B1.AppID = C1.ADMISSIONS_APPLICATION_ID__C )
        ) 
    )as base
    left outer join (
        select a.ID, c.ID AS Interviewer__c
            from dbo.AppointmentSUMO_tbl as a
            inner join dbo.AppointmentParticipant_tbl as b ON (
                ( a.ID = b.FORCEBRAIN__EVENT__C )
            )
            inner join dbo.Contacts_tbl as c ON (
                ( b.FORCEBRAIN__CUSTOMER__C collate SQL_Latin1_General_CP1_CS_AS = c.ID collate SQL_Latin1_General_CP1_CS_AS )
            )
            where ( '1' = b.FORCEBRAIN__IS_PROVIDER__C )
            group by a.ID, c.ID
    ) as Int on (
        ( Base.ID = Int.ID )
    )
    left outer join (
        select ID, Interview_1_Status__c
            from dbo.ADM_Applications
    ) as ADM on (
        ( base.AppID = adm.ID )
    )
    where (
        ( 
            '1' = Ranker
            and Interview_1_Status__c not in ( 'Completed', 'Complete' )
            and Interview_1_Status__c != case 
                    when 3 < len( base.feedback_status__C ) then ( 
                        case 
                            when base.feedback_status__C in ( 'Not Started', 'Started' ) then 'Completed - No Report' 
                            else  base.feedback_status__C 
                        end 
                    )
                    else base.FORCEBRAIN__STATUS__C + '/' + base.FORCEBRAIN__OVERALL_APPT_STATUS_TEXT__C 
                end
        )
        and datediff( month, base.Interview_1_Date_Time__c, GETDATE() )  < 4
    )
    order by extid__C