Sql query to overlay date/period overrides over default dates

Question

Any ideas on building a Sql Server (2008) query that will give me say the "date specific prices for an item based on the default or override where exists".

So a Default table might look like this - columns Price, StartDate, EndDate (yyyy-M-d):

Default:  $10, 2010-1-1, 2010-2-1

The Override table like this:

Override: $12, 2010-1-5, 2010-1-8

And the query would return:

Result:   $10, 2010-1-1, 2010-1-4
          $12, 2010-1-5, 2010-1-8
          $10, 2010-1-9, 2010-2-1

Probably I'd wrap this in a stored proc or function and call it for a specific date range.

Answer 1

Soemthing like:

SELECT
   D.Price, ISNULL(O.StartDate, D.StartDate), ISNULL(O.EndDate, D.EndDate)
FROM
   Default D
   LEFT JOIN
   Override O ON D.Price= O.Price

Answer 2

The proper design will be to have only one table. You don't want the override table at all. Just keep everyhing in the single table - constrained by the date range. The query becomes much simpler as well.

Your table structure becomes

CREATE TABLE Rates
(ID INT NOT NULL,
 Rate Decimal NOT NULL,
 FromDate NOT NULL,
 ToDate   NOT NULL,
 CONSTRAINT PK_RATES (ID,FromDate,ToDate))

Then the query becomes

SELECT Rate FROM Rates WHERE ID = @ID AND FromDate = (SELECT MAX(FromDate) FROM Rates WHERE ID = @ID AND FromDate <= @Date) AND ToDate = (SELECT MIN(ToDate) FROM Rates WHERE ID = @ID AND ToDate >=@Date)

Answer 3

I think you're going to have to do something like this (assuming that you want to keep the override dates separate, and that you want to avoid anything procedural):

1. define and populate a utility table, listing each individual date which could be relevant to you
2. construct a SELECT that "marks" each date from this utility table as either i) belonging to the override dates or ii) belonging to the default dates
3. group the results of this SELECT by date and "mark"
4. join these results back to the respective price info