Is there a table that indicates if an address (or phone) 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)