• Replies 3 replies
  • Subscribers 962 subscribers
  • Views 57 views
  • Users 0 members are here
Related

NCOA Checking Report - before processing

Former Member
Former Member $organization

Hi-

I thought I would check and see if anybody has created a checking report between NCOA changes (the NCOA incoming table) and the address table before the changes are applied, before I go creating one.

Thanks

Jeanette Boudjouk

 

Parents
  • $organization

    I didn't create a report but did write a query that I just paste the results into excel.

     

     

    DECLARE @control_group INT

    DECLARE @const INT

     

    SET @control_group = 1

    SET @const = 39

     

    --SELECT   

    --    *

        TR_CONSTITUENCY

     

     

     

    SELECT      Distinct

          b.ncoa_session,

          e.description,

          b.customer_no,

          b.address_no,

          isnull(b.prefix,'') AS prefix,

          isnull(b.fname,'') AS First_Name,

          isnull(b.mname,'') AS Middle_Name,

          isnull(b.lname,'') AS Last_Name,

          Isnull(b.suffix,'') AS Suffix,

          isnull(b.street1,'') AS street1,

          isnull(b.street2,'') AS street2,

          isnull(b.city,'') AS city,

          isnull(b.state,'') AS state,

          isnull(b.postal_code,'') AS postal_code,

          '****************************',

          isnull(a.sadd1,'') AS New_Street1,

          isnull(a.sadd2,'') AS New_Street2,

          isnull(a.scity,'') AS New_City,

          isnull(a.sstate,'') AS New_State,

          isnull(a.szip,'') AS New_Postal_Code,

          Isnull(d.description,'No Change Made') AS result

    FROM

          TW_NCOA_INCOMING a LEFT join

          TW_NCOA_OUTGOING b ON a.ncoa_session = b.ncoa_session AND a.address_no = b.Address_no JOIN

          T_ADDRESS c ON c.address_no = a.address_no LEFT Join

          TR_NCOALINK_ACTION d ON d.id = c.ncoa_action AND c.ncoa_session = a.ncoa_session JOIN

          TR_ADDRESS_TYPE e ON e.id = c.address_type JOIN

          TX_CONST_CUST cc ON cc.Customer_No = a.customer_no

    WHERE

          a.ncoa_session = 7

          AND isnull(e.control_group,0) IN (0,@Control_Group)

          AND cc.constituency = @const

     

     

     

    Marty

     

Reply
  • $organization

    I didn't create a report but did write a query that I just paste the results into excel.

     

     

    DECLARE @control_group INT

    DECLARE @const INT

     

    SET @control_group = 1

    SET @const = 39

     

    --SELECT   

    --    *

        TR_CONSTITUENCY

     

     

     

    SELECT      Distinct

          b.ncoa_session,

          e.description,

          b.customer_no,

          b.address_no,

          isnull(b.prefix,'') AS prefix,

          isnull(b.fname,'') AS First_Name,

          isnull(b.mname,'') AS Middle_Name,

          isnull(b.lname,'') AS Last_Name,

          Isnull(b.suffix,'') AS Suffix,

          isnull(b.street1,'') AS street1,

          isnull(b.street2,'') AS street2,

          isnull(b.city,'') AS city,

          isnull(b.state,'') AS state,

          isnull(b.postal_code,'') AS postal_code,

          '****************************',

          isnull(a.sadd1,'') AS New_Street1,

          isnull(a.sadd2,'') AS New_Street2,

          isnull(a.scity,'') AS New_City,

          isnull(a.sstate,'') AS New_State,

          isnull(a.szip,'') AS New_Postal_Code,

          Isnull(d.description,'No Change Made') AS result

    FROM

          TW_NCOA_INCOMING a LEFT join

          TW_NCOA_OUTGOING b ON a.ncoa_session = b.ncoa_session AND a.address_no = b.Address_no JOIN

          T_ADDRESS c ON c.address_no = a.address_no LEFT Join

          TR_NCOALINK_ACTION d ON d.id = c.ncoa_action AND c.ncoa_session = a.ncoa_session JOIN

          TR_ADDRESS_TYPE e ON e.id = c.address_type JOIN

          TX_CONST_CUST cc ON cc.Customer_No = a.customer_no

    WHERE

          a.ncoa_session = 7

          AND isnull(e.control_group,0) IN (0,@Control_Group)

          AND cc.constituency = @const

     

     

     

    Marty

     

Children
  • Former Member
    Former Member $organization in reply to Marty Jones

    Marty-

    Thanks for sharing, this is a great starting point.

     

    Thanks

    Jeanette

  • $organization in reply to Former Member

    Jeanette,

     

    Your welcome. Just to let you know that we use constituency code so we can filter it down to only V.I.P.s. and only look at their results.

     

    Marty

     

     

    From: Tessitura Shared Reports Forum [mailto:forums-shared.reports@tessituranetwork.com] On Behalf Of Jeanette Boudjouk
    Sent: Thursday, March 04, 2010 4:07 PM
    To: Martin A. Jones
    Subject: Re: [Tessitura Shared Reports Forum] RE: NCOA Checking Report - before processing

     

    Marty-

    Thanks for sharing, this is a great starting point.

     

    Thanks

    Jeanette

    From: Marty Jones <bounce-martyjones7649@tessituranetwork.com>
    Sent: 3/3/2010 1:00:01 PM

    I didn't create a report but did write a query that I just paste the results into excel.

     

     

    DECLARE @control_group INT

    DECLARE @const INT

     

    SET @control_group = 1

    SET @const = 39

     

    --SELECT   

    --    *

        TR_CONSTITUENCY

     

     

     

    SELECT      Distinct

          b.ncoa_session,

          e.description,

          b.customer_no,

          b.address_no,

          isnull(b.prefix,'') AS prefix,

          isnull(b.fname,'') AS First_Name,

          isnull(b.mname,'') AS Middle_Name,

          isnull(b.lname,'') AS Last_Name,

          Isnull(b.suffix,'') AS Suffix,

          isnull(b.street1,'') AS street1,

          isnull(b.street2,'') AS street2,

          isnull(b.city,'') AS city,

          isnull(b.state,'') AS state,

          isnull(b.postal_code,'') AS postal_code,

          '****************************',

          isnull(a.sadd1,'') AS New_Street1,

          isnull(a.sadd2,'') AS New_Street2,

          isnull(a.scity,'') AS New_City,

          isnull(a.sstate,'') AS New_State,

          isnull(a.szip,'') AS New_Postal_Code,

          Isnull(d.description,'No Change Made') AS result

    FROM

          TW_NCOA_INCOMING a LEFT join

          TW_NCOA_OUTGOING b ON a.ncoa_session = b.ncoa_session AND a.address_no = b.Address_no JOIN

          T_ADDRESS c ON c.address_no = a.address_no LEFT Join

          TR_NCOALINK_ACTION d ON d.id = c.ncoa_action AND c.ncoa_session = a.ncoa_session JOIN

          TR_ADDRESS_TYPE e ON e.id = c.address_type JOIN

          TX_CONST_CUST cc ON cc.Customer_No = a.customer_no

    WHERE

          a.ncoa_session = 7

          AND isnull(e.control_group,0) IN (0,@Control_Group)

          AND cc.constituency = @const

     

     

     

    Marty

     




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Shared Reports Forum. You may reply to this message to post to the forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!