One of Paul's first assignments on a new contract seemed fairly simple ...

Produce a report that will list the Case Number, Officer Name, and Days Open for all remote offices where the case is TPS Reportable.

The report fields were accessible from the Cases table, so Paul wasn't too worried. The Is_TPS_Reportable column was a nullable INT -- slightly different than a BIT or CHAR(1) type that one would normally use -- but, not too out of the ordinary. Paul was pretty sure that the data in that column would be 1's and 0's, but just through he'd double check: 

> SELECT DISTINCT [Is_TPS_Reportable] FROM [Cases]
 +-------------------+
 | Is_TPS_Reportable |
 +-------------------+
 | NULL              |
 | -1                |
 | 0                 |
 | 1                 |
 | 2                 |
 | 4                 |
 +-------------------+

At this point, Paul was starting to have second thoughts about this contract. No less, after trudging through some sparse documentation, he leaned that Is_TPS_Reportable is actually a foreign key (unenforced) to another table ... 

> SELECT * FROM [YN_Lookups]
 +----+---------------+
 | ID | Text          |
 +----+---------------+
 | 1  | Yes           |
 | 2  | No            |
 | 3  | Not Specified |
 | 4  | NULL          |
 | 5  | Other         |
 | 7  | Unknown       |
 +----+---------------+

... hoooo boy! It's going to be a loooong contract ....

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!