SHARE
TWEET

Untitled

uniblab Oct 11th, 2019 108 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Select distinct base.AppID as Id, base.ExtID__c,
  2.         case
  3.             when 3 < len( base.feedback_status__C ) then
  4.                 case
  5.                     when base.feedback_status__C in ( 'Not Started', 'Started' ) then 'Completed – No Report'
  6.                     else  base.feedback_status__C
  7.                 end
  8.             else base.FORCEBRAIN__STATUS__C + '/' + base.FORCEBRAIN__OVERALL_APPT_STATUS_TEXT__C
  9.         end as Interview_1_Status__c,
  10.         base.Interview_Type__c,
  11.         Cast( Left( dbo.TrimToNull( base.Interview_Location__c ), 200 ) as varchar( 200 ) ) as Interview_Location__c,
  12.         Int.Interviewer__c,
  13.         base.Interview_1_Date_Time__c as Interview_1_Date_Time__c,
  14.         case
  15.             when base.CURRENT_STATUS__C IN (
  16.                     'Denied', 'Deferred', 'Accepted', 'Deposit Required', 'Offer', 'Accepted Offer', 'Withdraw', 'Not Entering',
  17.                     'Contingent Offer', 'Denied from Waitlist', 'WaitList', 'Enrollment Form Required', 'Waiting on Contingency',
  18.                     'Send to Committee'
  19.                 ) then base.CURRENT_STATUS__C
  20.             else
  21.                 case
  22.                     when
  23.                         case
  24.                             when 3 < len( base.feedback_status__C ) then
  25.                                 case
  26.                                     when base.feedback_status__C in ( 'Not Started', 'Started' ) then 'Completed - No Report'
  27.                                     else base.feedback_status__C
  28.                                 end
  29.                             else base.FORCEBRAIN__STATUS__C + '/' + base.FORCEBRAIN__OVERALL_APPT_STATUS_TEXT__C
  30.                         end = 'Completed' then 'Send to Committee'
  31.                     else CURRENT_STATUS__C
  32.                 end
  33.         end as Current_Status__c
  34.     from (
  35.         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,
  36.                 B1.FORCEBRAIN__FIRST_NAME__C, B1.FORCEBRAIN__IS_PROVIDER__C, B1.FORCEBRAIN__LAST_NAME__C, B1.FORCEBRAIN__LEAD__C,
  37.                 B1.FORCEBRAIN__LOCATION_SUMO__C, B1.FORCEBRAIN__LOCATIONADDRESS__C, B1.FORCEBRAIN__OVERALL_APPT_STATUS_TEXT__C, B1.FORCEBRAIN__ROOM_SUMO__C,
  38.                 B1.FORCEBRAIN__STATUS__C, B1.ID, B1.Interview_1_Date_Time__c, B1.Interview_Location__c, B1.Interview_Type__c, B1.LASTNAME, B1.LeadID,
  39.                 B1.NAME, B1.PROGRAM_C__C, B1.Ranker, B1.[FORCEBRAIN__DESCRIPTION__C]
  40.             from (
  41.                 select d.ID AS AppID, a.FORCEBRAIN__DESCRIPTION__C, d.APPLY_YEAR__C,
  42.                         d.PROGRAM_C__C, d.CURRENT_STATUS__C, d.ExtID__c, a.ID,
  43.                         b.FORCEBRAIN__APPOINTMENT_STATUS__C, b.FORCEBRAIN__LEAD__C, c.FIRSTNAME,
  44.                         c.LASTNAME, c.ID AS LeadID, a.FORCEBRAIN__LOCATION_TEXT__C AS Interview_Type__c,
  45.                         a.FORCEBRAIN__LOCATION_SUMO__C, a.FORCEBRAIN__STARTDATETIME__C AS Interview_1_Date_Time__c,
  46.                         a.FORCEBRAIN__STATUS__C, a.FORCEBRAIN__OVERALL_APPT_STATUS_TEXT__C,
  47.                         a.FORCEBRAIN__LOCATIONADDRESS__C,
  48.                         RANK () over( partition by d.ID order by convert( date, a.FORCEBRAIN__START_DATE_TIME_TEXT__C ) desc ) as Ranker,
  49.                         REPLACE( REPLACE( REPLACE( a.FORCEBRAIN__LOCATIONADDRESS__C, '<br>', '' ), ',', '' ), '  ', ' ' )
  50.                             + CASE
  51.                                 WHEN 1 < LEN( e.NAME ) THEN ' / Room: ' + e.NAME
  52.                                 ELSE ''
  53.                             END
  54.                             + CASE
  55.                                 WHEN a.FORCEBRAIN__LOCATION_TEXT__C = 'Skype' THEN ' / Skype: ' + a.[FORCEBRAIN__DESCRIPTION__C]
  56.                                 ELSE ''
  57.                             END
  58.                         AS Interview_Location__c,
  59.                         e.NAME, a.FORCEBRAIN__ROOM_SUMO__C, b.FORCEBRAIN__IS_PROVIDER__C,
  60.                         b.FORCEBRAIN__FIRST_NAME__C, b.FORCEBRAIN__LAST_NAME__C,
  61.                         a.FORCEBRAIN__EVENTTYPE__C
  62.                     from dbo.AppointmentSUMO_tbl as a
  63.                     inner join dbo.AppointmentParticipant_tbl as b ON (
  64.                         ( a.ID = b.FORCEBRAIN__EVENT__C )
  65.                     )
  66.                     inner join dbo.Lead_tbl as c ON (
  67.                         ( b.FORCEBRAIN__LEAD__C = c.ID )
  68.                     )
  69.                     inner join dbo.ADM_Applications as d ON (
  70.                         ( c.ID = d.APPLICANT_LEAD__C )
  71.                     )
  72.                     left outer join dbo.SUMORoom_tbl as e ON (
  73.                         ( a.FORCEBRAIN__ROOM_SUMO__C = e.ID )
  74.                     )
  75.                     where (
  76.                         ( d.PROGRAM_C__C IN ( 'a0vA0000001UIRBIA4', 'a0vA0000001UIRLIA4' ) )
  77.                         and (d.EXTERNAL_STATUS__C not in ( 'closed', 'complete', '', 'accepted', 'inactive', 'deposit required', 'incomplete', 'In Progress' ) )
  78.                         and (a.FORCEBRAIN__EVENTTYPE__C IN ( 'a3vA0000000kAKqIAM', 'a3vA0000000kAKvIAM', 'a3vA0000000kAL0IAM' ) )
  79.                     )
  80.         ) as B1
  81.         left outer join (
  82.             select c.ADMISSIONS_APPLICATION_ID__C, c.FEEDBACK_STATUS__C
  83.                 from dbo.Program_tbl as a
  84.                 RIGHT OUTER JOIN dbo.ADM_Applications as b ON a.ID = b.PROGRAM_C__C
  85.                 RIGHT OUTER JOIN dbo.CandidateFeedback_tbl as c ON b.ID = c.ADMISSIONS_APPLICATION_ID__C
  86.                 where (
  87.                     ( c.RECORDTYPEID = '012A00000012dSmIAI' )
  88.                     and ( b.EXTERNAL_STATUS__C not in ( 'Accepted', 'Closed', 'Complete', 'Denied', 'Inactive', 'In Progress' ) )
  89.                     and ( a.ID in ( 'a0vA0000001UIRBIA4', 'a0vA0000001UIRLIA4' ) )
  90.                 )
  91.         ) as C1 on (
  92.             ( B1.AppID = C1.ADMISSIONS_APPLICATION_ID__C )
  93.         )
  94.     )as base
  95.     left outer join (
  96.         select a.ID, c.ID AS Interviewer__c
  97.             from dbo.AppointmentSUMO_tbl as a
  98.             inner join dbo.AppointmentParticipant_tbl as b ON (
  99.                 ( a.ID = b.FORCEBRAIN__EVENT__C )
  100.             )
  101.             inner join dbo.Contacts_tbl as c ON (
  102.                 ( b.FORCEBRAIN__CUSTOMER__C collate SQL_Latin1_General_CP1_CS_AS = c.ID collate SQL_Latin1_General_CP1_CS_AS )
  103.             )
  104.             where ( '1' = b.FORCEBRAIN__IS_PROVIDER__C )
  105.             group by a.ID, c.ID
  106.     ) as Int on (
  107.         ( Base.ID = Int.ID )
  108.     )
  109.     left outer join (
  110.         select ID, Interview_1_Status__c
  111.             from dbo.ADM_Applications
  112.     ) as ADM on (
  113.         ( base.AppID = adm.ID )
  114.     )
  115.     where (
  116.         (
  117.             '1' = Ranker
  118.             and Interview_1_Status__c not in ( 'Completed', 'Complete' )
  119.             and Interview_1_Status__c != case
  120.                     when 3 < len( base.feedback_status__C ) then (
  121.                         case
  122.                             when base.feedback_status__C in ( 'Not Started', 'Started' ) then 'Completed - No Report'
  123.                             else  base.feedback_status__C
  124.                         end
  125.                     )
  126.                     else base.FORCEBRAIN__STATUS__C + '/' + base.FORCEBRAIN__OVERALL_APPT_STATUS_TEXT__C
  127.                 end
  128.         )
  129.         and datediff( month, base.Interview_1_Date_Time__c, GETDATE() )  < 4
  130.     )
  131.     order by extid__C
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top