A View Inside My Head

Filtering using Effective Dates in Queries

Nothing makes me want to pull my hair out more than writing SQL Queries that need to join two or more tables, with one of those tables containing an "Effective Date" column. Of course, the requirement is inevitably to filter out all records from this join such that the match condition is on some date value from one table and the latest "Effective Date" from the second table that pre-dates the first table's date value. /headdesk

For example, suppose that we have a table that maintains a "capacity" number that changes over time. To maintain historical accuracy, an "Effective Date" column tracks when the capacity changes:

And another table that contains a "quantity" value that changes over time.

Now the challenge is to write a single SELECT statement that will join these two tables so that you can see Date, Quantity, and the effective Capacity for the given date all in the same row. Extraneous Capacities need to be filtered out. Inner queries are acceptable.

The result should be:

I have written a few different solutions to this problem in the past, and I'll post my most recent attempt in comments here. But, I'm interested to see if anyone has a real head-slapper approach to writing this type of query that differs from my method.

Here's some setup code to generate the tables used in the sample above:

CREATE TABLE #EffCapacity
    (
      EffDate DATETIME,
      Capacity INT
    )
INSERT  INTO #EffCapacity
VALUES  ( '1/1/2007', 50 )
INSERT  INTO #EffCapacity
VALUES  ( '5/1/2007', 45 )
INSERT  INTO #EffCapacity
VALUES  ( '1/1/2008', 52 )
CREATE TABLE #MyQuantity
    (
      Date DATETIME,
      Quantity INT
    )
INSERT  INTO #MyQuantity
VALUES  ( '1/1/2007', 45 )
INSERT  INTO #MyQuantity
VALUES  ( '3/4/2007', 33 )
INSERT  INTO #MyQuantity
VALUES  ( '8/11/2007', 65 )
INSERT  INTO #MyQuantity
VALUES  ( '2/28/2008', 56 )

Print | posted on Thursday, December 27, 2007 11:58 AM | Filed Under [ SQL ]