Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement