locked
Problem joing Contact View rec with Contract and Contract Details Views RRS feed

  • Question

  •  I have the following sql query...
    I need to join the contact filtered view with the contract view and the contract detail views to get the value of 2 fields,  billingfrequencycode - which is on the Filtered Contract view and
    new_paymenttype  - which is on the Filtered Contract Details view

    What field do I do the join on?

    I apologize, I am a newbie to this and am trying

    =========================================================

    SELECT
     CON.new_MembershipNumber   AS 'Membership ID',
     CON.FullName     AS 'Member Name',
     CON.new_SalesChannelOnlyforPrivateMembersName AS 'Bll To Corp. Name',
     STG.Value         AS 'Plan Type',
     CON.new_TotalNoofPlanMembers   AS 'Total # Plan Members',
      
     /*
     Billing Type = 'monthly' - is on the - Contract)
      Payment Type = 'Robs Amex' - On Contract Detail Form
     */
     
     CON.StatusCode

    FROM
     dbo.FilteredContact CON

    LEFT OUTER JOIN StringMap STG
                 ON           STG.attributename  = 'new_coveragetype'
                AND           STG.attributevalue = CON.new_CoverageType
                AND           STG.objecttypecode = 2
                AND           STG.langid = 1033


    /* Billing Type = 'Monthly' : Field Name: billingfrequencycode */
    LEFT OUTER JOIN  dbo.FilteredContract
         ON    


    /* Payment Type = 'Robs Amex' : Field Name : new_paymenttype */
    LEFT OUTER JOIN  dbo.FilteredContractDetail
         ON    

    WHERE

    CON.new_SalesChannelOnlyforPrivateMembers = '7434249C-76D0-E211-8FD5-00155D0A1422'
    AND
    CON.StatusCode = 1

    =========================================================

    Monday, July 15, 2013 9:09 PM