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.

Existential Queries

Recently I was asked to come up with a query that would select names of people who have one kind of record in a given table but from that group omit people with a different kind of record in the same table.

For those familiar with the Shelby Systems v.5 software, I needed to pull people with a particular Profile code but of that group omit people with a different Profile code.

The only method I could find to meet both condition was to use the EXISTS( ) function. This function returns a boolean true/false value based on whether or not a specified subquery returns any results. If the subquery returns results, the value is true. If it does not, it is false. As with most functions like this, the value can be reversed by the key word NOT.

I had run across EXISTS( ) some time ago, and I have used it periodically to simplify some query conditions. I'm glad I had it in my arsenal, because it was the only way I found to solve the problem of "find everyone who has one Profile, but not if he has specific second Profile too."

Here is the basic approach I took to solving this problem with a query, simplified to a minimum number of column references:

select

NameCounter, FirstMiddle, LastName

from

Shelby.NANames

where

exists (select * from Shelby.NAProfiles where Shelby.NAProfiles.Profile = 'value1' and Shelby.NAProfiles.NameCounter = Shelby.NANames.NameCounter) and not exists (select * from Shelby.NAProfiles where Shelby.NAProfiles.Profile = 'value2' and Shelby.NAProfiles.NameCounter = Shelby.NANames.NameCounter)

In regular English, the query says to pull the names of those who have a Profile of 'value1' but who do not also have a Profile of 'value2.' There may be other ways to achieve this kind of result, but this one worked for me.

Notice that the subquery's SELECT clause uses the asterisk (*) to stand in for all columns. Because the subquery is only used as a test to see if even one row is returned, there is no need to be specific about which column to select; the asterisk works fine.

Comparing Dates in SQL

I found out the hard way (read: customer calling in to complain that the query I wrote him was omitting some events from the calendar query I wrote for him) to NEVER use basic math comparisons (=, <, >) for dates; at least, I have learned not to use them without DATEDIFF( ). It is more difficult sometimes, but it is going to render the correct result every time, with the other comparison operators will not.

The problem is that dates are not stored merely as dates but as datetime values. This means there is an hour, minute, second, and millisecond value associated with every date, even if that value is ignored by the user interface. In most cases this is not going to cause a mistake when comparing two different dates, but it does cause a problem when one is trying to find equivalence between two instances of the same date.

Here is the underlying "gotcha": When the time of day does not matter, most databases store dates as if they were at midnight of the day in question whereas the GETDATE( ) function returns the date and current time. Any calculations based on the GETDATE( ) value also have the current time. And because 1/1/2009 00:00:00 is technically less than 1/1/2009 15:39:15 (or any other time after midnight), those two values will not satisfy a comparison using "=" even though they signify the same calendar day.

Here is a simple CASE statement showing the use of DATEDIFF( ) in conjunction =, >, or <.

CASE
WHEN DATEDIFF(day, date1, date2) = 0 THEN "The dates are the same."
WHEN DATEDIFF(day, date1, date2) > 0 THEN "Date1 is earlier than Date2."
WHEN DATEDIFF(day, date1, date2) <>


Using an approach like this will always give you the results you want, without any problems from the time element of the datetime value.

Reporting Services and check boxes

Most of the reports I make are created using a slimmed-down version of Active Reports, which has been integrated into Shelby Systems v.5 software as part of the ShelbyQUERY tool. Active Reports has a "checkbox" control object, which displays a checkmark in a box when the value of the field is anything other than "false," or an empty box when the value is "false." This can come in handy for reports with lots of Boolean (true/false) values.

Interestingly, even though SQL Server Reporting Services is much superior to Active Reports in many respects, it is lacking in a "checkbox" object. This seems to me to be a significant oversight, but it not completely insurmountable.

Although there is no "checkbox" object in Reporting Services, you can emulate one using a TextBox, if you are willing to have an "X" instead of an actual check mark in the box. Just do the following.

Create a new text box, put an "X" in it, and change the following properties to the listed values:

• BorderStyle: Solid
  
• Color: = IIf (Fields!MyField.Value, "Black", "White")
  
• Size: .175in, .175in (this fits a 10pt font; make it larger for a bigger font)
  

With these setting the box will have an "X" in it for true values and be empty for false values. I haven't tried it yet, but I suspect it might even be possible to use a font like Wingdings and actually print a checkmark in the box instead of just an X.