Recursive Golf to-par calculation

Question

Recursive Golf to-par calculation

Viewing 15 posts - 16 through 29 (of 29 total)

You must be logged in to reply to this topic. Login to reply

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 1

maybe I misunderstand what you are trying to accomplish....but surely all we need to consider is a running total of shots played in comparison against the running total of 'par" for the same number of holes....I dont see it matters on which hole the player starts....the result is after a fixed number of holes played for all players...be that 18/36 or 72.

suggest you search this site for "running sums/ totals"...plenty of options, some better than others ...but you aint really got a big data set to worry about.

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

Sean Lange SSC Guru Points: 286589 More actions · Answer 2

J Livingston SQL (7/27/2012)
Sean Lange (7/27/2012)
OK now we all have the same code to work from. Nice job posting readily consumable ddl and sample data. What I don't understand is what you want for output. What does this "to-par" number mean? Maybe you should just create a temp table and fill it with hard coded values along with an explanation of the calculation for your desired output. That way we not only gain some insight on what you want done but also we have a concrete target of what the correct output should look like.
maybe I misunderstand what you are trying to accomplish....but surely all we need to consider is a running total of shots played in comparison against the running total of 'par" for the same number of holes....I dont see it matters on which hole the player starts....the result is after a fixed number of holes played for all players...be that 18/36 or 72.
suggest you search this site for "running sums/ totals"...plenty of options, some better than others ...but you aint really got a big data set to worry about.

~~I was thinking running total too but then I am not sure that this isn't just a sum(par) - sum(shots)~~

I reread the description and it does seem like running total is the ticket here.

http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 3

Sean Lange (7/27/2012)
J Livingston SQL (7/27/2012)
Sean Lange (7/27/2012)
OK now we all have the same code to work from. Nice job posting readily consumable ddl and sample data. What I don't understand is what you want for output. What does this "to-par" number mean? Maybe you should just create a temp table and fill it with hard coded values along with an explanation of the calculation for your desired output. That way we not only gain some insight on what you want done but also we have a concrete target of what the correct output should look like.
maybe I misunderstand what you are trying to accomplish....but surely all we need to consider is a running total of shots played in comparison against the running total of 'par" for the same number of holes....I dont see it matters on which hole the player starts....the result is after a fixed number of holes played for all players...be that 18/36 or 72.
suggest you search this site for "running sums/ totals"...plenty of options, some better than others ...but you aint really got a big data set to worry about.
I was thinking running total too but then I am not sure that this isn't just a sum(par) - sum(shots)

could well be as simple as you suggest Sean....but we need OP to confirm.

a running total per player / number of holes played would enable a view to show "progression" throughout the match.....shouldnt be difficult to tie that back to which was the last hole played (irrespective of which hole the player started on)

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

pixelwiz Right there with Babe Points: 738 More actions · Answer 4

Ok, I've done running totals before, and I also thought it would be that or Recursive CTE.

But what I can't visualize in my head is how do I make it conditional based on what hole the player started at. These value must work during the event not just at the end. So if the player starts on 10, then it would just be hole 10, but if he started on 10 and now is on 3, then it would be 10 through 18 plus 1-3. But if he started on 1 and he's on 3 then it's just 1 through 3.

I can't come up with any approach that would be able to handle that, it's the starting on 10 and wrapping around to 1 that I can't figure out how to do. If they were all 1 through 18, that would be much simpler.

Sean Lange SSC Guru Points: 286589 More actions · Answer 5

Sean Lange (7/27/2012)
J Livingston SQL (7/27/2012)
Sean Lange (7/27/2012)
OK now we all have the same code to work from. Nice job posting readily consumable ddl and sample data. What I don't understand is what you want for output. What does this "to-par" number mean? Maybe you should just create a temp table and fill it with hard coded values along with an explanation of the calculation for your desired output. That way we not only gain some insight on what you want done but also we have a concrete target of what the correct output should look like.
maybe I misunderstand what you are trying to accomplish....but surely all we need to consider is a running total of shots played in comparison against the running total of 'par" for the same number of holes....I dont see it matters on which hole the player starts....the result is after a fixed number of holes played for all players...be that 18/36 or 72.
suggest you search this site for "running sums/ totals"...plenty of options, some better than others ...but you aint really got a big data set to worry about.
~~I was thinking running total too but then I am not sure that this isn't just a sum(par) - sum(shots)~~
I reread the description and it does seem like running total is the ticket here.
http://www.sqlservercentral.com/articles/T-SQL/68467/[/url]

