SELECT  a.assignment_id AssignmentId,
        CONVERT(VARCHAR(20), a.asn_num) FullIdentifier,
        a.asn_num,
        CASE WHEN a.asn_num = 0 THEN NULL ELSE RIGHT(a.asn_num, 3) END SequenceNumber,
        CASE WHEN a.pnb_id = 0 THEN NULL ELSE a.pnb_id END PnbId,
        CASE WHEN a.confidential_flag = 1 THEN a.confidential_client_id ELSE a.company_id END CompanyId,
        a.office_id OfficeId,
        b.CurrencyId InvoicingCurrencyId,
        c.CompanyLocationPostalAddressId ClientCompanyLocationPostalAddressId,
        e.CompanyLocationTelephoneId,
        CASE WHEN a.unit_not_appl_code = '' THEN NULL ELSE LTRIM(RTRIM(a.unit_not_appl_code)) END AssignmentFeesNotApplicableCategoryId,
        g.AssignmentCompletionStatusId,
        NULL AssignmentPricingCategoryId,
        i.CompanyLocationPostalAddressId ClientTeamCompanyLocationPostalAddressId,
        j.AssignmentClientCategoryId,
        k.AssignmentCategoryId,
        CASE WHEN l.deletion_code <> '' THEN 1 ELSE 0 END DeletionIndicator,
        ISNULL(m.AssignmentDeletionReasonCategoryId, NULL) AssignmentDeletionReasonCategoryId,
        NULL DeletionReasonText,
        CASE WHEN a.initiated_by = '' THEN NULL ELSE n.AssignmentInitiationCategoryId END AssignmentInitiationCategoryId,
        NULL AssignmentClientContactId,
        CASE WHEN (a.confidential_flag = 1 AND a.company_id <> a.confidential_client_id) THEN o.name ELSE NULL END ConfidentialDisplayName,
        a.confidential_flag ConfidentialIndicator,
        CASE WHEN p.retainer = 0 THEN p.new_retainer ELSE p.retainer END TotalFeesAmount,
        a.kickoff_mtg_date InitialMeetingDate,
        NULL ConfirmationLetterClientSignDate,                                                  --no f-ing clue on this one atm
        a.booking_date BookingDate,
        a.closing_date ClosedDate,
        a.ent_date ReviewDate,
        CASE WHEN a.client_type_code IN (3,5) THEN 1 ELSE 0 END ExistingClientIndicator,
        a.fallout_plcmt_flag FalloutPlacementIndicator,
        l.exi_flag ExiMethodologyIndicator,
        a.no_off_limits_flag NoOffLimitsAgreementIndicator,
        a.advertised_flag PositionAdvertisedIndicator,
        CASE WHEN a.initiated_by = '' THEN 0 ELSE 1 END InvestorInstigatedIndicator,
        NULL SearchInitiatedInicator,                                                           --ruled to be redundant
        CASE WHEN q.css_assignment_id IS NULL THEN 0 ELSE 1 END PrimaryIndicator,
        CASE WHEN (a.assignment_id <> a.css_assignment_id AND a.css_assignment_id <> 0) THEN 1 ELSE 0 END ClientSurveyAnotherAssignmentIndicator,
        a.css_date_notified ClientSurveyNotifiedDate,
        a.css_how_notified_id AssignemntClientSurveyNotificationCategoryId,
        CASE WHEN LTRIM(RTRIM(a.css_notified)) = 'Y' THEN 1 ELSE 0 END ClientSurveyNotifiedIndicator,
        NULL ClientSurveyConfirmedIndicator,
        CASE WHEN a.asn_reason = '' THEN NULL ELSE r.AssignmentClientSurveyOptoutReasonCategoryId END AssignmentClientSurveyOptoutReasonCategoryId,
        CASE WHEN LTRIM(RTRIM(a.asn_css_surveyed)) IN ('V', 'Y') THEN 1 ELSE 0 END ClientSurveyIndicator,
        s.confirm_priorites_flag ClientSurveyPriorityConfirmedIndicator,
        CASE WHEN t.AssignmentClientSurveyApprovalCategoryId IS NULL    THEN u.AssignmentClientSurveyApprovalCategoryId
                                                                        ELSE t.AssignmentClientSurveyApprovalCategoryId END AssignmentClientSurveyApprovalCategoryId,
        a.css_om_approve_date ClientSurveyApprovalEffectiveDate,
        CASE WHEN a.asn_css_reason_cmt = '' THEN NULL ELSE LTRIM(RTRIM(a.asn_css_reason_cmt)) END ClientOptoutReasonDescription,
        a.css_om_approve_emp_id EmployeeId,
        v.PositionTitleCategoryId,
        NULL CountrySubdivisionId,
        NULL CountryId,
        w.CurrencyId OriginalCurrencyId,
        NULL ConversionCurrencyId,
        NULL AssignmentPositionCategoryId,
        NULL PersonBoardPositionCategoryId,
        NULL CompanyPositionLocationPostalAddressId,
        NULL PositionCity,
        NULL PositionCompetencyBehaviorText,
        NULL PositionCompetencyChallengingSituationText,
        NULL PositionCopmetencyGoalText,
        NULL PositionExperienceRequirementsText,
        LTRIM(RTRIM(a.POSITION)) PositionFullName,
        NULL PositionFullNameLocal,
        a.diversity_interest_flag PositionInterestDiversityIndicator,
        a.intl_exp_flag PositionInternationalExperienceIndicator,
        CASE WHEN a.spec_header <> '' THEN LTRIM(RTRIM(a.spec_header)) ELSE x.comment END PositionSummaryText,
        (a.base_comp + a.bonus) PositionTotalCashCompensationAmount,
        NULL PositionTotalCashCompensationConvertedAmount,
        (a.base_comp + a.bonus) PositionTotalCompensationAmount,
        NULL PositionTotalCompensationConvertedAmount,
        CASE WHEN a.vc_header <> '' THEN LTRIM(RTRIM(a.vc_header)) ELSE y.comment END InvestorCommentText,
        a.ent_oper, a.ent_date, a.chg_oper, a.chg_date, a.rec_sts
