Listing Husband and Wife on One Row - Part 3

I didn't get a post in last week. I have been busy reviewing and revising the Fall 2009 training season workbooks and trying to get SSTips ready to send out as well. So today I am finally getting around to posting part 3 of the series on putting names together on one row. Today we'll look at how to put the names together into one column as a combined name. This is tricky because of the possibility of different last names, as well as the fact that sometimes spouse information is not available even when we know that the person in the database is married. Another factor is that the gender of the head of household may be male or female. And I'm not even going to get into handling special titles - such as Dr. - because that would add another layer of complexity to the basic process of combining names.

The following code puts two names together. It limits the results to those who have a TESTGRUP profile code, the same as in the previous parts of this series.

/* query begins */
select
SelectedName = SelectedNames.FirstMiddle + ' ' + SelectedNames.LastName,
Spouse = Spouses.FirstMiddle + ' ' + Spouses.LastName,
CombineIfMarried =
case /* This begins a conditional tree. The top branch of the tree is to check that the selected person is married. */
when SelectedNames.MaritalStatus in ('M','R') then
case /* The next branch is to check the gender of the selected person. */
when SelectedNames.Gender = 'M' then
case /* The bottom banch determines if the person and the spouse have the same last name. If there is no spouse info in the database, both comparisons will fail and the "else" condition applies. */
when SelectedNames.DifName + Spouses.DifName = 0 then Spouses.FirstMiddle + ' & ' + SelectedNames.FirstMiddle + ' ' + SelectedNames.LastName + isnull(' ' + SelectedNamesSuffixes.Descr, '')
when SelectedNames.DifName + Spouses.DifName = -1 then SelectedNames.FirstMiddle + ' ' + SelectedNames.LastName + isnull(' ' + SelectedNamesSuffixes.Descr, '') + ' & ' + Spouses.FirstMiddle + ' ' + Spouses.LastName
else 'Mr. & Mrs. ' + SelectedNames.FirstMiddle + ' ' + SelectedNames.LastName + isnull(' ' + SelectedNamesSuffixes.Descr, '')
end
when SelectedNames.Gender = 'F' then
case
when SelectedNames.DifName + Spouses.DifName = 0 then SelectedNames.FirstMiddle + ' & ' + Spouses.FirstMiddle + ' ' + Spouses.LastName + isnull(' ' + SpousesSuffixes.Descr, '')
when SelectedNames.DifName + Spouses.DifName = -1 then Spouses.FirstMiddle + ' ' + Spouses.LastName + isnull(' ' + SpousesSuffixes.Descr, '') + ' & ' + SelectedNames.FirstMiddle + ' ' + SelectedNames.LastName
else 'Mr. & Mrs. ' + SelectedNames.LastName
end
else 'Mr. & Mrs. ' + SelectedNames.LastName
end
else SelectedNames.FirstMiddle + ' ' + SelectedNames.LastName + isnull(' ' + SelectedNamesSuffixes.Descr, '')
end
from
Shelby.NANames as SelectedNames inner join
Shelby.NAProfiles as Profiles on SelectedNames.NameCounter = Profiles.NameCounter and Profiles.Profile = 'TESTGRUP' left join
Shelby.NANames as Spouses on SelectedNames.FamNu = Spouses.FamNu and Spouses.UnitNu = case when SelectedNames.UnitNu < 2 then abs(SelectedNames.UnitNu -1) end left join
Shelby.NASuffixes as SelectedNamesSuffixes on SelectedNames.SuffixCounter = SelectedNamesSuffixes.Counter left join
Shelby.NASuffixes as SpousesSuffixes on Spouses.SuffixCounter = SpousesSuffixes.Counter

/* query ends */

The CASE statement to put the names together has three levels, as noted with the comments in the statement. The first level divides those who are married from those who are not. The second level divides the people selected into male and female, and the third level divides those spouses with different last names.

Listing Husband and Wife on One Line - Part 2

Last week I posted on how to put both husband and wife on one line by joining to the names table (NANames for Shelby v5) multiple times. That is a useful technique when you want to show the spouse information for every name that is part of the desired result set. However, a different technique is needed when you want to show spouse information only when both spouses are part of the desired result set. A typical example might be a label report for the choir or another group in which one or both of the spouses might participate, and when both spouses do participate, they should both be combined. When only one spouse participates, the other should not be part of the results.