Yeah I wasn't really thinking about "to par" as a progression. I was thinking about "to par" what does this golfer need to do to get to par. I think my simplistic idea is too simplistic.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

SQLRNNR SSC Guru Points: 281334 More actions · Answer 6

Sean Lange (7/27/2012)
Lynn Pettis (7/27/2012)
Sean Lange (7/27/2012)
Not sure what the issue with posting the code so I will help that. 😀
...
Not sure, Sean, but I know I had issues with some of the code I tried posting when I worked at the school district. Some code would post fine, others I had to post as attachments. The same code posted fine from home.
Yeah I know I have heard of that happening to some people from time to time and I don't think it has really ever been figured out why that happens. No biggie, that is why I just posted the contents so others that might want to help can do so a little easier.

Usually happens due to some proxy software or something at work like websense. I had websense blocking me from posting code for a while.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 7

pixelwiz (7/27/2012)
Ok, I've done running totals before, and I also thought it would be that or Recursive CTE.
But what I can't visualize in my head is how do I make it conditional based on what hole the player started at. These value must work during the event not just at the end. So if the player starts on 10, then it would just be hole 10, but if he started on 10 and now is on 3, then it would be 10 through 18 plus 1-3. But if he started on 1 and he's on 3 then it's just 1 through 3.
I can't come up with any approach that would be able to handle that, it's the starting on 10 and wrapping around to 1 that I can't figure out how to do. If they were all 1 through 18, that would be much simpler.

Hi pixelwiz....have you ever played golf?

it doesnt matter what hole you start on...its your total score after 18 holes (normal round of golf).....if you want to compare your score to "par" then you are either under/par/over for each hole...this is cumulative.

after 18 holes, every player convenes to the 19th hole...adds up their total number of shots and compares against the competitors......now if you want to get more complicated golf also has a "handicap system" and also rules for various games that use 3/4 handicaps etc.

....nevertheless...its either a running total by number of holes played...or a summary at end of each round

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

pixelwiz (7/27/2012)
Ok, I've done running totals before, and I also thought it would be that or Recursive CTE.
But what I can't visualize in my head is how do I make it conditional based on what hole the player started at. These value must work during the event not just at the end. So if the player starts on 10, then it would just be hole 10, but if he started on 10 and now is on 3, then it would be 10 through 18 plus 1-3. But if he started on 1 and he's on 3 then it's just 1 through 3.
I can't come up with any approach that would be able to handle that, it's the starting on 10 and wrapping around to 1 that I can't figure out how to do. If they were all 1 through 18, that would be much simpler.

Have you considered illustrating this in an SSRS report rather than straight TSQL?

It seems you are having issues with displaying the score in addition to tallying the score. Tallying the score doesn't matter which hole the player starts on since it is the round. Maybe you need to explain why this piece is important a little more.

But displaying the score for each hole for all players is a different matter and is greatly simplified in SSRS - unless I am missing your vision on it.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

pixelwiz Right there with Babe Points: 738 More actions · Answer 9

Hello J Livingston SQL,

No, I don't play golf, but I know a whole lot about properly scoring golf tournaments, because I'm in charge of building and running the online scoring system for The PGA of America.

We display leaderboards as play progresses during the tournament, and people from Golf Channel and CBS and NBC need to look at particularly designed score boards. I linked to two examples in my very first post when I asked the original question:

Groups screen:

http://publicscoring.pgalinks.net/leaderboards/lobby.cfm?eventid=5042&activeTab=groups

Monster Board:

http://publicscoring.pgalinks.net/scoreboard/monsterboard.cfm?eventid=5042

The groups screen displays the number of shots a player hit on each hole, and shows circles and squares to represent bogeys, birdies and doubles, etc.

