daily pastebin goal
91%
SHARE
TWEET

Untitled

a guest Mar 8th, 2018 54 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT  a.assignment_id AssignmentId,
  2.         CONVERT(VARCHAR(20), a.asn_num) FullIdentifier,
  3.         a.asn_num,
  4.         CASE WHEN a.asn_num = 0 THEN NULL ELSE RIGHT(a.asn_num, 3) END SequenceNumber,
  5.         CASE WHEN a.pnb_id = 0 THEN NULL ELSE a.pnb_id END PnbId,
  6.         CASE WHEN a.confidential_flag = 1 THEN a.confidential_client_id ELSE a.company_id END CompanyId,
  7.         a.office_id OfficeId,
  8.         b.CurrencyId InvoicingCurrencyId,
  9.         c.CompanyLocationPostalAddressId ClientCompanyLocationPostalAddressId,
  10.         e.CompanyLocationTelephoneId,
  11.         CASE WHEN a.unit_not_appl_code = '' THEN NULL ELSE LTRIM(RTRIM(a.unit_not_appl_code)) END AssignmentFeesNotApplicableCategoryId,
  12.         g.AssignmentCompletionStatusId,
  13.         NULL AssignmentPricingCategoryId,
  14.         i.CompanyLocationPostalAddressId ClientTeamCompanyLocationPostalAddressId,
  15.         j.AssignmentClientCategoryId,
  16.         k.AssignmentCategoryId,
  17.         CASE WHEN l.deletion_code <> '' THEN 1 ELSE 0 END DeletionIndicator,
  18.         ISNULL(m.AssignmentDeletionReasonCategoryId, NULL) AssignmentDeletionReasonCategoryId,
  19.         NULL DeletionReasonText,
  20.         CASE WHEN a.initiated_by = '' THEN NULL ELSE n.AssignmentInitiationCategoryId END AssignmentInitiationCategoryId,
  21.         NULL AssignmentClientContactId,
  22.         CASE WHEN (a.confidential_flag = 1 AND a.company_id <> a.confidential_client_id) THEN o.name ELSE NULL END ConfidentialDisplayName,
  23.         a.confidential_flag ConfidentialIndicator,
  24.         CASE WHEN p.retainer = 0 THEN p.new_retainer ELSE p.retainer END TotalFeesAmount,
  25.         a.kickoff_mtg_date InitialMeetingDate,
  26.         NULL ConfirmationLetterClientSignDate,                                                  --no f-ing clue on this one atm
  27.         a.booking_date BookingDate,
  28.         a.closing_date ClosedDate,
  29.         a.ent_date ReviewDate,
  30.         CASE WHEN a.client_type_code IN (3,5) THEN 1 ELSE 0 END ExistingClientIndicator,
  31.         a.fallout_plcmt_flag FalloutPlacementIndicator,
  32.         l.exi_flag ExiMethodologyIndicator,
  33.         a.no_off_limits_flag NoOffLimitsAgreementIndicator,
  34.         a.advertised_flag PositionAdvertisedIndicator,
  35.         CASE WHEN a.initiated_by = '' THEN 0 ELSE 1 END InvestorInstigatedIndicator,
  36.         NULL SearchInitiatedInicator,                                                           --ruled to be redundant
  37.         CASE WHEN q.css_assignment_id IS NULL THEN 0 ELSE 1 END PrimaryIndicator,
  38.         CASE WHEN (a.assignment_id <> a.css_assignment_id AND a.css_assignment_id <> 0) THEN 1 ELSE 0 END ClientSurveyAnotherAssignmentIndicator,
  39.         a.css_date_notified ClientSurveyNotifiedDate,
  40.         a.css_how_notified_id AssignemntClientSurveyNotificationCategoryId,
  41.         CASE WHEN LTRIM(RTRIM(a.css_notified)) = 'Y' THEN 1 ELSE 0 END ClientSurveyNotifiedIndicator,
  42.         NULL ClientSurveyConfirmedIndicator,
  43.         CASE WHEN a.asn_reason = '' THEN NULL ELSE r.AssignmentClientSurveyOptoutReasonCategoryId END AssignmentClientSurveyOptoutReasonCategoryId,
  44.         CASE WHEN LTRIM(RTRIM(a.asn_css_surveyed)) IN ('V', 'Y') THEN 1 ELSE 0 END ClientSurveyIndicator,
  45.         s.confirm_priorites_flag ClientSurveyPriorityConfirmedIndicator,
  46.         CASE WHEN t.AssignmentClientSurveyApprovalCategoryId IS NULL    THEN u.AssignmentClientSurveyApprovalCategoryId
  47.                                                                         ELSE t.AssignmentClientSurveyApprovalCategoryId END AssignmentClientSurveyApprovalCategoryId,
  48.         a.css_om_approve_date ClientSurveyApprovalEffectiveDate,
  49.         CASE WHEN a.asn_css_reason_cmt = '' THEN NULL ELSE LTRIM(RTRIM(a.asn_css_reason_cmt)) END ClientOptoutReasonDescription,
  50.         a.css_om_approve_emp_id EmployeeId,
  51.         v.PositionTitleCategoryId,
  52.         NULL CountrySubdivisionId,
  53.         NULL CountryId,
  54.         w.CurrencyId OriginalCurrencyId,
  55.         NULL ConversionCurrencyId,
  56.         NULL AssignmentPositionCategoryId,
  57.         NULL PersonBoardPositionCategoryId,
  58.         NULL CompanyPositionLocationPostalAddressId,
  59.         NULL PositionCity,
  60.         NULL PositionCompetencyBehaviorText,
  61.         NULL PositionCompetencyChallengingSituationText,
  62.         NULL PositionCopmetencyGoalText,
  63.         NULL PositionExperienceRequirementsText,
  64.         LTRIM(RTRIM(a.POSITION)) PositionFullName,
  65.         NULL PositionFullNameLocal,
  66.         a.diversity_interest_flag PositionInterestDiversityIndicator,
  67.         a.intl_exp_flag PositionInternationalExperienceIndicator,
  68.         CASE WHEN a.spec_header <> '' THEN LTRIM(RTRIM(a.spec_header)) ELSE x.comment END PositionSummaryText,
  69.         (a.base_comp + a.bonus) PositionTotalCashCompensationAmount,
  70.         NULL PositionTotalCashCompensationConvertedAmount,
  71.         (a.base_comp + a.bonus) PositionTotalCompensationAmount,
  72.         NULL PositionTotalCompensationConvertedAmount,
  73.         CASE WHEN a.vc_header <> '' THEN LTRIM(RTRIM(a.vc_header)) ELSE y.comment END InvestorCommentText,
  74.         a.ent_oper, a.ent_date, a.chg_oper, a.chg_date, a.rec_sts
  75. --select count(1)  
  76. FROM questnt.dbo.asn_master a WITH (nolock)                                 --100,002
  77. JOIN q2.dbo.Currency b WITH (nolock)                                        --100,002
  78.     ON LTRIM(RTRIM(a.fees_curr_code)) = b.Code
  79. JOIN dm_staging.dbo.WORK_CompanyLocationPostalAddress c WITH (nolock)       --99,818
  80.     ON a.company_id = c.CompanyId
  81.     AND a.client_addr_key = c.addr_key
  82. JOIN q2.dbo.TelephoneCategory d WITH (nolock)                               --99,818
  83.     ON d.Code = 'OG'
  84. JOIN q2.dbo.CompanyLocationTelephone e WITH (nolock)                        --81,744
  85.     ON c.CompanyLocationPostalAddressId = e.CompanyLocationPostalAddressId
  86.     AND e.TelephoneCategoryId = d.TelephoneCategoryId
  87. LEFT JOIN q2.dbo.AssignmentFeesNotApplicableCategory f WITH (nolock)
  88.     ON LTRIM(RTRIM(a.unit_not_appl_code)) = f.Code
  89. LEFT JOIN q2.dbo.AssignmentCompletionStatusCategory g WITH (nolock)             --81,744
  90.     ON LTRIM(RTRIM(a.status_code)) + LTRIM(RTRIM(a.completion_code)) = g.Code
  91. LEFT JOIN questnt.dbo.peo_job_hist h WITH (nolock)
  92.     ON a.cc_person_id = h.person_id
  93.     AND h.current_job_flag = 1
  94. LEFT JOIN dm_staging.dbo.WORK_CompanyLocationPostalAddress i WITH (nolock)
  95.     ON h.company_id = i.CompanyId
  96.     AND h.ml_cmp_addr_key = i.addr_key
  97. LEFT JOIN q2.dbo.AssignmentClientCategory j WITH (nolock)
  98.     ON LTRIM(RTRIM(a.client_type_code)) = j.Code
  99. LEFT JOIN q2.dbo.AssignmentCategory k WITH (nolock)
  100.     ON LTRIM(RTRIM(a.type_code)) = k.Code
  101. LEFT JOIN questnt.dbo.asn_admin_info l WITH (nolock)
  102.     ON a.assignment_id = l.assignment_id
  103. LEFT JOIN q2.dbo.AssignmentDeletionReasonCategory m WITH (nolock)           --huh?
  104.     ON LTRIM(RTRIM(l.deletion_code)) = m.Code
  105. LEFT JOIN q2.dbo.AssignmentInitiationCategory n WITH (nolock)
  106.     ON LTRIM(RTRIM(a.initiated_by)) = n.Code
  107. LEFT JOIN questnt.dbo.cmp_master o WITH (nolock)
  108.     ON a.company_id = o.company_id
  109. LEFT JOIN questnt.dbo.asn_billing p WITH (nolock)
  110.     ON a.assignment_id = p.assignment_id
  111. LEFT JOIN (SELECT DISTINCT css_assignment_id FROM questnt.dbo.asn_master WITH (nolock)) q
  112.     ON a.assignment_id = q.css_assignment_id
  113. LEFT JOIN q2.dbo.AssignmentClientSurveyOptoutReasonCategory r WITH (nolock)
  114.     ON LTRIM(RTRIM(a.asn_reason)) = r.Code
  115. LEFT JOIN questnt.dbo.asn_acf_prs_header s WITH (nolock)
  116.     ON a.assignment_id = s.assignment_id
  117. LEFT JOIN q2.dbo.AssignmentClientSurveyApprovalCategory t WITH (nolock)     --table not complete
  118.     ON LTRIM(RTRIM(a.css_approval_type)) = t.Code
  119. LEFT JOIN q2.dbo.AssignmentClientSurveyApprovalCategory u WITH (nolock)     --table not complete
  120.     ON u.Code = 'Not Applicable'
  121. LEFT JOIN q2.dbo.PositionTitleCategory v WITH (nolock)
  122.     ON CONVERT(VARCHAR(10), a.pri_title_id) = v.Code
  123. LEFT JOIN q2.dbo.Currency w WITH (nolock)
  124.     ON LTRIM(RTRIM(a.comp_curr_code)) = w.Code
  125. LEFT JOIN questnt.dbo.asn_master_cmt x WITH (nolock)
  126.     ON a.assignment_id = x.assignment_id
  127. LEFT JOIN questnt.dbo.asn_master_vc_cmt y WITH (nolock)
  128.     ON a.assignment_id = y.assignment_id
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