I am creating a T-SQL View that joins 5 tables of election types to one main table of people. Not every person from my main table is in all 5 of the election tables (but if they are, they can not appeaer more than once within each election type table). So I am doing Left Outer Joins from my main table to the 5 election tables.
Each of these 5 election tables have the same structure and fields. What I want to do, is for each record, compare the values of a Date field in each one, and return the earliest non-null value.
Here is an example. Let's say for one record, the value of the "Effective Date" field in each election table is:
So I want to return 1/1/2008.
I thought about writing a CASE statement, but am not sure how to do that, because I think that checks the value of one particular field in a specific table, where I am comparing simliar fields across all five tables.
Can someone help?