Hello All,

I have a database of various artist's average annual auction prices,
which I would like to graph. In some years there are no sales for an
artist, and therefore no data for that artist for that year, in the
table.

However in selecting the years to graph, I want to commence with the
current year and work back through the data until I reach a year with
no data, so that the graph covers an unbroken period.

For example in the table below, the period to be graphed would be 2000
to 2007, being the unbroken period working back from 2007, as there is
no data for 1999.

1996 $1,300
1998 $1,500
2000 $1,800
2001 $1,900
2002 $1,600
2003 $2,100
2004 $2,400
2005 $2,600
2006 $1,800
2007 $2,200

The length of the period to be graphed will vary from artist to
artist, but all will have a common starting point of the current year,
and work back from there.

Can this be accomplished with a SELECT statement? If not, any ideas as
to how it can be done?

Many thanks in advance,

John Furphy

Here is one method (SQL Server 2005). Since your explanation about starting
from current year (but pointing to 2007 which is not current) was a bit
vague, there are two solutions:

1). Based on assumption you really mean current year (2008 or whatever that
happens to be). The logic there is based on calculating ranking number for
year by artist, and adding that to the auction year, then subtracting the
current year. Given current year is 2008, the logic looks like:

Rank Auction Year Current Year Calc
----- ------------------ ----------------- ------
1 2008 2008 1
2 2007 2008 1
3 2006 2008 1
....
10 1998 2008 0
11 1996 2008 -1

As you can see then simply selecting the result set where Calc = 1 will give
you the sequence of years starting with current.

2). Assuming under "current year" you mean the latest year that had data for
the artist, the logic has to change a bit. Very close, but since this time
there is no defined anchor year, then you simply take part of the formula
and calculate ranking + auction year. Because sequential years will give you
the same number, then just taking the years with highest calculated number
will give you the latest auction year sequence.



CREATE TABLE AuctionPrices (
artist_id INT NOT NULL,
auction_year INT NOT NULL,
avg_price DECIMAL(12, 2) NOT NULL DEFAULT 0.0,
PRIMARY KEY (artist_id, auction_year));

INSERT INTO AuctionPrices VALUES (1, 1996, 1300.00);
INSERT INTO AuctionPrices VALUES (1, 1998, 1500.00);
INSERT INTO AuctionPrices VALUES (1, 2000, 1800.00);
INSERT INTO AuctionPrices VALUES (1, 2001, 1900.00);
INSERT INTO AuctionPrices VALUES (1, 2002, 1600.00);
INSERT INTO AuctionPrices VALUES (1, 2003, 2100.00);
INSERT INTO AuctionPrices VALUES (1, 2004, 2400.00);
INSERT INTO AuctionPrices VALUES (1, 2005, 2600.00);
INSERT INTO AuctionPrices VALUES (1, 2006, 1800.00);
INSERT INTO AuctionPrices VALUES (1, 2007, 2200.00);
INSERT INTO AuctionPrices VALUES (1, 2008, 2100.00);

INSERT INTO AuctionPrices VALUES (2, 1993, 2200.00);
INSERT INTO AuctionPrices VALUES (2, 1994, 2100.00);
INSERT INTO AuctionPrices VALUES (2, 2006, 1500.00);
INSERT INTO AuctionPrices VALUES (2, 2007, 2400.00);

-- If using current year
WITH CTEPrices
AS
(SELECT artist_id,
avg_price,
auction_year,
ROW_NUMBER()
OVER(PARTITION BY artist_id
ORDER BY auction_year DESC) +
auction_year -
YEAR(CURRENT_TIMESTAMP) AS seq
FROM AuctionPrices)
SELECT artist_id,
auction_year,
avg_price
FROM CTEPrices
WHERE seq = 1;

-- If using latest year with prices
WITH CTEPrices
AS
(SELECT artist_id,
avg_price,
auction_year,
ROW_NUMBER()
OVER(PARTITION BY artist_id
ORDER BY auction_year DESC) +
auction_year AS seq
FROM AuctionPrices)
SELECT artist_id,
auction_year,
avg_price
FROM CTEPrices AS A
WHERE seq = (SELECT MAX(seq)
FROM CTEPrices AS B
WHERE B.artist_id = A.artist_id);

HTH,

Plamen Ratchev
http://www.SQLStudio.com