Table that shows an address is inherited

Gloria,

I don't think there is a table that spells this out explicitly.  But you could extrapolate something into a view. By looking for customer numbers in T_ADDRESS that don't have any addresses marked as primary.

The other place I would suggest looking for a bit of head start on it is the Table-valued Function FT_GET_PRIMARY_ADDRESS.  It handles returning a primary address for any constituent, even if it is inherited. 

Sorry if this is a restatement of the obvious.  The business rule is pretty simple:  Only primary addresses can be inherited, always from the household to a primary affiliate.  A primary affiliate’s address cannot be used to inherit to another affiliate or the household itself.

Bearing that in mind, it’s simply a matter of finding where the primary address’s owner (customer_no) doesn’t match the current customer_no.  Something like this:

Select c.lname, c.fname, a.*

From dbo.FT_GET_PRIMARY_ADDRESS() a

       JOIN dbo.T_CUSTOMER c on a.customer_no = c.customer_no

Where Not Exists (Select * From T_ADDRESS x Where x.address_no = a.address_no and x.customer_no = a.customer_no)

Sorry if this is a restatement of the obvious.  The business rule is pretty simple:  Only primary addresses can be inherited, always from the household to a primary affiliate.  A primary affiliate’s address cannot be used to inherit to another affiliate or the household itself.

Bearing that in mind, it’s simply a matter of finding where the primary address’s owner (customer_no) doesn’t match the current customer_no.  Something like this:

Select c.lname, c.fname, a.*

From dbo.FT_GET_PRIMARY_ADDRESS() a

       JOIN dbo.T_CUSTOMER c on a.customer_no = c.customer_no

Where Not Exists (Select * From T_ADDRESS x Where x.address_no = a.address_no and x.customer_no = a.customer_no)