In one of the systems we support, there is a SQL Server table with a bunch of date columns that store information on the most recent dates for several types of services a customer receives. Some of these service types are called “basic”, “intensive”, and “training”, for example. These are some sample rows:
We needed to generate a report that indicated the most recent service a customer had received, of any type. The end result we wanted was this:
A couple of solutions came to mind:
1- Use a scalar function with nested IF statements.
2- Pivot the data and then calculate the MAX per customer.
The first one gets way too complex as more columns need to be considered (in our case, about 10). The second one seemed to require a complex query or perhaps two. The solution that appear to be both simpler and more elegant was to use a derived table for the column values of each row and then calculate the MAX, like this:
