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 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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement