Select distinct

base.AppID as Id,
base.ExtID__c,

case when len(base.feedback_status__C) > 3 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,
--cast(base.Interview_1_Date_Time__c as datetime) as Interview_1_Date_Time__c,

--CONVERT(datetime,SWITCHOFFSET(CONVERT(datetimeoffset,cast(base.Interview_1_Date_Time__c as datetime)),'-05:00')) Interview_1_Date_Time__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 len(base.feedback_status__C) > 3 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






--Interview_1_Status__c as SFIS,
--base.CURRENT_STATUS__C as SFCS

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        dbo.ADM_Applications.ID AS AppID, dbo.AppointmentSUMO_tbl.FORCEBRAIN__DESCRIPTION__C, dbo.ADM_Applications.APPLY_YEAR__C,
                         dbo.ADM_Applications.PROGRAM_C__C, dbo.ADM_Applications.CURRENT_STATUS__C, dbo.ADM_Applications.ExtID__c, dbo.AppointmentSUMO_tbl.ID,
                         dbo.AppointmentParticipant_tbl.FORCEBRAIN__APPOINTMENT_STATUS__C, dbo.AppointmentParticipant_tbl.FORCEBRAIN__LEAD__C, dbo.Lead_tbl.FIRSTNAME,
                         dbo.Lead_tbl.LASTNAME, dbo.Lead_tbl.ID AS LeadID, dbo.AppointmentSUMO_tbl.FORCEBRAIN__LOCATION_TEXT__C AS Interview_Type__c,
                         dbo.AppointmentSUMO_tbl.FORCEBRAIN__LOCATION_SUMO__C, dbo.AppointmentSUMO_tbl.FORCEBRAIN__STARTDATETIME__C AS Interview_1_Date_Time__c,
                         dbo.AppointmentSUMO_tbl.FORCEBRAIN__STATUS__C, dbo.AppointmentSUMO_tbl.FORCEBRAIN__OVERALL_APPT_STATUS_TEXT__C,
                         dbo.AppointmentSUMO_tbl.FORCEBRAIN__LOCATIONADDRESS__C,




                          RANK () over(partition by dbo.ADM_Applications.ID order by    convert(date,dbo.AppointmentSUMO_tbl.FORCEBRAIN__START_DATE_TIME_TEXT__C) desc) as Ranker,



                         REPLACE(REPLACE(REPLACE(dbo.AppointmentSUMO_tbl.FORCEBRAIN__LOCATIONADDRESS__C, '<br>', ''), ',', ''), '  ', ' ')
                         + CASE WHEN LEN(dbo.SUMORoom_tbl.NAME)
                         > 1 THEN ' / Room: ' + dbo.SUMORoom_tbl.NAME ELSE '' END + CASE WHEN dbo.AppointmentSUMO_tbl.FORCEBRAIN__LOCATION_TEXT__C = 'Skype' THEN ' / Skype: '
                          + dbo.AppointmentSUMO_tbl.[FORCEBRAIN__DESCRIPTION__C] ELSE '' END AS Interview_Location__c, dbo.SUMORoom_tbl.NAME,
                         dbo.AppointmentSUMO_tbl.FORCEBRAIN__ROOM_SUMO__C, dbo.AppointmentParticipant_tbl.FORCEBRAIN__IS_PROVIDER__C,
                         dbo.AppointmentParticipant_tbl.FORCEBRAIN__FIRST_NAME__C, dbo.AppointmentParticipant_tbl.FORCEBRAIN__LAST_NAME__C,
                         dbo.AppointmentSUMO_tbl.FORCEBRAIN__EVENTTYPE__C
FROM            dbo.AppointmentSUMO_tbl INNER JOIN
                         dbo.AppointmentParticipant_tbl ON dbo.AppointmentSUMO_tbl.ID = dbo.AppointmentParticipant_tbl.FORCEBRAIN__EVENT__C INNER JOIN
                         dbo.Lead_tbl ON dbo.AppointmentParticipant_tbl.FORCEBRAIN__LEAD__C = dbo.Lead_tbl.ID INNER JOIN
                         dbo.ADM_Applications ON dbo.Lead_tbl.ID = dbo.ADM_Applications.APPLICANT_LEAD__C LEFT OUTER JOIN
                         dbo.SUMORoom_tbl ON dbo.AppointmentSUMO_tbl.FORCEBRAIN__ROOM_SUMO__C = dbo.SUMORoom_tbl.ID
WHERE        (dbo.ADM_Applications.PROGRAM_C__C IN ('a0vA0000001UIRBIA4', 'a0vA0000001UIRLIA4')) AND (NOT (dbo.ADM_Applications.EXTERNAL_STATUS__C IN ('closed',
                         'complete', '', 'accepted', 'inactive', 'deposit required', 'incomplete', 'In Progress'))) AND
                         (dbo.AppointmentSUMO_tbl.FORCEBRAIN__EVENTTYPE__C IN ('a3vA0000000kAKqIAM', 'a3vA0000000kAKvIAM', 'a3vA0000000kAL0IAM'))) as B1


left outer join

(SELECT        TOP (100) PERCENT dbo.CandidateFeedback_tbl.ADMISSIONS_APPLICATION_ID__C, dbo.CandidateFeedback_tbl.FEEDBACK_STATUS__C
FROM            dbo.Program_tbl RIGHT OUTER JOIN
                         dbo.ADM_Applications ON dbo.Program_tbl.ID = dbo.ADM_Applications.PROGRAM_C__C RIGHT OUTER JOIN
                         dbo.CandidateFeedback_tbl ON dbo.ADM_Applications.ID = dbo.CandidateFeedback_tbl.ADMISSIONS_APPLICATION_ID__C
WHERE        (dbo.CandidateFeedback_tbl.RECORDTYPEID = '012A00000012dSmIAI') AND (NOT (dbo.ADM_Applications.EXTERNAL_STATUS__C IN ('Accepted', 'Closed', 'Complete', 'Denied', 'Inactive', 'In Progress'))) AND
                         (dbo.Program_tbl.ID IN ('a0vA0000001UIRBIA4', 'a0vA0000001UIRLIA4'))) as C1

on B1.AppID = C1.ADMISSIONS_APPLICATION_ID__C

) as base


                         left outer join


                         (SELECT        TOP (100) PERCENT dbo.AppointmentSUMO_tbl.ID, dbo.Contacts_tbl.ID AS Interviewer__c
FROM            dbo.AppointmentSUMO_tbl INNER JOIN
                         dbo.AppointmentParticipant_tbl ON dbo.AppointmentSUMO_tbl.ID = dbo.AppointmentParticipant_tbl.FORCEBRAIN__EVENT__C INNER JOIN
                         dbo.Contacts_tbl ON dbo.AppointmentParticipant_tbl.FORCEBRAIN__CUSTOMER__C collate SQL_Latin1_General_CP1_CS_AS = dbo.Contacts_tbl.ID collate SQL_Latin1_General_CP1_CS_AS
WHERE        (dbo.AppointmentParticipant_tbl.FORCEBRAIN__IS_PROVIDER__C = '1')
GROUP BY dbo.AppointmentSUMO_tbl.ID, dbo.Contacts_tbl.ID
ORDER BY dbo.AppointmentSUMO_tbl.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

(Ranker = '1' and Interview_1_Status__c not in ('Completed', 'Complete')

and

case when len(base.feedback_status__C) > 3 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 <> Interview_1_Status__c)



and datediff (month,base.Interview_1_Date_Time__c, GETDATE())  < 4

order by extid__C