view to select specific period or latest when null
Posted
by edosoft
on Stack Overflow
See other posts from Stack Overflow
or by edosoft
Published on 2010-04-12T08:34:23Z
Indexed on
2010/04/12
9:43 UTC
Read the original article
Hit count: 413
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
© Stack Overflow or respective owner