Monster Boards shows the running topar score (that's what it's referred to, as in I'm 3 below par, or 4 over par) The way that's calculated and displayed varies depending on what hole the player started playing on. Right now this screen does all the logic using ColdFusion, which performs fine for 1-20 records. However, now I need to do it on the Groups screen that can have up to 312 records. It might still work that way, but I don't like the looping through each cell approach. I'd like to find a way to do it in SQL as a query and then just display the results. I think it would perform much faster that way.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

pixelwiz (7/27/2012)
Hello J Livingston SQL,
No, I don't play golf, but I know a whole lot about properly scoring golf tournaments, because I'm in charge of building and running the online scoring system for The PGA of America.
We display leaderboards as play progresses during the tournament, and people from Golf Channel and CBS and NBC need to look at particularly designed score boards. I linked to two examples in my very first post when I asked the original question:
Groups screen:
http://publicscoring.pgalinks.net/leaderboards/lobby.cfm?eventid=5042&activeTab=groups
Monster Board:
http://publicscoring.pgalinks.net/scoreboard/monsterboard.cfm?eventid=5042
The groups screen displays the number of shots a player hit on each hole, and shows circles and squares to represent bogeys, birdies and doubles, etc.
Monster Boards shows the running topar score (that's what it's referred to, as in I'm 3 below par, or 4 over par) The way that's calculated and displayed varies depending on what hole the player started playing on. Right now this screen does all the logic using ColdFusion, which performs fine for 1-20 records. However, now I need to do it on the Groups screen that can have up to 312 records. It might still work that way, but I don't like the looping through each cell approach. I'd like to find a way to do it in SQL as a query and then just display the results. I think it would perform much faster that way.

OK, I'm starting to better see your vision.

Did I miss the sample data posting?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

pixelwiz Right there with Babe Points: 738 More actions · Answer 11

Yes, there were two attachments in the earlier posts... Here they are

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 12

pixelwiz (7/27/2012)
Hello J Livingston SQL,
No, I don't play golf, but I know a whole lot about properly scoring golf tournaments, because I'm in charge of building and running the online scoring system for The PGA of America.

please accept my apologies pixelwiz....didnt follow your posted links to really try and understand your requirements :ermm:

please take a look at some code I have thrown together...not sure if its anywhere close to what you are after...and its not polished or finished..more work in progress.

it uses the Quirky update...but you may prefer an alternative approach.

It also relies on some form of incremental ID/ timestamp to sort the holes in order of play.

it may give you a few ideas...

--=== please note that this code is basically outling mythought process on how it could work

--=== it may have more steps than are necessary and no doubt can be tidied up and refactored

--=== think of it as work in progress ,grin>

USE [tempdb]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[HoleDetail]') AND type in (N'U'))

DROP TABLE [dbo].[HoleDetail]

GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Scores]') AND type in (N'U'))

DROP TABLE [dbo].[Scores]

GO

CREATE TABLE [dbo].[Scores](

[ID] [int] IDENTITY(1,1) NOT NULL,

[HoleID] [int] NOT NULL,

[PlayerID] [int] NOT NULL,

[RoundID] [int] NOT NULL,

[Score] [int] NOT NULL

) ON [PRIMARY]

GO

SET IDENTITY_INSERT [dbo].[Scores] ON;

INSERT INTO [dbo].[Scores]([ID], [HoleID], [PlayerID], [RoundID], [Score])

SELECT 96, 1, 1, 1, 3 UNION ALL

SELECT 97, 2, 1, 1, 5 UNION ALL

SELECT 98, 3, 1, 1, 5 UNION ALL

SELECT 99, 4, 1, 1, 3 UNION ALL

SELECT 100, 5, 1, 1, 4 UNION ALL

SELECT 101, 6, 1, 1, 4 UNION ALL

SELECT 102, 7, 1, 1, 5 UNION ALL

SELECT 103, 8, 1, 1, 4 UNION ALL

SELECT 104, 9, 1, 1, 4 UNION ALL

SELECT 105, 10, 1, 1, 3 UNION ALL

SELECT 106, 11, 1, 1, 4 UNION ALL

SELECT 107, 12, 1, 1, 5 UNION ALL

SELECT 109, 13, 1, 1, 4 UNION ALL

SELECT 111, 14, 1, 1, 4 UNION ALL

SELECT 113, 15, 1, 1, 3 UNION ALL

SELECT 115, 16, 1, 1, 5 UNION ALL

SELECT 117, 17, 1, 1, 4 UNION ALL

SELECT 118, 18, 1, 1, 4 UNION ALL

SELECT 132, 1, 1, 2, 3 UNION ALL

SELECT 135, 2, 1, 2, 4 UNION ALL

SELECT 137, 3, 1, 2, 4 UNION ALL

SELECT 138, 4, 1, 2, 4 UNION ALL

SELECT 140, 5, 1, 2, 4 UNION ALL

SELECT 142, 6, 1, 2, 4 UNION ALL

SELECT 143, 7, 1, 2, 5 UNION ALL

SELECT 108, 1, 2, 1, 3 UNION ALL

SELECT 110, 2, 2, 1, 4 UNION ALL

SELECT 112, 3, 2, 1, 4 UNION ALL

SELECT 114, 4, 2, 1, 4 UNION ALL

SELECT 116, 5, 2, 1, 5 UNION ALL

SELECT 119, 6, 2, 1, 3 UNION ALL

SELECT 120, 7, 2, 1, 4 UNION ALL

SELECT 121, 8, 2, 1, 4 UNION ALL

SELECT 122, 9, 2, 1, 4 UNION ALL

SELECT 123, 10, 2, 1, 4 UNION ALL

SELECT 124, 11, 2, 1, 4 UNION ALL

SELECT 125, 12, 2, 1, 4 UNION ALL

SELECT 126, 13, 2, 1, 3 UNION ALL

SELECT 127, 14, 2, 1, 4 UNION ALL

SELECT 128, 15, 2, 1, 3 UNION ALL

SELECT 129, 16, 2, 1, 5 UNION ALL

SELECT 130, 17, 2, 1, 3 UNION ALL

SELECT 131, 18, 2, 1, 4 UNION ALL

SELECT 133, 10, 2, 2, 3 UNION ALL

SELECT 134, 11, 2, 2, 5 UNION ALL

SELECT 136, 12, 2, 2, 3 UNION ALL

SELECT 139, 13, 2, 2, 3 UNION ALL

SELECT 141, 14, 2, 2, 5

GO

SET IDENTITY_INSERT [dbo].[Scores] OFF;

CREATE TABLE [dbo].[HoleDetail](

[HoleID] [int] NOT NULL,

[Par] [int] NULL

) ON [PRIMARY]

GO

INSERT INTO [dbo].[HoleDetail]([HoleID], [Par])

SELECT 1, 4 UNION ALL

SELECT 2, 4 UNION ALL

SELECT 3, 3 UNION ALL

SELECT 4, 5 UNION ALL

SELECT 5, 4 UNION ALL

SELECT 6, 4 UNION ALL

SELECT 7, 4 UNION ALL

SELECT 8, 3 UNION ALL

SELECT 9, 4 UNION ALL

SELECT 10, 3 UNION ALL

SELECT 11, 5 UNION ALL

SELECT 12, 3 UNION ALL

SELECT 13, 5 UNION ALL

SELECT 14, 4 UNION ALL

SELECT 15, 3 UNION ALL

SELECT 16, 5 UNION ALL

SELECT 17, 4 UNION ALL

SELECT 18, 5

SELECT Scores.ID ,

Scores.HoleID ,

Scores.PlayerID ,

Scores.RoundID ,

Scores.Score ,

HoleDetail.Par ,

Scores.Score - HoleDetail.Par AS ToPar_Hole ,

0 AS RunningScore ,

0 AS RunningPar ,

0 AS ToPar_Match

INTO #scores

FROM

Scores INNER JOIN HoleDetail ON Scores.HoleID = HoleDetail.HoleID;

--=== as an idea to negate the problem of who started on which hole, I am assuming that some form of unique ID / timestamp etc

--=== will be used when scores are entered.....this then forms part of the clustered index below

--=== following shows entry of results

SELECT [ID]

,[HoleID]

,[PlayerID]

,[RoundID]

,[Score]

FROM [playpit].[dbo].[Scores]

order by ID

--=== the index is required for QU (quirky update)

CREATE UNIQUE CLUSTERED INDEX CIX ON dbo.#Scores( PlayerID ASC , ID ASC )ON [PRIMARY];

GO

--==== QU below

DECLARE @SafetyCounter BIGINT,

@PlayerID INT,

@RunningScore INT,

@RunningPar INT

;

SELECT @SafetyCounter = 1 -- assume there's at least one row at this point.

;

WITH

cte1 AS

(

SELECT SafetyCounter = ROW_NUMBER() OVER (ORDER BY PlayerID, ID),

PlayerID, RoundId, HoleId, Score,RunningScore, Par, RunningPar

FROM #Scores

)

UPDATE cte

SET @RunningScore

= Runningscore

= CASE

WHEN SafetyCounter = @SafetyCounter --Makes sure we're working on the correct row.

THEN CASE

WHEN PlayerId = @Playerid

THEN @RunningScore + score

ELSE score

END

ELSE 1/0 --Forces error we get out of sync.

END,

@Runningpar

= RunningPar

= CASE

WHEN SafetyCounter = @SafetyCounter

THEN CASE

WHEN PlayerId = @Playerid

THEN @Runningpar + par

ELSE par

END

ELSE 1/0

END,

@PlayerID = PlayerId,

@SafetyCounter = @SafetyCounter + 1

FROM cte1 cte

OPTION (MAXDOP 1)

;

UPDATE #Scores

SET ToPar_Match = RunningScore - RunningPar

go

SELECT PlayerID,

cast( MAX(CASE WHEN HoleID=1 then CAST(ToPar_Match AS VARCHAR(3)) else '-' end ) AS VARCHAR (3)) AS HOLE1,

cast( MAX(CASE WHEN HoleID=2 then CAST(ToPar_Match AS VARCHAR(3)) else '-' end ) AS VARCHAR (3)) AS HOLE2,

cast( MAX(CASE WHEN HoleID=3 then CAST(ToPar_Match AS VARCHAR(3)) else '-' end ) AS VARCHAR (3)) AS HOLE3,

cast( MAX(CASE WHEN HoleID=4 then CAST(ToPar_Match AS VARCHAR(3)) else '-' end ) AS VARCHAR (3)) AS HOLE4,

cast( MAX(CASE WHEN HoleID=5 then CAST(ToPar_Match AS VARCHAR(3)) else '-' end ) AS VARCHAR (3)) AS HOLE5,

cast( MAX(CASE WHEN HoleID=6 then CAST(ToPar_Match AS VARCHAR(3)) else '-' end ) AS VARCHAR (3)) AS HOLE6,

cast( MAX(CASE WHEN HoleID=7 then CAST(ToPar_Match AS VARCHAR(3)) else '-' end ) AS VARCHAR (3)) AS HOLE7,

cast( MAX(CASE WHEN HoleID=8 then CAST(ToPar_Match AS VARCHAR(3)) else '-' end ) AS VARCHAR (3)) AS HOLE8,

cast( MAX(CASE WHEN HoleID=9 then CAST(ToPar_Match AS VARCHAR(3)) else '-' end ) AS VARCHAR (3)) AS HOLE9,

cast( MAX(CASE WHEN HoleID=10 then CAST(ToPar_Match AS VARCHAR(3)) else '-' end ) AS VARCHAR (3)) AS HOLE10,

cast( MAX(CASE WHEN HoleID=11 then CAST(ToPar_Match AS VARCHAR(3)) else '-' end ) AS VARCHAR (3)) AS HOLE11,

cast( MAX(CASE WHEN HoleID=12 then CAST(ToPar_Match AS VARCHAR(3)) else '-' end ) AS VARCHAR (3)) AS HOLE12,

cast( MAX(CASE WHEN HoleID=13 then CAST(ToPar_Match AS VARCHAR(3)) else '-' end ) AS VARCHAR (3)) AS HOLE13,

cast( MAX(CASE WHEN HoleID=14 then CAST(ToPar_Match AS VARCHAR(3)) else '-' end ) AS VARCHAR (3)) AS HOLE14,

cast( MAX(CASE WHEN HoleID=15 then CAST(ToPar_Match AS VARCHAR(3)) else '-' end ) AS VARCHAR (3)) AS HOLE15,

cast( MAX(CASE WHEN HoleID=16 then CAST(ToPar_Match AS VARCHAR(3)) else '-' end ) AS VARCHAR (3)) AS HOLE16,

cast( MAX(CASE WHEN HoleID=17 then CAST(ToPar_Match AS VARCHAR(3)) else '-' end ) AS VARCHAR (3)) AS HOLE17,

cast( MAX(CASE WHEN HoleID=18 then CAST(ToPar_Match AS VARCHAR(3)) else '-' end ) AS VARCHAR (3)) AS HOLE18

FROM #scores

WHERE (RoundID = 2)

GROUP BY PlayerID

--=== BOTH ROUNDS

SELECT PlayerID, RoundiD,