--select count(1)
FROM questnt.dbo.asn_master a WITH (nolock)                                 --100,002
JOIN q2.dbo.Currency b WITH (nolock)                                        --100,002
    ON LTRIM(RTRIM(a.fees_curr_code)) = b.Code
JOIN dm_staging.dbo.WORK_CompanyLocationPostalAddress c WITH (nolock)       --99,818
    ON a.company_id = c.CompanyId
    AND a.client_addr_key = c.addr_key
JOIN q2.dbo.TelephoneCategory d WITH (nolock)                               --99,818
    ON d.Code = 'OG'
JOIN q2.dbo.CompanyLocationTelephone e WITH (nolock)                        --81,744
    ON c.CompanyLocationPostalAddressId = e.CompanyLocationPostalAddressId
    AND e.TelephoneCategoryId = d.TelephoneCategoryId
LEFT JOIN q2.dbo.AssignmentFeesNotApplicableCategory f WITH (nolock)
    ON LTRIM(RTRIM(a.unit_not_appl_code)) = f.Code
LEFT JOIN q2.dbo.AssignmentCompletionStatusCategory g WITH (nolock)             --81,744
    ON LTRIM(RTRIM(a.status_code)) + LTRIM(RTRIM(a.completion_code)) = g.Code
LEFT JOIN questnt.dbo.peo_job_hist h WITH (nolock)
    ON a.cc_person_id = h.person_id
    AND h.current_job_flag = 1
LEFT JOIN dm_staging.dbo.WORK_CompanyLocationPostalAddress i WITH (nolock)
    ON h.company_id = i.CompanyId
    AND h.ml_cmp_addr_key = i.addr_key
LEFT JOIN q2.dbo.AssignmentClientCategory j WITH (nolock)
    ON LTRIM(RTRIM(a.client_type_code)) = j.Code
LEFT JOIN q2.dbo.AssignmentCategory k WITH (nolock)
    ON LTRIM(RTRIM(a.type_code)) = k.Code
LEFT JOIN questnt.dbo.asn_admin_info l WITH (nolock)
    ON a.assignment_id = l.assignment_id
LEFT JOIN q2.dbo.AssignmentDeletionReasonCategory m WITH (nolock)           --huh?
    ON LTRIM(RTRIM(l.deletion_code)) = m.Code
LEFT JOIN q2.dbo.AssignmentInitiationCategory n WITH (nolock)
    ON LTRIM(RTRIM(a.initiated_by)) = n.Code
LEFT JOIN questnt.dbo.cmp_master o WITH (nolock)
    ON a.company_id = o.company_id
LEFT JOIN questnt.dbo.asn_billing p WITH (nolock)
    ON a.assignment_id = p.assignment_id
LEFT JOIN (SELECT DISTINCT css_assignment_id FROM questnt.dbo.asn_master WITH (nolock)) q
    ON a.assignment_id = q.css_assignment_id
LEFT JOIN q2.dbo.AssignmentClientSurveyOptoutReasonCategory r WITH (nolock)
    ON LTRIM(RTRIM(a.asn_reason)) = r.Code
LEFT JOIN questnt.dbo.asn_acf_prs_header s WITH (nolock)
    ON a.assignment_id = s.assignment_id
LEFT JOIN q2.dbo.AssignmentClientSurveyApprovalCategory t WITH (nolock)     --table not complete
    ON LTRIM(RTRIM(a.css_approval_type)) = t.Code
LEFT JOIN q2.dbo.AssignmentClientSurveyApprovalCategory u WITH (nolock)     --table not complete
    ON u.Code = 'Not Applicable'
LEFT JOIN q2.dbo.PositionTitleCategory v WITH (nolock)
    ON CONVERT(VARCHAR(10), a.pri_title_id) = v.Code
LEFT JOIN q2.dbo.Currency w WITH (nolock)
    ON LTRIM(RTRIM(a.comp_curr_code)) = w.Code
LEFT JOIN questnt.dbo.asn_master_cmt x WITH (nolock)
    ON a.assignment_id = x.assignment_id
LEFT JOIN questnt.dbo.asn_master_vc_cmt y WITH (nolock)
    ON a.assignment_id = y.assignment_id