xpt xpt - 1 month ago 8
SQL Question

SQL, Select by the date BETWEEN the record rows

The typical scenario/application is to search with the following data/table structure:

StartDate TheVersion
day1 ver1
day2 ver2


I.e., each new version number is recorded into a table with a starting date.

So for a given date, if it is BETWEEN
day1
AND
day2
, and
day1
and
day2
are the most adjacent records in the table, then
ver1
should be returned.

The problem is that I can't use the SQL BETWEEN clause because the
day1
and
day2
are actually from different rows.

How to do that? I have thought of several different ways, but none of them is not messy. Generic SQL or T-SQL appreciated. I'm on SQL Server 2008R2 BTW. Thanks.

Answer

If I understand correctly, you want to get the version that was in effect at the time of a Given_Date. This is a nested query that first obtains the maximum StartDate on or before Given_Date, and then returns the version attached to that date:

SELECT
Version_Table.StartDate,
Version_Table.TheVersion
FROM
Version_Table
INNER JOIN 
    (SELECT
    Max(StartDate)
    FROM
    Version_Table
    WHERE 
    StartDate <= Given_Date) maxdate on maxdate.startdate = version_table.startdate
Comments