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.