Ranking (Look for previous ranking)

[leobear]

ID Sales Date Sales
1 A 201101 8
2 B 201101 7
3 C 201101 7
4 D 201101 6
5 A 201102 3
6 B 201102 4
7 C 201102 6
8 D 201102 7


9 A 201103 2
10 B 201103 8
11 C 201103 4
12 D 201103 5

Hi I have a table look like this and I have created a ranking sql.
SELECT a.SalesPerson, a.Sales, (Select count(*) from Table1 as b where
b.sales > a.sales and b.date = a.date ) + 1 AS rank, a.date
FROM Table1 AS a
ORDER BY sales DESC;
The question now I have is how can I look for previous ranking in the
same query?
i.e. in 201103 "B" previous ranking is 3 which come from the data of
201102.
and for 201101, how can I tell SQL to get the data from 201012?
Thanks.

[June7]

The previous ranking for 201103 B is from 201102 A, not B? Would the previous ranking for 201103 D be from 201102 C?

[leobear]

Hi June,

The previous ranking for B is still B but the date is one period backward.

I am not sure if how I can calculate the previous rank in the same query of below.

SELECT a.SalesPerson, a.Sales, (Select count(*) from Table1 as b where
b.sales > a.sales and b.date = a.date ) + 1 AS rank, a.date
FROM Table1 AS a
ORDER BY sales DESC;

Thanks.

[June7]

Check this tutorial for calculation between records http://allenbrowne.com/subquery-01.html#AnotherRecord