Select Poorly Used Start and End Dates From Facility Table

Question

I'm using DB2, although a solution using any flavor of SQL would likely be easy enough for me to convert.

I didn't design this database, or the application that uses the database. I haven't the power to change this application, how it works, or the data. Because it defies what I consider to be conventional use of a start and an end date, I am struggling with writing something as simple as a select for a specific point in time.

Here are the relevant/edited parts of the table:

OBJECTID    FACILITY_ID  START_DATE  END_DATE FACILITY_NAME
1001     500  1/1/1980  5/1/2000 Really Old Name
1002     500  1/1/1980  1/1/2006 Old Name
1003     500  1/1/1980  null  Current Name
1004     501  1/1/1980  3/1/2008 Closed Facility Name
1004     502  1/1/1980  null  Another Current Name

What I want to return, are the records which are valid for 7/1/2005:

OBJECTID    FACILITY_ID  START_DATE  END_DATE FACILITY_NAME
1002     500  1/1/1980  1/1/2006 Old Name
1004     501  1/1/1980  3/1/2008 Closed Facility Name
1004     502  1/1/1980  null  Another Current Name

I'm trying to avoid subselects, but understand they may be necessary. If I do need a subselect, I'd like to keep it limited to one. Looking between the start and end date doesn't work, because it doesn't return facilities which have only one record with a null end date. Adding an OR condition to include end dates which are null may return more than one record in some cases. This problem seems so simple on the service, that I must be missing a ridiculously obvious solution. Does anyone have any ideas?

Answer 1

would this work:

SELECT * FROM table_name
WHERE START_DATE < '7/1/2005' AND (END_DATE > '7/1/2005' OR END_DATE IS NULL);

Answer 2

The trick is to coalesce the end date to the next day, :) Coalesce basically replaces a null value with the second parameter. Pretty cool little trick.

select * from TAble where START_DATE < @DATE and Coalesce(END_DATE, @DATE+1) > @DATE

Answer 3

From what you've given us, this is what you want:

select * from facitities
where START_DATE <= @my_date and (@mydate <= END_DATE or END_DATE is null)

however, I suspect you knew that, and want something different, in which case, you'll have to be more specific about what's wrong with the data.

Answer 4

Take 2:

select OBJECTID, FACILITY_ID, START_DATE, FACILITY_NAME, MIN(END_DATE) as  END_DATE
from facitities
where START_DATE <= @my_date and (@mydate <= END_DATE or END_DATE is null)
group by OBJECTID, FACILITY_ID, START_DATE, FACILITY_NAME

Answer 5

Sorry to answer my own question. Thanks all, the answers given were very helpful. It was also helpful to think about exactly what I was trying to accomplish. Combining concepts from the answers that were given, I was able to come up with something that seems to work:

SELECT 
    * 
FROM 
    FACILITY_TABLE
WHERE 
    (END_DATE IS NULL
     AND OBJECTID NOT IN 
     (SELECT A.OBJECTID FROM FACILITY_TABLE A 
     WHERE '7/1/2005' BETWEEN A.BEGINDATE AND A.ENDDATE))
  OR 
    '7/1/2005' BETWEEN FACILITY_TABLE.START_DATE AND FACILITY_TABLE.ENDDATE

Since the start date was made meaningless by the data, I didn't include it. The returns only the records that were valid back in 7/1/2005 without also including the current record if a record was expired between now and then.

Answer 6

Slight variation on the above:

select * from facilities
where @my_date between START_DATE AND COALESCE(END_DATE, CURRENT DATE)