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 |
|
pixelwiz
Starting Member
25 Posts |
 Posted - 2012-07-27 : 09:29:43
|
| Hello all, I know you really need some sample data and outputs to be able to help the best, but that's not easy to provide for this question, so I'm gonna try to explain it and hopefully someone can point me in the right direction.I'm working with a golf scoring system. There is a page that displays the results per group per hole, so you can see how many shots a player hit for each hole and if it was a birdie/bogey etc:http://publicscoring.pgalinks.net/leaderboards/lobby.cfm?eventid=5042&activeTab=groupsThere is also another way to display a similar output, instead of showing shots it shows the actual event score for each player after every hole: http://publicscoring.pgalinks.net/scoreboard/monsterboard.cfm?eventid=5042The way the second screen works right now is all the math is done at runtime, and it's ok when only 20 records are displayed. The math is a little complex because it depends on whether a player started on the first hole or on 10th hole on how the to-par value is displayed for each hole. So if it was a 10th hole start, reading from left to right between 9th and 10th might look a little strange...They just requested a similar output for the first screen, which can have up to 312 records to display. That's be a lot of math to do at run-time looping through every hole for every player. It might still work, but I'd much prefer to instead come up with an SQL solution to replace all instances of that.The way the To-Par value is calculated is the following:Start with the starting value, it'll be 0 if round 1, or prior round's finishing value. If on hole 1 and start hole is 1, then add shots and subtract par to get the new To-Par.If on hole 1 and start hole was 10, then need to take starting value plus shots minus par but also plus To-Par value for the 18th hole (that's really where I'm stuck, cause I don't have that value available). But every To-Par value is based on knowing what it was for the prior hole, and I don't quite see how to do it in SQL.The best solution probably would be to just store that value when I store the shots value because I know it at that moment, and it's something I could do for future events but I'd still have to populate the data for past events.Any suggestions? |
|
|
pixelwiz
Starting Member
25 Posts |
Posted - 2012-07-27 : 09:56:38
|
| I was just reading about Recursive Queries using Common Table Expressions, sounds like something that could help, but I have no idea how to actually use them yet... http://msdn.microsoft.com/en-us/library/ms186243%28v=sql.105%29.aspx |
 |
|
|
|
|
|
|
|
Conditional running total for unordered data