API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Apr 1st, 2015
212
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 11.05 KB | None | 0 0
raw download clone embed print report
  1. SELECT CASE WHEN dbo.vw_avoice_candidate_NO_contacts.candidate_id IS NULL THEN 'Yes' ELSE 'No' END AS ever_contacted,
  2. dbo.vw_avoice_last_contacted.contact_dte, dbo.vw_avoice_last_contacted.lastupdateddate AS last_contact_date,
  3. CASE WHEN dbo.vw_avoice_last_signed_card.signed_card IS NULL THEN 'No' ELSE dbo.vw_avoice_last_signed_card.signed_card END AS signed_card,
  4. CASE WHEN dbo.vw_avoice_last_signed_card.sign_card_dte = '19000101' THEN NULL
  5. ELSE dbo.vw_avoice_last_signed_card.sign_card_dte END AS sign_card_dte, dbo.avoice_candidate.candidate_id, dbo.avoice_candidate.campaign_id,
  6. dbo.avoice_candidate.location_id, dbo.avoice_candidate.ssn, dbo.avoice_candidate.lst_nme, dbo.avoice_candidate.frst_nme, dbo.avoice_candidate.avc_init,
  7. dbo.avoice_candidate.name_suffix, CASE WHEN len(avc_init) > 0 AND NOT avc_init IS NULL THEN LTRIM(isnull(name_suffix, '')
  8. + ' ' + frst_nme + ' ' + avc_init + ' ' + lst_nme) ELSE LTRIM(isnull(name_suffix, '') + ' ' + frst_nme + ' ' + isnull(lst_nme, '')) END AS Full_Nme,
  9. dbo.avoice_candidate.addr1, dbo.avoice_candidate.addr2, CASE WHEN avoice_candidate.addr2 IS NULL OR
  10. avoice_candidate.addr2 = ' ' OR
  11. avoice_candidate.addr2 = '' THEN avoice_candidate.city + ', ' + avoice_candidate.state + ' ' + CASE WHEN avoice_candidate.zip_cde IS NULL
  12. THEN ' ' ELSE avoice_candidate.zip_cde END ELSE avoice_candidate.addr2 END AS disp_addr2, CASE WHEN avoice_candidate.addr2 IS NULL OR
  13. avoice_candidate.addr2 = ' ' OR
  14. avoice_candidate.addr2 = '' THEN ' ' ELSE avoice_candidate.city + ', ' + avoice_candidate.state + ' ' + CASE WHEN avoice_candidate.zip_cde IS NULL
  15. THEN ' ' ELSE avoice_candidate.zip_cde END END AS disp_csz, CASE WHEN dbo.avoice_candidate.city IS NULL
  16. THEN 'Unknown' WHEN LEN(RTRIM(dbo.avoice_candidate.city)) < 1 THEN 'Unknown' ELSE dbo.avoice_candidate.city END AS city, dbo.avoice_candidate.state,
  17. CASE WHEN avoice_candidate.zip_cde IS NULL THEN ' ' ELSE avoice_candidate.zip_cde END AS zip_cde, dbo.avoice_candidate.home_addr1,
  18. CASE WHEN home_addr2 IS NULL OR
  19. home_addr2 = ' ' OR
  20. home_addr2 = '' THEN avoice_candidate.city + ', ' + avoice_candidate.state + ' ' + CASE WHEN avoice_candidate.zip_cde IS NULL
  21. THEN ' ' ELSE avoice_candidate.zip_cde END ELSE home_addr2 END AS home_disp_addr2, CASE WHEN home_addr2 IS NULL OR
  22. home_addr2 = ' ' OR
  23. home_addr2 = '' THEN ' ' ELSE avoice_candidate.city + ', ' + avoice_candidate.state + ' ' + CASE WHEN avoice_candidate.zip_cde IS NULL
  24. THEN ' ' ELSE avoice_candidate.zip_cde END END AS home_disp_csz, dbo.avoice_candidate.home_city, dbo.avoice_candidate.home_state,
  25. dbo.avoice_candidate.home_zip_cde, dbo.avoice_candidate.misc_flag1, dbo.avoice_candidate.misc_flag2, dbo.avoice_candidate.misc_number1,
  26. dbo.avoice_candidate.misc_number2, CASE WHEN dbo.avoice_candidate.misc_date1 = '19000101' THEN NULL
  27. ELSE dbo.avoice_candidate.misc_date1 END AS misc_date1, CASE WHEN dbo.avoice_candidate.misc_date2 = '19000101' THEN NULL
  28. ELSE dbo.avoice_candidate.misc_date2 END AS misc_date2, dbo.avoice_candidate.note, dbo.avoice_candidate.txt_msg_flg, dbo.avoice_candidate.latitude,
  29. dbo.avoice_candidate.longitude, dbo.avoice_candidate.zoom, dbo.avoice_candidate.iconfile, dbo.avoice_candidate.county, dbo.avoice_candidate.ctry_cde,
  30. dbo.avoice_candidate.home_phone,
  31. CASE WHEN dbo.avoice_candidate.home_phone = '(___) ___-____' THEN 'No' WHEN dbo.avoice_candidate.home_phone = '' THEN 'No' WHEN dbo.avoice_candidate.home_phone
  32. IS NULL THEN 'No' ELSE 'Yes' END AS phone,
  33. CASE WHEN dbo.avoice_candidate.cell_phone = '(___) ___-____' THEN 'No' WHEN dbo.avoice_candidate.cell_phone = '' THEN 'No' WHEN dbo.avoice_candidate.cell_phone
  34. IS NULL THEN 'No' ELSE 'Yes' END AS cellphone, CASE WHEN dbo.avoice_candidate.work_phone > ' ' THEN 'Yes' ELSE 'No' END AS workphone,
  35. dbo.avoice_candidate.cell_phone, dbo.avoice_candidate.work_phone, dbo.avoice_candidate.email_addr,
  36. CASE WHEN dbo.avoice_candidate.email_addr > ' ' THEN 'Yes' ELSE 'No' END AS email, dbo.avoice_candidate.fax,
  37. CASE WHEN dbo.avoice_candidate.birth_dte = '19000101' THEN NULL ELSE dbo.avoice_candidate.birth_dte END AS birth_dte,
  38. CASE WHEN dbo.avoice_candidate.sex = 'M' THEN 'Male' WHEN dbo.avoice_candidate.sex = 'F' THEN 'Female' ELSE 'Unknown' END AS sex,
  39. CASE WHEN dbo.avoice_candidate.marital_sts = 'M' THEN 'Married' WHEN dbo.avoice_candidate.marital_sts = 'S' THEN 'Single' WHEN dbo.avoice_candidate.marital_sts
  40. = 'D' THEN 'Divorced' WHEN dbo.avoice_candidate.marital_sts = 'W' THEN 'Widowed' ELSE 'Unknown' END AS marital_sts, dbo.avoice_candidate.chalenge_vote,
  41. dbo.avoice_candidate.chal_vte_reas,
  42. CASE WHEN dbo.avoice_candidate.bad_addr_flg = 0 THEN 'No' WHEN dbo.avoice_candidate.bad_addr_flg = 1 THEN 'Yes' ELSE 'No' END AS bad_addr_flg,
  43. CASE WHEN dbo.avoice_candidate.misc_data1 > ' ' THEN dbo.avoice_candidate.misc_data1 ELSE 'None' END AS misc_data1,
  44. CASE WHEN dbo.avoice_candidate.misc_data2 > ' ' THEN dbo.avoice_candidate.misc_data2 ELSE 'None' END AS misc_data2,
  45. dbo.avoice_candidate.lastupdateduser, dbo.avoice_candidate.lastupdateddate, dbo.avoice_candidate.moduleid, dbo.avoice_candidate.roleid,
  46. dbo.avoice_campaign.campaign_name, dbo.avoice_candidate.work_ext,
  47. CASE WHEN dbo.avoice_candidate.full_time = 'Y' THEN 'Yes' WHEN dbo.avoice_candidate.full_time = 'N' THEN 'No' ELSE 'Unknown' END AS full_time,
  48. dbo.avoice_candidate.hours_worked, dbo.avoice_candidate.supervisor, dbo.avoice_candidate.pay_rate,
  49. CASE WHEN dbo.avoice_candidate.hire_dte = '19000101' THEN NULL ELSE dbo.avoice_candidate.hire_dte END AS hire_dte, dbo.avoice_candidate.term_reason,
  50. CASE WHEN dbo.avoice_candidate.seasonal = 'N' THEN 'No' WHEN dbo.avoice_candidate.seasonal = 'Y' THEN 'Yes' ELSE '' END AS seasonal,
  51. dbo.avoice_candidate.work_email, dbo.avoice_location.location_name, CASE WHEN avoice_vw_TableDetail_1.Description IS NULL
  52. THEN 'Unknown' ELSE avoice_vw_TableDetail_1.Description END AS DescDept, CASE WHEN avoice_vw_TableDetail_2.Description IS NULL
  53. THEN 'Unknown' ELSE avoice_vw_TableDetail_2.Description END AS DescStatus, CASE WHEN avoice_vw_TableDetail_3.Description IS NULL
  54. THEN 'Unknown' ELSE avoice_vw_TableDetail_3.Description END AS DescShift, CASE WHEN avoice_vw_TableDetail_4.Description IS NULL
  55. THEN 'Unknown' ELSE avoice_vw_TableDetail_4.Description END AS DescJob, CASE WHEN avoice_vw_TableDetail_5.Description IS NULL
  56. THEN 'Unknown' ELSE avoice_vw_TableDetail_5.Description END AS DescRace, CASE WHEN dbo.avoice_vw_TableDetail.Description IS NULL
  57. THEN 'Unknown' ELSE dbo.avoice_vw_TableDetail.Description END AS DescLanguage, CASE WHEN avoice_vw_TableDetail_6.Description IS NULL
  58. THEN 'Not Contacted' ELSE avoice_vw_TableDetail_6.Description END AS DescContact, CASE WHEN avoice_vw_TableDetail_7.Description IS NULL
  59. THEN 'None' ELSE avoice_vw_TableDetail_7.Description END AS DescEval, dbo.avoice_candidate.native_lang,
  60. CASE WHEN dbo.avoice_candidate.english_speak = 'N' THEN 'No' WHEN dbo.avoice_candidate.english_speak = 'Y' THEN 'Yes' ELSE '' END AS english_speak,
  61. dbo.avoice_candidate.sex AS Expr2, dbo.avoice_candidate.marital_sts AS Expr3, dbo.avoice_candidate.seasonal AS Expr4,
  62. CASE WHEN dbo.avoice_candidate.term_dte = '19000101' THEN NULL ELSE dbo.avoice_candidate.term_dte END AS term_dte, dbo.avoice_candidate.user_added,
  63. dbo.avoice_candidate.date_added, dbo.avoice_candidate.home_addr2, dbo.avoice_candidate.misc_flag3, dbo.avoice_candidate.misc_flag4
  64. FROM dbo.avoice_vw_TableDetail AS avoice_vw_TableDetail_7 RIGHT OUTER JOIN
  65. dbo.vw_avoice_last_contacted ON avoice_vw_TableDetail_7.Id = dbo.vw_avoice_last_contacted.evaluation RIGHT OUTER JOIN
  66. dbo.vw_avoice_last_signed_card RIGHT OUTER JOIN
  67. dbo.avoice_candidate INNER JOIN
  68. dbo.avoice_location ON dbo.avoice_candidate.location_id = dbo.avoice_location.location_id INNER JOIN
  69. dbo.avoice_campaign ON dbo.avoice_candidate.campaign_id = dbo.avoice_campaign.campaign_id ON
  70. dbo.vw_avoice_last_signed_card.candidate_id = dbo.avoice_candidate.candidate_id LEFT OUTER JOIN
  71. dbo.vw_avoice_last_contact_type LEFT OUTER JOIN
  72. dbo.avoice_vw_TableDetail AS avoice_vw_TableDetail_6 ON dbo.vw_avoice_last_contact_type.contact_type = avoice_vw_TableDetail_6.Id ON
  73. dbo.avoice_candidate.candidate_id = dbo.vw_avoice_last_contact_type.candidate_id ON
  74. dbo.vw_avoice_last_contacted.candidate_id = dbo.avoice_candidate.candidate_id LEFT OUTER JOIN
  75. dbo.vw_avoice_candidate_NO_contacts ON dbo.avoice_candidate.candidate_id = dbo.vw_avoice_candidate_NO_contacts.candidate_id LEFT OUTER JOIN
  76. dbo.avoice_vw_TableDetail AS avoice_vw_TableDetail_5 ON dbo.avoice_candidate.race = avoice_vw_TableDetail_5.Id LEFT OUTER JOIN
  77. dbo.avoice_vw_TableDetail ON dbo.avoice_candidate.native_lang = dbo.avoice_vw_TableDetail.Id LEFT OUTER JOIN
  78. dbo.avoice_vw_TableDetail AS avoice_vw_TableDetail_4 ON dbo.avoice_candidate.tbl_dtl_job_id = avoice_vw_TableDetail_4.Id LEFT OUTER JOIN
  79. dbo.avoice_vw_TableDetail AS avoice_vw_TableDetail_2 ON dbo.avoice_candidate.status = avoice_vw_TableDetail_2.Id LEFT OUTER JOIN
  80. dbo.avoice_vw_TableDetail AS avoice_vw_TableDetail_3 ON dbo.avoice_candidate.work_shift = avoice_vw_TableDetail_3.Id LEFT OUTER JOIN
  81. dbo.avoice_vw_TableDetail AS avoice_vw_TableDetail_1 ON dbo.avoice_candidate.tbl_dtl_dept_id = avoice_vw_TableDetail_1.Id
  82. WHERE (CASE WHEN dbo.avoice_candidate.term_dte = '19000101' THEN NULL ELSE dbo.avoice_candidate.term_dte END IS NULL)
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!