Ranking (Look for previous ranking)

[wilkins]

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.

 

[Bob Barrows]

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?

The same method as the solution to your previous post: a correlated
subquery.
Give it a try. If you still haven't solved it tomorrow, I'll take another
look.

 

[wilkins]

The same method as the solution to your previous post: a correlated
subquery.
Give it a try. If you still haven't solved it tomorrow, I'll take another
look.

Thanks again Bob, I have solved the last one, but can't manage to
solve this one because it seems i can't use select () to refer the
data I just create. I manage to create the previous ranking using 2
queries as below, but I can't combine into one.


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;

Select rank.*, (select rank from rank as a where a.salesperson =
rank.salesperson and rank.date -1 = a.date) from rank

 

[Bob Barrows]

You can, but I wouldn't. You should save the query and join to it in a new
query to avoid writing the same logic twice.

Create a table (MonthNumbers) like this:
MonthNumber MonthDesc
1 199901
2 199902
....
12 199912
13 200001
....
24 200012
25 200101
etc


Now join your sales data to MonthNumbers on date=MonthDesc.
This allows you to do arithmetic with the MonthNumber values.

Thanks again Bob, I have solved the last one, but can't manage to
solve this one because it seems i can't use select () to refer the
data I just create. I manage to create the previous ranking using 2
queries as below, but I can't combine into one.


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;

Select rank.*, (select rank from rank as a where a.salesperson =
rank.salesperson and rank.date -1 = a.date) from rank

Have you saved the MonthlySalesRank query? I would, without the order by
clause, of course. Then

SELECT SalesPerson, Sales,[date], Rank,
(SELECT pmn.Rank
FROM MonthlySalesRank as pm JOIN MonthNumbers As pmn ON
pm.[date]=pmn.MonthDesc
WHERE mn.MonthNumber = pmn.MonthNumber + 1
) As PreviousMonthlyRank
FROM MonthlySalesRank as m JOIN MonthNumbers As mn ON
m.[date]=mn.MonthDesc
ORDER BY Sales DESC