SQL JOIN from [T_SUB_LINEITEM] to postode

Matt Gumbley $organization over 6 years ago

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

Parents

Lucie Spieler $organization over 6 years ago

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

Reply