This new method selects the names as you would normally, joining only once to the names table. But then add a GROUP BY clause to the query, grouping by the common factor for the family (NANames.FamNu for Shelby v5). After you group the results, you can use a CASE statement to select either the Head of House or the Spouse name to appear in the results.

In the example query below I have selected all the adult choir members by using an inner join to the NAProfiles table and at the same time limiting the included Profile values to those that begin with MUAC, which is a common prefix on all the adult choir profile codes. The query uses GROUP BY and CASE statements to pull the spouses together and list them in the appropriate columns of the results.


select
HHFirstName = max(case when Names.UnitNu = 0 then Names.FirstMiddle end),
HHLastName = max(case when Names.UnitNu = 0 then Names.LastName end),
SPFirstName = max(case when Names.UnitNu = 1 then Names.FirstMiddle end),
SPLastName = max(case when Names.UnitNu = 1 then Names.LastName end)
from
Shelby.NANames as Names inner join
Shelby.NAProfiles as Profiles on Names.NameCounter = Profiles.NameCounter and Profiles.Profile like 'MUAC%'
group by
Names.FamNu


Here are the results:

You can see that the Blackwells , the Carters, and the Glovers are in the choir together. In the visible results all the other individuals do not have a spouse in the choir.

With the two techniques from last week and this week, you should be able to get the names of spouses together whenever you need them in one row of results. Of course, listing them on one row is usually only part of the requirement. Often you will want to combine them together into one column as well, with a result such as "John & Jane Doe" or "Mr. & Mrs. Smith." That process is surprisingly complex, and will be the topic for next week's post.

Listing Husband and Wife on One Line - Part 1

There are several ways to combine a husband and wife in query results, each one offering an advantage over the others depending on the overall design of the query. The first method I want to throw out there assumes that the Head of House will be the "primary" name chosen by the WHERE condition of the query. Adding the spouse involves joining a second time to the names table (NANames for Shelby Systems v.5).

Before examining the query statement itself, here are the noteworthy columns of the NANames table when it comes to combing family members together:

• NameCounter - a numeric value that is unique for every person in the table
• FamNu - a numeric value shared by all members of the same family; it is helpful to realize that this value is the same as the head of household's NameCounter value.
• UnitNu - a numeric value indicating the family position in the household; the head of house value is zero (0) and the spouse value is one (1).

The following query lists all the heads of house in the NANames table and lists the spouses' names along side.


select
HeadOfHouse = HH.FirstMiddle + ' ' + HH.LastName,
Spouse = SP.FirstMiddle + ' ' + SP.LastName
from
Shelby.NANames as HH left join
Shelby.NANames as SP on HH.FamNu = SP.FamNu and SP.UnitNu = 1
where
HH.UnitNu = 0 and HH.TitleCounter > -1


The UnitNu condition in the WHERE clause is there to prevent the spouses and children from being listed on a separate line. The TitleCounter condition is there to eliminate businesses and other organizations from the list.

A simple variation of this approach allows you to add the spouse of either husband or wife, depending on who is included by the WHERE criteria. In the following example the WHERE condition is not given, but the FROM condition will add the spouse of whichever name is part of the result list.


select
HeadOfHouse = ChosenNames.FirstMiddle + ' ' + ChosenNames.LastName,
Spouse = Spouses.FirstMiddle + ' ' + Spouses.LastName
from
Shelby.NANames as ChosenNames left join
Shelby.NANames as Spouses on ChosenNames.FamNu = Spouses.FamNu and Spouses.UnitNu = ABS(ChosenNames.UnitNu - 1)
where
any conditional statements


The join to the "Spouses" instance of NANames uses a mathematical operation to return a 1 whenever the primary name has a UnitNu value of 0 and to return a 0 whenever the primary name has a UnitNu value of 1. Thus the spouse will be added to any head of house, and the head of house will be added to any spouse.

Both variations of this approach use a LEFT join to add the spouse information. This type of join means that all the primary names will be listed, regardless of whether there actually is a spouse in the system for that person or not.