Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dmcnair81
Starting Member
3 Posts |
 Posted - 2012-07-23 : 17:28:28
|
| I am trying to compare a customer's performance to every other customer in the database, and display a 5 star rating on a report for them to kind of give them an idea as to which areas they are doing great in, and others that need improving. I have messed around with the Rank, Dense_Rank, and NTile functions, and got something pretty close to how I want it. I have a table that looks something like this.Location qID Value Rank DenseRank Stars-------- --- ----- ---- --------- -----A 1 96 1 1 10B 1 95 2 2 9C 1 94 3 3 8D 1 84 4 4 7E 1 81 5 5 6F 1 78 6 6 5G 1 78 6 6 4H 1 73 8 7 3I 1 73 8 7 2J 1 71 10 8 1This would be only part of the table. There are multiple qID's, and I partition the rankings based on qID. I use NTile(10) for the stars, where all odds are considered half stars (3 would convert to 1 1/2 stars, etc...). Now if I was only going to have 10 customers in the database, I see where I could just use the Rank column to get what I want, but becomes more difficult when I have an undetermined amount of customers. On locations that share the same value, I would like for the Stars column to have the same value as well, and also keep the value that is the greatest. Once a row comes up after that where the value is different, I want it to perform the same way as the Rank function does rather than the Dense_Rank function. For example, in the table above, I would want the Stars value from location G to change from 4 to 5, but I want to keep location H at 3 rather than incrementing it to 4. I apologize if this sounds really confusing, but wasn't sure how to best express what it is I'm looking to do. Any assistance would be appreciated. |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-07-23 : 19:36:40
|
I didn't completely follow your requirement, if you could show sample data that demonstrates the problem that would help. But, from what I understood, it could be something like this:SELECT ROUND(10.0*(Count(*) over()-DenseRank)/COUNT(*) OVER (),0) You may need to add a 0.5 if the ranks don't exactly match your requirement:SELECT ROUND(10.0*(Count(*) over()-DenseRank)/COUNT(*) OVER () + 0.5,0) If you have more than one qId, partition by quid.SELECT ROUND(10*(Count(*) over(PARTITION BY qID)-DenseRank)/COUNT(*) OVER (PARTITION BY qID)+0.5,0) |
 |
|
|
dmcnair81
Starting Member
3 Posts |
Posted - 2012-07-24 : 12:11:14
|
| You hit the nail right on the head. Your bottom select statement gave me exactly what I was looking for with just minor modifications. Changed - DenseRank to - [Rank] and added the +1 at the end where you showed +0.5. I was trying to find a way to make the NTile(10) function work with it so I would've never thought of doing it this way. Thanks for all of your help! |
|
|
|
dmcnair81
Starting Member
3 Posts |
Posted - 2012-07-24 : 12:22:55
|
| Not sure why I used +1. +0.5 makes it more accurate. Brain fart. |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-07-24 : 19:38:46
|
| Glad you got it figured out; there may be a more elegant and simpler way using NTILE, but I have never warmed up to NTILE. Take a look at this thread and Peso's reply if you are interested in pursuing "N-Tiling". http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=158923 |
|
|
|
|
|
|
|
|
Using Rank and NTile, but how do I...