Posted by: Karlo Bartels | 2009/02/10

Date range search

How to search a date range without an end date

The other day I was asked to create a query which retrieves a value for a specific date from a table containing prices that only had a start date, but no end date. This is what the table looked like:

date range table 1

As you can see, water prices increased three times last year. So how would you query this table to get the water price at 31 May 2008?

Since you can’t do this directly (because there is no record with start date 2008-05-31) you have to query the table using a date range. However, this table does not contain a column with an end date, so you have to create a self-join on the table which refers to the next row to get the end date. You can’t simply refer to the CostID column, because the IDs are not consecutive. I used the ROW_NUMBER() function to generate a column called RowNumber that contains a consecutive sequence of row numbers:

SELECT CostID, StartDate, WaterCost,
      
ROW_NUMBER() OVER (ORDER BY CostID) AS RowNumber
FROM   EnergyCosts;

This yields the following result:

date range table 2

Now that we have consecutive row numbers, we can self-join the results to generate an EndDate column. To do so, I used two CTEs: the first returns the results you saw above, while the second self-joins the first to generate the EndDate column:

;WITH CostsNoDateRange(StartDate, WaterCost, RowNumber) AS
(
  
SELECT  StartDate, WaterCost,
          
ROW_NUMBER() OVER (ORDER BY CostID) AS RowNumber
  
FROM    EnergyCosts
),
CostsWithDateRange(StartDate, EndDate, WaterCost) AS
(
  
SELECT  T1.StartDate, T2.StartDate AS EndDate, T1.WaterCost
  
FROM    CostsNoDateRange T1 LEFT OUTER JOIN CostsNoDateRange T2
  
ON      T1.RowNumber = T2.RowNumber - 1
)
SELECT * FROM CostsWithDateRange;

This will return the following:

date range table 2

Now that we have an end date column, we can use it in the WHERE condition of the CostsWithDateRange CTE. I created a stored procedure that does all that:

CREATE PROCEDURE [dbo].[GetWaterCost]
  
@Date date
AS
BEGIN
   SET NOCOUNT ON
;
  
DECLARE @LastDate date
  
SET @LastDate = CONVERT(date, '31-12-9999', 105)

   ;WITH CostsNoDateRange(StartDate, WaterCost, RowNumber) AS
  
(
  
SELECT
           
StartDate, WaterCost
          
,ROW_NUMBER() OVER (ORDER BY CostID)
  
FROM    EnergyCosts
  
),
  
CostsWithDateRange(StartDate, EndDate, WaterCost) AS
  
(
  
SELECT 
           
-- Get start date of period. If the requested date is
            -- not in the table, no records will be returned.
           
T1.StartDate
           
-- Get end date of period. If the requested date is
            -- not present, use the highest date possible.
          
,ISNULL(T2.StartDate, @LastDate)
           
-- Get cost column
          
,T1.WaterCost
  
FROM    CostsNoDateRange T1 LEFT OUTER JOIN CostsNoDateRange T2
  
ON      T1.RowNumber = T2.RowNumber - 1
  
)
  
SELECT  WaterCost FROM CostsWithDateRange
  
WHERE   @Date >= StartDate AND @Date < EndDate;
  
END

Please note I added the ISNULL statement to prevent a NULL value in the EndDate column, which allows queries for prices later than the last StartDate entry. 

The following table contains some results for different dates:

Date WaterCost
01-02-2008 0,50
01-06-2008 1,421
30-08-2008 1,421
01-09-2008 3,2357
30-11-2008 3,2357
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Categories

%d bloggers like this: