"CSS-like" fallback cascading data in SQL Server 2005

Question

I'm trying to implement a price fallback system in SQL server. I'd like to have a set of increasingly specific prices (eg: by region, store, warehouse, etc.) for a product, that may or may not be defined, and be able to select the most specific prices (ie: the one with most parameters defined) in a report.

For example, I might have the following data:

 Region
--------
   1
   2

 Store  
--------
   1
   2
   3

Product | Region | Store | Price
--------------------------------
  Foo   | NULL   | NULL  |  1.0
  Foo   | 1      | NULL  |  2.0
  Foo   | 1      | 1     |  2.5
  Foo   | 1      | 2     |  2.3

So if I wanted to know the price for product Foo...

• in Region 1, Store 1 = 2.5
• in Region 1, Store 3 = 2.0 (Store 3 is not defined explicitly in the data, so the result comes from the NULL store for Region 1)
• in Region 2, Store 4 = 1.0 (Region 2 is not defined explicitly in the data, so the result comes from the NULL region)

For the sake of simplicity, I can assume that the Store is always more specific than the region, and a store can only exist in one region.

A schema for this data would be something like this:

CREATE TABLE Prices(
 ID      int   IDENTITY(1,1) NOT NULL,
 Product int   NOT NULL,
 Region  int   NULL,
 Store   int   NULL,
 Price   money NOT NULL,
 CONSTRAINT PK_Prices PRIMARY KEY CLUSTERED (ID ASC),
 CONSTRAINT IX_Prices UNIQUE NONCLUSTERED (Product ASC, Region ASC, Store ASC)
)

Aside from crunching this data in code, how can I query this table for a list of effective prices for every product, based on (Region, Store)?

Answer 1

try this:
EDIT for all products, each listed once, even where given region and store do not exist...

CREATE PROCEDURE GetPrice
     @Region int = null
    ,@Store int = null
AS

SELECT
    Product
        ,Region
        ,Store
        ,Price
    FROM (SELECT
              Product
                  ,Region AS Region
                  ,Store As Store
                  ,Price
                  ,Row_Number() OVER(PARTITION BY Product ORDER BY SortBy,Product,Region,Store,Price) AS RowNumber
              FROM (SELECT 1 AS SortBy,* FROM Prices WHERE (Region = @Region OR @Region IS NULL) AND (Store = @Store OR @Store IS NULL)
                    UNION
                    SELECT 2 AS SortBy,* FROM Prices WHERE (Region = @Region OR @Region IS NULL) 
                    UNION
                    SELECT 3 AS SortBy,* FROM Prices
                   ) Prices
         ) dt
    WHERE RowNumber=1
    ORDER BY Product

GO

Answer 2

I guess this must work (didn't try it, though):

select top 1 price
from prices
where product = @product
and isnull(region, @region) = @region
and isnull(store, @store) = @store
order by region desc, store desc

A product must have least 1 Prices record. The ORDER BY .. DESC sorts original NULL values last.

Answer 3

Wow, you guys are trying way too hard. Here's the easy way: use the COALESCE command, which takes the first non-null value.

SELECT COALESCE(st.Price, rg.Price, gn.Price) AS Price
  FROM dbo.Prices gn  /*General price*/
  LEFT OUTER JOIN dbo.Prices rg /*Regional price*/
    ON rg.Product = @Product AND rg.Region = @Region AND rg.Store IS NULL
  LEFT OUTER JOIN dbo.Prices st  /*Store price*/
    ON rg.Product = @Product AND rg.Region = @Region AND rg.Store = @Store
  WHERE gn.Product = @Product
    AND gn.Region IS NULL AND gn.Store IS NULL

That way, you'll get the store price if that's not null, or the regional price if that's not null, or the general price if all else fails.

Answer 4

I think your data model might be a little screwy. Is the prices table being populated just for the report? I might review my tables, but if i had to use this setup, I would do something like this:

For product prices in a specified region (NOT STORE). Use this select in a sproc that accepts region as a parameter. If I did it right, it will return the region price, if there is one, and the product price, if there isn't.

Select Product, Price from dbo.prices where (Region = @region) or (region is NULL and product not in (select product from prices where region = @Region))

For product prices in a specified store. Use this select in a sproc that accepts store as a parameter. You will also need to pass in region or figure it out from the store. If I did it right, it will return the store price, if there is one, then he region price, then the product price.

Select product, price from dbo.prices where (store = @store) or (store is NULL AND region = @Region and product not in (select product from prices where store = @store) or (store is NULL and region is NULL and product not in (select product from prices where region = @Region))

To get prices from multiple regions or stores, just execute one of the above sprocs multiple times (in a loop or cursor).