• Replies 2 replies
  • Subscribers 962 subscribers
  • Views 111 views
  • Users 0 members are here
Related

SQL JOIN from [T_SUB_LINEITEM] to postode

Matt Gumbley
$organization

Hi,

I am trying to write a query that will join the rows of T_SUB_LINEITEM to a postcode. I want a table that I can import into Power Bi for a performance with all the associated SLI with a performance (done that via perf_no).

I have gone through T_ORDER / T_CUSTOMER / T_ADDRESS but I get back a significant amount of NULLS as the customer numbers are associated with a merged account.

Any suggestions would be great,

Matt 

  • $organization

    Hi, Matt:

     If you limit the rows you return from T_CUSTOMER to those where inactive = 1, that should weed out the merged and inactive patrons.

     Depending on how your query is written, you could still get multiple rows for each SLI per address match. Instead of joining T_ADDRESS, you can use the FT_GET_ADDRESS function so that you’re pulling the best address for your purpose. It uses five parameters: @mail_dt, @purpose, @label, @customer_no, and @allow_multiple_contact_points.

     Here’s how I use this function in list criteria to get people out of the country as of a particular date:

    Select Distinct a.customer_no
    From V_CUSTOMER_WITH_PRIMARY_GROUP a (Nolock)
    cross apply FT_GET_ADDRESS('12/15/2019',null,'Y',a.customer_no,0) b
    JOIN vs_address e (Nolock) ON e.customer_no = a.customer_no
    Where  IsNull(a.inactive, 1) = 1
     AND b.address_no = e.address_no
     AND e.geo_area in (4,0) --check your settings geo_area settings for this
    AND e.inactive = 'N' 

    Lucie

  • $organization
    Hi, Matt:
     
    If you limit the rows you return from T_CUSTOMER to those where inactive = 1, that should weed out the merged and inactive patrons.
     
    Depending on how your query is written, you could still get multiple rows for each SLI per address match. Instead of joining T_ADDRESS, you can use the FT_GET_ADDRESS function so that you’re pulling the best address for your purpose. It uses five parameters: @mail_dt, @purpose, @label, @customer_no, and @allow_multiple_contact_points.
     
    Here’s how I use this function in list criteria to get people out of the country as of a particular date:
     
    Select Distinct a.customer_no
    From V_CUSTOMER_WITH_PRIMARY_GROUP a (Nolock)
    cross apply FT_GET_ADDRESS('12/15/2019',null,'Y',a.customer_no,0) b
    JOIN vs_address e (Nolock) ON e.customer_no = a.customer_no
    Where  IsNull(a.inactive, 1) = 1
     AND b.address_no = e.address_no
     AND e.geo_area in (4,0) --check your settings geo_area settings for this
    AND e.inactive = 'N'
     
    Lucie
     

    Lucie Spieler
    IT Dev. & Training Manager
    Direct: 305.403.3291 | Box Office: 800.741.1010
    lspieler@fgo.org | www.FGO.org

    FLORIDA GRAND OPERA | 79th SEASON
    8390 NW 25th Street | Miami, FL 33122