Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

sql stored procedure to get golf leaderboard

Posted on 2009-05-19
15
Medium Priority
?
555 Views
Last Modified: 2012-05-07
I received some help here that got me started down this path.  I need some more help.  I am trying to print out a golf leaderboard calculated from my SQL database.  Below is my stored procedure, the output I get with it and then the output on how I actually want it to look.  If there is a tie the position should show a T before the position number and it should be the same for all players with the same score as shown below.

The scoresbygame view sums up the scores for each game and groups by the playerid.

Here is my stored procedure using what you provided:
Select
      CASE
            WHEN COUNT(Scoretotal) > 1 THEN 'T'
            ELSE ''
      END
            + CAST(position as varchar(5)) as [position], [Scoretotal]
FROM scoresbygame
WHERE gameid = 110
GROUP BY position, scoretotal
ORDER BY scoretotal

This is the output:

position  Scoretotal  
------        -----------
1               37
2               37
3               53
4               61
5               62
6               62
7               62
8               65

It should look like this:

position Scoretotal  
------      -----------
T1            37
T1            37
3              53
4              61
T5            62
T5            62
T5            62
8              65

Also, I need to get the position strictly from the order of scoretotal?

Any more help will be appreciated.
0
Comment
Question by:dgboyd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
15 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24423887
Hope this helps
select position, scoretotal,
CASE WHEN cnt > 1 THEN 'T' + CAST(position as varchar(5))
         ELSE CAST(position as varchar(5))
END position
from (
Select position, scoretotal, count(*) over ( order b scoretotal) cnt
FROM scoresbygame
WHERE gameid = 110 ) temp

Open in new window

0
 
LVL 3

Author Comment

by:dgboyd
ID: 24424075
I'm running SQL Server 7.0 and can't use the 'over' keyword.  Can you write this for 7.0?

Thanks
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24428031
Since SQL Server 2005 is mentioned in the zone, I provided that 2005 syntax.
This one should help you out:
select position, scoretotal,
CASE WHEN cnt > 1 THEN 'T' + CAST(position as varchar(5))
         ELSE CAST(position as varchar(5))
END position
from (
Select position, scoretotal, count(*) cnt
FROM scoresbygame
WHERE gameid = 110 
GROUP BY position, scoretotal) temp
ORDER BY scoretotal

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 3

Author Comment

by:dgboyd
ID: 24430615
Thanks.  I am going to be traveling on business for the next two days.  When I get back, I am going to upgrade to 2005 and try this.  I will post back here in a few days.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24430923
dgboyd,
   Even for SQL Server 2005 use the query in comment 24428031 because I missed Grouping and Ordering in the Earlier query provided or an equivalent 2005 query below
select position, scoretotal,
CASE WHEN cnt > 1 THEN 'T' + CAST(position as varchar(5))
         ELSE CAST(position as varchar(5))
END position
from (
Select position, scoretotal, count(*) over ( partition by position, scoretotal) cnt
FROM scoresbygame
WHERE gameid = 110 
group by position, scoretotal) temp
order by scoretotal

Open in new window

0
 
LVL 3

Author Comment

by:dgboyd
ID: 24442394
Got the upgrade to 2005 successful.  

I added your last request and it completes, but there is no output.  Is there also a way to leave position field out and have it add the position dynamically in the output?  I won't have the position field in the database, because I want to calculate it each time the procedure is run.

Thanks
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24443112
Hope this is what you expect
select scoretotal,
CASE WHEN cnt > 1 THEN 'T' + CAST(position as varchar(5))
         ELSE CAST(position as varchar(5))
END position
from (
Select position, scoretotal, count(*) over ( partition by position, scoretotal) cnt
FROM scoresbygame
WHERE gameid = 110 
group by position, scoretotal) temp
order by scoretotal

Open in new window

0
 
LVL 3

Author Comment

by:dgboyd
ID: 24443992
I am getting results back, but they look like this:

Score     Position
64      1
64      2
67      3
95      4

They should look like this:

Score     Position
64      T1
64      T1
67      3
95      4

I want to do away with the position field and have the procedure calculate the position using 'count' or something like that.  

If I change cnt = 1 from cnt > 1, then I do get a T in front of every position.

Thanks again for your help here.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24449388
A minor mistake and this should help
select scoretotal,
CASE WHEN cnt > 1 THEN 'T' + CAST(position as varchar(5))
         ELSE CAST(position as varchar(5))
END position
from (
Select position, scoretotal, count(*) over ( partition by scoretotal) cnt
FROM scoresbygame
WHERE gameid = 110 
group by position, scoretotal) temp
order by scoretotal

Open in new window

0
 
LVL 3

Author Comment

by:dgboyd
ID: 24451169
Here's what I get now with the position field still in the query:

65      T1
65      T2
65      T3
95      7
97      T4
97      T5
102      6

When I pull the position field out of the view the stored procedure is querying, I get an invalid column name 'Position' error.  
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24466479
<< When I pull the position field out of the view the stored procedure is querying >>

I am not obtaining this part of your requirement, Can you kindly explain more.
0
 
LVL 3

Author Comment

by:dgboyd
ID: 24466816
The only fields in the scoresbygame view are the scoretotal field and playerid.  The scoretotal is simply a sum of each players scores for a particular game.  The view orders the scoretotal from low to high.  This way I can display the players and their scores by position.  

I would like the stored procedure to return the position and scoretotal of each player.  If player one and two are tied, their position should be T1 and T1 which means they are tied for first.  The next player will actually be in third and his position should be 3 - assuming he is not tied with the fourth player.  Like this:

Pos    Score
T1        78
T1        78
3          79
4          80
T5        81
T5        81

Over the weekend I was able to get these results using an array in vbscript by querying the scoresbygame view, but, I would rather have a stored procedure do this.  I'm not even sure if this can be done in a stored procedure, or if I have to create the array of scores and send the array to the stored procedure.  

I hope this explains it a little more.

Do you think this can be accomplished in a stored procedure?
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 2000 total points
ID: 24467774
Hope I got your requirement correct this time and try the query below:
select scoretotal,
CASE WHEN cnt > 1 THEN 'T' + CAST(rnk as varchar(5))
         ELSE CAST(rnk as varchar(5))
END position
from (
Select scoretotal, rank() over ( order by scoretotal) rnk, count(*) over ( partition by scoretotal) cnt
FROM scoresbygame
WHERE gameid = 110 ) temp
order by scoretotal

Open in new window

0
 
LVL 3

Author Closing Comment

by:dgboyd
ID: 31585129
You nailed it!  Works perfectly!  This took me about 60 lines of code to get it working in vbscript and you did it in 9!

I love it.  I was sure hoping this could be done in a stored procedure.  

I sure appreciate you sticking with it.  This helps me a bunch!
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 24470880
Sure.. You can also achieve this one using a procedure.
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question