Advertisement
uniblab

Untitled

Oct 11th, 2019
582
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 5.77 KB | None | 0 0
  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
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement