API tools faq
paste
Advertisement
uniblab

Untitled

uniblab
Oct 11th, 2019
453
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 7.47 KB | None | 0 0
raw download clone embed print report
  1. Select distinct
  2.  
  3. base.AppID as Id,
  4. base.ExtID__c,
  5.  
  6. case when len(base.feedback_status__C) > 3 then
  7. (case when base.feedback_status__C in ('Not Started', 'Started') then 'Completed – No Report' else  base.feedback_status__C end)
  8. else base.FORCEBRAIN__STATUS__C+'/'+base.FORCEBRAIN__OVERALL_APPT_STATUS_TEXT__C end as Interview_1_Status__c,
  9.  
  10.  
  11. base.Interview_Type__c,
  12. Cast( Left( dbo.TrimToNull( base.Interview_Location__c ), 200 ) as varchar( 200 ) ) as Interview_Location__c,
  13. Int.Interviewer__c,
  14. --cast(base.Interview_1_Date_Time__c as datetime) as Interview_1_Date_Time__c,
  15.  
  16. --CONVERT(datetime,SWITCHOFFSET(CONVERT(datetimeoffset,cast(base.Interview_1_Date_Time__c as datetime)),'-05:00')) Interview_1_Date_Time__c
  17. base.Interview_1_Date_Time__c as Interview_1_Date_Time__c,
  18.  
  19.  
  20. case when base.CURRENT_STATUS__C IN ('Denied', 'Deferred', 'Accepted', 'Deposit Required', 'Offer', 'Accepted Offer', 'Withdraw',
  21. 'Not Entering', 'Contingent Offer', 'Denied from Waitlist', 'WaitList', 'Enrollment Form Required', 'Waiting on Contingency',
  22. 'Send to Committee') then base.CURRENT_STATUS__C else
  23.  
  24. (case when
  25. (case when len(base.feedback_status__C) > 3 then
  26. (case when base.feedback_status__C in ('Not Started', 'Started') then 'Completed - No Report' else  base.feedback_status__C end)
  27. else base.FORCEBRAIN__STATUS__C+'/'+base.FORCEBRAIN__OVERALL_APPT_STATUS_TEXT__C end) = 'Completed' then
  28. 'Send to Committee' else CURRENT_STATUS__C end) end as Current_Status__c
  29.  
  30.  
  31.  
  32.  
  33.  
  34.  
  35. --Interview_1_Status__c as SFIS,
  36. --base.CURRENT_STATUS__C as SFCS
  37.  
  38. from
  39.  
  40. (Select
  41.  
  42. C1.FEEDBACK_STATUS__C,
  43. B1.AppID,
  44. B1.APPLY_YEAR__C,
  45. B1.CURRENT_STATUS__C,
  46. B1.ExtID__c,
  47. B1.FIRSTNAME,
  48. B1.FORCEBRAIN__APPOINTMENT_STATUS__C,
  49. B1.FORCEBRAIN__FIRST_NAME__C,
  50. B1.FORCEBRAIN__IS_PROVIDER__C,
  51. B1.FORCEBRAIN__LAST_NAME__C,
  52. B1.FORCEBRAIN__LEAD__C,
  53. B1.FORCEBRAIN__LOCATION_SUMO__C,
  54. B1.FORCEBRAIN__LOCATIONADDRESS__C,
  55. B1.FORCEBRAIN__OVERALL_APPT_STATUS_TEXT__C,
  56. B1.FORCEBRAIN__ROOM_SUMO__C,
  57. B1.FORCEBRAIN__STATUS__C,
  58. B1.ID,
  59. B1.Interview_1_Date_Time__c,
  60. B1.Interview_Location__c,
  61. B1.Interview_Type__c,
  62. B1.LASTNAME,
  63. B1.LeadID,
  64. B1.NAME,
  65. B1.PROGRAM_C__C,
  66. B1.Ranker,
  67. B1.[FORCEBRAIN__DESCRIPTION__C]
  68.  
  69. from
  70.  
  71. (SELECT        dbo.ADM_Applications.ID AS AppID, dbo.AppointmentSUMO_tbl.FORCEBRAIN__DESCRIPTION__C, dbo.ADM_Applications.APPLY_YEAR__C,
  72.                          dbo.ADM_Applications.PROGRAM_C__C, dbo.ADM_Applications.CURRENT_STATUS__C, dbo.ADM_Applications.ExtID__c, dbo.AppointmentSUMO_tbl.ID,
  73.                          dbo.AppointmentParticipant_tbl.FORCEBRAIN__APPOINTMENT_STATUS__C, dbo.AppointmentParticipant_tbl.FORCEBRAIN__LEAD__C, dbo.Lead_tbl.FIRSTNAME,
  74.                          dbo.Lead_tbl.LASTNAME, dbo.Lead_tbl.ID AS LeadID, dbo.AppointmentSUMO_tbl.FORCEBRAIN__LOCATION_TEXT__C AS Interview_Type__c,
  75.                          dbo.AppointmentSUMO_tbl.FORCEBRAIN__LOCATION_SUMO__C, dbo.AppointmentSUMO_tbl.FORCEBRAIN__STARTDATETIME__C AS Interview_1_Date_Time__c,
  76.                          dbo.AppointmentSUMO_tbl.FORCEBRAIN__STATUS__C, dbo.AppointmentSUMO_tbl.FORCEBRAIN__OVERALL_APPT_STATUS_TEXT__C,
  77.                          dbo.AppointmentSUMO_tbl.FORCEBRAIN__LOCATIONADDRESS__C,
  78.  
  79.  
  80.                          
  81.                        
  82.                           RANK () over(partition by dbo.ADM_Applications.ID order by    convert(date,dbo.AppointmentSUMO_tbl.FORCEBRAIN__START_DATE_TIME_TEXT__C) desc) as Ranker,
  83.                          
  84.  
  85.  
  86.                          REPLACE(REPLACE(REPLACE(dbo.AppointmentSUMO_tbl.FORCEBRAIN__LOCATIONADDRESS__C, '<br>', ''), ',', ''), '  ', ' ')
  87.                          + CASE WHEN LEN(dbo.SUMORoom_tbl.NAME)
  88.                          > 1 THEN ' / Room: ' + dbo.SUMORoom_tbl.NAME ELSE '' END + CASE WHEN dbo.AppointmentSUMO_tbl.FORCEBRAIN__LOCATION_TEXT__C = 'Skype' THEN ' / Skype: '
  89.                           + dbo.AppointmentSUMO_tbl.[FORCEBRAIN__DESCRIPTION__C] ELSE '' END AS Interview_Location__c, dbo.SUMORoom_tbl.NAME,
  90.                          dbo.AppointmentSUMO_tbl.FORCEBRAIN__ROOM_SUMO__C, dbo.AppointmentParticipant_tbl.FORCEBRAIN__IS_PROVIDER__C,
  91.                          dbo.AppointmentParticipant_tbl.FORCEBRAIN__FIRST_NAME__C, dbo.AppointmentParticipant_tbl.FORCEBRAIN__LAST_NAME__C,
  92.                          dbo.AppointmentSUMO_tbl.FORCEBRAIN__EVENTTYPE__C
  93. FROM            dbo.AppointmentSUMO_tbl INNER JOIN
  94.                          dbo.AppointmentParticipant_tbl ON dbo.AppointmentSUMO_tbl.ID = dbo.AppointmentParticipant_tbl.FORCEBRAIN__EVENT__C INNER JOIN
  95.                          dbo.Lead_tbl ON dbo.AppointmentParticipant_tbl.FORCEBRAIN__LEAD__C = dbo.Lead_tbl.ID INNER JOIN
  96.                          dbo.ADM_Applications ON dbo.Lead_tbl.ID = dbo.ADM_Applications.APPLICANT_LEAD__C LEFT OUTER JOIN
  97.                          dbo.SUMORoom_tbl ON dbo.AppointmentSUMO_tbl.FORCEBRAIN__ROOM_SUMO__C = dbo.SUMORoom_tbl.ID
  98. WHERE        (dbo.ADM_Applications.PROGRAM_C__C IN ('a0vA0000001UIRBIA4', 'a0vA0000001UIRLIA4')) AND (NOT (dbo.ADM_Applications.EXTERNAL_STATUS__C IN ('closed',
  99.                          'complete', '', 'accepted', 'inactive', 'deposit required', 'incomplete', 'In Progress'))) AND
  100.                          (dbo.AppointmentSUMO_tbl.FORCEBRAIN__EVENTTYPE__C IN ('a3vA0000000kAKqIAM', 'a3vA0000000kAKvIAM', 'a3vA0000000kAL0IAM'))) as B1
  101.  
  102.  
  103. left outer join
  104.  
  105. (SELECT        TOP (100) PERCENT dbo.CandidateFeedback_tbl.ADMISSIONS_APPLICATION_ID__C, dbo.CandidateFeedback_tbl.FEEDBACK_STATUS__C
  106. FROM            dbo.Program_tbl RIGHT OUTER JOIN
  107.                          dbo.ADM_Applications ON dbo.Program_tbl.ID = dbo.ADM_Applications.PROGRAM_C__C RIGHT OUTER JOIN
  108.                          dbo.CandidateFeedback_tbl ON dbo.ADM_Applications.ID = dbo.CandidateFeedback_tbl.ADMISSIONS_APPLICATION_ID__C
  109. WHERE        (dbo.CandidateFeedback_tbl.RECORDTYPEID = '012A00000012dSmIAI') AND (NOT (dbo.ADM_Applications.EXTERNAL_STATUS__C IN ('Accepted', 'Closed', 'Complete', 'Denied', 'Inactive', 'In Progress'))) AND
  110.                          (dbo.Program_tbl.ID IN ('a0vA0000001UIRBIA4', 'a0vA0000001UIRLIA4'))) as C1
  111.  
  112. on B1.AppID = C1.ADMISSIONS_APPLICATION_ID__C
  113.  
  114. ) as base
  115.                          
  116.                          
  117.                          left outer join
  118.                          
  119.                          
  120.                          (SELECT        TOP (100) PERCENT dbo.AppointmentSUMO_tbl.ID, dbo.Contacts_tbl.ID AS Interviewer__c
  121. FROM            dbo.AppointmentSUMO_tbl INNER JOIN
  122.                          dbo.AppointmentParticipant_tbl ON dbo.AppointmentSUMO_tbl.ID = dbo.AppointmentParticipant_tbl.FORCEBRAIN__EVENT__C INNER JOIN
  123.                          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
  124. WHERE        (dbo.AppointmentParticipant_tbl.FORCEBRAIN__IS_PROVIDER__C = '1')
  125. GROUP BY dbo.AppointmentSUMO_tbl.ID, dbo.Contacts_tbl.ID
  126. ORDER BY dbo.AppointmentSUMO_tbl.ID) as Int
  127.  
  128. on Base.ID = Int.ID
  129.  
  130.  
  131. left outer join
  132.  
  133. (SELECT        ID, Interview_1_Status__c
  134. FROM            dbo.ADM_Applications) as ADM
  135.  
  136. on base.AppID = adm.ID
  137.  
  138.  
  139. where
  140.  
  141. (Ranker = '1' and Interview_1_Status__c not in ('Completed', 'Complete')
  142.  
  143. and
  144.  
  145. case when len(base.feedback_status__C) > 3 then
  146. (case when base.feedback_status__C in ('Not Started', 'Started') then 'Completed - No Report' else  base.feedback_status__C end)
  147. else base.FORCEBRAIN__STATUS__C+'/'+base.FORCEBRAIN__OVERALL_APPT_STATUS_TEXT__C end <> Interview_1_Status__c)
  148.  
  149.  
  150.  
  151. and datediff (month,base.Interview_1_Date_Time__c, GETDATE())  < 4
  152.  
  153. order by extid__C
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!