Head of Household SQL Logic

Dan Kirk · February 2017

Greetings all. I'm new to RE and working to decipher the database structure for performing some SQL queries. Can someone walk me through the logic of Head of Household? After reviewing the table structure and reading through some Blackbaud help docs, this is what I've come up with as the logic I will use to work on a query.

1. If the constituent individual has no spouse relationship, then they are the HoH by default.

2. If the constituent has a spouse relationship, then one of those two relationship records will have the HoH indicator field set to -1.

What am I missing? My goal at the moment is to be write a query that returns one row per household.

Thanks.

James Andrews · February 2017

Dan Kirk:

Greetings all. I'm new to RE and working to decipher the database structure for performing some SQL queries. Can someone walk me through the logic of Head of Household? After reviewing the table structure and reading through some Blackbaud help docs, this is what I've come up with as the logic I will use to work on a query.

1. If the constituent individual has no spouse relationship, then they are the HoH by default.

2. If the constituent has a spouse relationship, then one of those two relationship records will have the HoH indicator field set to -1.

What am I missing? My goal at the moment is to be write a query that returns one row per household.

Thanks.

I think that's pretty much all there is to it.

John Heizer · February 2017

Dan Kirk:

Greetings all. I'm new to RE and working to decipher the database structure for performing some SQL queries. Can someone walk me through the logic of Head of Household? After reviewing the table structure and reading through some Blackbaud help docs, this is what I've come up with as the logic I will use to work on a query.

1. If the constituent individual has no spouse relationship, then they are the HoH by default.

2. If the constituent has a spouse relationship, then one of those two relationship records will have the HoH indicator field set to -1.

What am I missing? My goal at the moment is to be write a query that returns one row per household.

Thanks.

One caution, whether someone has a Spouse Relationship gets tracked in two different places that can sometimes get out of sync.

RECORDS.SPOUSE_ID points to the spouse in RECORDS.ID

then there's CONSTIT_RELATIONSHIPS.IS_SPOUSE (which can accidentally get more than one Relationship record flagged as a Spouse).

Dan Kirk · February 2017

John Heizer:

Dan Kirk:

Greetings all. I'm new to RE and working to decipher the database structure for performing some SQL queries. Can someone walk me through the logic of Head of Household? After reviewing the table structure and reading through some Blackbaud help docs, this is what I've come up with as the logic I will use to work on a query.

1. If the constituent individual has no spouse relationship, then they are the HoH by default.

2. If the constituent has a spouse relationship, then one of those two relationship records will have the HoH indicator field set to -1.

What am I missing? My goal at the moment is to be write a query that returns one row per household.

Thanks.

One caution, whether someone has a Spouse Relationship gets tracked in two different places that can sometimes get out of sync.

RECORDS.SPOUSE_ID points to the spouse in RECORDS.ID

then there's CONSTIT_RELATIONSHIPS.IS_SPOUSE (which can accidentally get more than one Relationship record flagged as a Spouse).

Thanks for the responses James and John. It was a little tricky because it seems that CONSTIT_RELATIONSHIP rows having IS_HEADOFHOUSEHOLD set to -1, that the RELATION_ID is actually the contituent who is condsidered to be the head of household. I 'think' that's right.

Anyway, here is the query I've come up with for generating those constituents considered hoh.

(

-- Get constituents with no spouse relationship

SELECT r.CONSTITUENT_ID

FROM records r

WHERE NOT EXISTS

(SELECT *

FROM constit_relationships cr

WHERE cr.CONSTIT_ID = r.ID

and is_spouse = -1)

UNION

--Get constituents who are marked as the head of household

SELECT r.CONSTITUENT_ID

FROM records r

WHERE EXISTS

(SELECT *

FROM constit_relationships cr

WHERE cr.RELATION_ID = r.ID

and cr.is_spouse = -1

and cr.IS_HEADOFHOUSEHOLD = -1)

)

Head of Household SQL Logic

Comments

Categories