view to select specific period or latest when null

Question

Hi

I have a product table which simplifies to this:

create table product(id int primary key identity, productid int, year int, quarter int, price money)

and some sample data:

insert into product select 11, 2010, 1, 1.11
insert into product select 11, 2010, 2, 2.11
insert into product select 11, 2010, 3, 3.11
insert into product select 12, 2010, 1, 1.12
insert into product select 12, 2010, 2, 2.12
insert into product select 13, 2010, 1, 1.13

Prices are can be changed each quarter, but not all products get a new price each quarter. Now I could duplicate the data each quarter, keeping the price the same, but I'd rather use a view.

How can I create a view that can be used to return prices for (for example) quarter 2? I've written this to return the current (=latest) price:

CREATE VIEW vwCurrentPrices AS
    SELECT *
    FROM
    (
      SELECT  *, ROW_NUMBER() OVER (PARTITION BY productid  ORDER BY year DESC, quarter DESC) AS Ranking        
      FROM product
    ) p
    WHERE p.Ranking = 1

I'd like to create a view so I can use queries like select * from vwProduct where quarter = 2

Answer 1

After some experimenting I came up with a UDF that accomplishes my desired result. It first filters all prices before the desired date (quarter+year), then takes the highest rownumber, ie the latest result.

CREATE FUNCTION dbo.fTest(@year int, @quarter int)
RETURNS TABLE 
AS

return
(
    select * from
    (
        SELECT  *, 
        ROW_NUMBER() OVER (PARTITION BY productid  ORDER BY year DESC, quarter DESC ) AS rownum
        FROM product
        where DateAdd(month, (quarter - 1) * 3, DateAdd(Year, year - 1900, 0)) <= DateAdd(month, (@quarter - 1) * 3, DateAdd(Year, @year - 1900, 0))
    ) p
    where rownum = 1
)

Answer 2

CREATE TABLE quarters ([year] int, [quarter] int, PRIMARY KEY([year],[quarter]))

INSERT INTO quarters ([year], [quarter])
SELECT 2000 + number/4 [year], number%4 + 1 [quarter]
FROM master.dbo.spt_values
WHERE type = 'P' AND number BETWEEN 0 and 199

CREATE VIEW pricing WITH SCHEMABINDING AS 
WITH pricerank AS
    (SELECT q.[year], q.[quarter], p.productid, p.price, 
    p.[year] effectiveyear, p.[quarter] effectivequarter, 
    ROW_NUMBER() OVER (PARTITION BY p.productid, 
    q.[year], q.[quarter] ORDER BY p.[year] DESC, p.[quarter] DESC) AS ranking 
    FROM dbo.quarters q INNER JOIN dbo.product p
    ON p.[year] = q.[year] AND p.[quarter] <= q.[quarter] OR p.[year] < q.[year]
    WHERE q.[year] <= YEAR(GETDATE()))
SELECT [year], [quarter], productid, price
FROM pricerank
WHERE ranking = 1

CREATE UNIQUE CLUSTERED INDEX IX_pricing ON pricing([year],[quarter],productid)