May 8, 2002 at 1:00 pm
Hello All. I am attemtpting to write a query that will return a Rank order from 3 different columns. After calculating the SUM from a couple different columns, I have created a TEMP table to store the SUM. How can I write a query to calculate the RANK in Order? Here is a sample of my code to this point:
--------------Beginning Of Code-----------------------
Create Table ##TestTable
(
Store_Number int,
[Month] int,
MonthScore Float
)
Insert Into ##TestTable
Select
StoreName_Dept.Store_Number,
DATEPART(mm,StoreName_Cashier.ShopMonth) As [Month],
((Sum(StoreName_Dept.DeptScore)/Count(StoreName_Dept.Department))*.3)
+
(Cast(Avg(StoreName_Cashier.CashierScore) AS Float)*.4)
+
(Cast(Avg(StoreName_Cashier.EnvirScore) AS Float)*.3)
As MonthScore
FROM StoreName_Dept
JOIN
StoreName_Cashier
ON
StoreName_Dept.ShopIdentity =
StoreName_Cashier.ShopIdentity
GROUP BY StoreName_Dept.Store_Number,
DATEPART(mm,StoreName_Cashier.ShopMonth)
Order By DATEPART(mm,StoreName_Cashier.ShopMonth),
StoreName_Dept.Store_Number
Select
W1.Store_Number, W1.[Month], W1.MonthScore,
(Select count (Distinct MonthScore)
From ##TestTable as W2
Where W2.MonthScore >= W1.MonthScore
AND W2.[Month] = W1.[Month]) as rank
From ##TestTable as W1 WHERE [Month] = '1'
ORDER BY Rank ASC
Drop Table ##TestTable
--------------End Of Code------------------
Thank You in Advance for your assistance
Andrew
How long a minute is....
Depends on what side of the bathroom door you are on.
May 8, 2002 at 1:40 pm
What database you using?
May 9, 2002 at 6:52 am
I am using SQL 2000.
🙂
How long a minute is....
Depends on what side of the bathroom door you are on.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply