July 27, 2012 at 8:39 am
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=groups
There 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=5042
The 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.
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
Any suggestions?
July 27, 2012 at 9:01 am
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
That pretty much sums up the issue here. Look at your post and ask yourself if you would be able to provide any help based on the explanation. There is nothing concrete here to work with.
_______________________________________________________________
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/
July 27, 2012 at 9:25 am
Sean's nailed it. You've got data that you can see and which you have already queried in an attempt to get the results - we can't see it, we can only guess.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 27, 2012 at 9:35 am
Hi Sean, you're right. I know. It's just been driving me nuts for 2 days and I can't even figure out how to explain it right.
I'd need to do it for up to 312 players, and accounting for prior rounds, I'm gonna try to create a real simple example with 1 or 2 players. But is there any way for me to actually give you the data for the tables I'm using in the bigger query so I don't have to write insert statements for all of it?
July 27, 2012 at 9:39 am
pixelwiz (7/27/2012)
Hi Sean, you're right. I know. It's just been driving me nuts for 2 days and I can't even figure out how to explain it right.I'd need to do it for up to 312 players, and accounting for prior rounds, I'm gonna try to create a real simple example with 1 or 2 players. But is there any way for me to actually give you the data for the tables I'm using in the bigger query so I don't have to write insert statements for all of it?
IIRC Article 61537 which Sean and I both link to in our sigs shows you how to extract data from your tables with minimum effort. Well worth a read.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 27, 2012 at 9:56 am
pixelwiz (7/27/2012)
Hi Sean, you're right. I know. It's just been driving me nuts for 2 days and I can't even figure out how to explain it right.I'd need to do it for up to 312 players, and accounting for prior rounds, I'm gonna try to create a real simple example with 1 or 2 players. But is there any way for me to actually give you the data for the tables I'm using in the bigger query so I don't have to write insert statements for all of it?
Right click the database in Object Explorer. Must be the database and not tables etc.
Tasks -> Generate Scripts.
Select the object(s) you want to script.
On the Set Scripting Objects page you need to click the advanced button. Then scroll down to the last item in the General section called "Types of data to script". This will likely say Schema Only. Change that to Schema and Data. Voila! You will get ddl and data for every object you chose in the first step.
_______________________________________________________________
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/
July 27, 2012 at 10:54 am
This thing is not letting me post the reply with code, what's going on?
July 27, 2012 at 10:57 am
Keeps saying connection was recet every time I try to paste the code in here, even with the code brackets
July 27, 2012 at 10:58 am
Ok, please see the attachment, it won't let me paste it here
July 27, 2012 at 12:41 pm
Not sure what the issue with posting the code so I will help that. 😀
CREATE TABLE [dbo].[tempholes](
[id] [int] IDENTITY(1,1) NOT NULL,
[number] [int] NOT NULL,
[par] [int] NOT NULL,
CONSTRAINT [PK_tempholes] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
----------------------
-----------
CREATE TABLE [dbo].[tempplayerholes](
[id] [int] IDENTITY(1,1) NOT NULL,
[hole_id] [int] NOT NULL,
[player_id] [int] NOT NULL,
[shots] [int] NOT NULL,
CONSTRAINT [PK_tempplayerholes] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
----------------------
-----------
CREATE TABLE [dbo].[tempstarts](
[id] [int] IDENTITY(1,1) NOT NULL,
[player_id] [int] NOT NULL,
[start_hole] [int] NOT NULL,
CONSTRAINT [PK_tempstarts] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
----------------------
---------------------------
INSERT INTO tempholes (number, par)
VALUES (1,3),
(2,3),
(3,4),
(4,5),
(5,3),
(6,4),
(7,5),
(8,3),
(9,4),
(10,5),
(11,3),
(12,3),
(13,4),
(14,4),
(15,5),
(16,3),
(17,4),
(18,3)
---------------------
---------------------------
INSERT INTO tempplayerholes (hole_id, player_id, shots)
VALUES (1,1,4),
(2,1,4),
(3,1,4),
(4,1,4),
(5,1,4),
(6,1,4),
(7,1,4),
(8,1,4),
(9,1,4),
(10,1,4),
(11,1,4),
(12,1,4),
(13,1,4),
(14,1,4),
(15,1,4),
(16,1,4),
(17,1,4),
(18,1,4),
(1,2,3),
(2,2,4),
(3,2,5),
(4,2,4),
(5,2,3),
(6,2,4),
(7,2,5),
(8,2,4),
(9,2,3),
(10,2,4),
(11,2,5),
(12,2,5),
(13,2,4),
(14,2,4),
(15,2,3),
(16,2,4),
(17,2,7),
(18,2,4)
---------------------
---------------------------
INSERT INTO tempstarts (player_id, start_hole)
VALUES (1,1),
(2,10)
---------------------
Ok, now the inner query to get the core data is
SELECT h.id hole_id, h.number hole_number, h.par, ph.player_id, ph.shots, ts.start_hole
FROM tempstarts ts
INNER JOIN tempplayerholes ph
ON ts.player_id = ph.player_id
INNER JOIN tempholes h
ON ph.hole_id = h.id
However, for some reason to get it to work with the Pivot correctly, I have to remove the hole_id and par from the select list...
SELECT player_id,
start_hole,
[1] AS h1,
[2] AS h2,
[3] AS h3,
[4] AS h4,
[5] AS h5,
[6] AS h6,
[7] AS h7,
[8] AS h8,
[9] AS h9,
[10] AS h10,
[11] AS h11,
[12] AS h12,
[13] AS h13,
[14] AS h14,
[15] AS h15,
[16] AS h16,
[17] AS h17,
[18] AS h18
FROM
(
SELECT h.number hole_number,ph.player_id, ph.shots, ts.start_hole
FROM tempstarts ts
INNER JOIN tempplayerholes ph
ON ts.player_id = ph.player_id
INNER JOIN tempholes h
ON ph.hole_id = h.id
) dataTable
PIVOT ( Max(shots)
FOR hole_number IN ([1],
[2],
[3],
[4],
[5],
[6],
[7],
[8],
[9],
[10],
[11],
[12],
[13],
[14],
[15],
[16],
[17],
[18]) ) pivotTable
So what I need instead is the to-par value for each hole, which is effected by the start hole.
_______________________________________________________________
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/
July 27, 2012 at 12:43 pm
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.
_______________________________________________________________
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/
July 27, 2012 at 12:55 pm
Ok, I'll send the table shortly, but here is the explanation.
Let's pretend there is only 1 round (in reality there will be 4, so I'll need a way to get the start value for later rounds)
Also, let's first assume the player starts on 1st Tee.
So to calculate ToPar value for first hole it's StartValue(0) + Player Shots for first player (4) - Par(3) = 1
Second hole would be StartValue(1) + Player Shots for first player (4) - Par(3) = 2
Third hole would be StartValue(2) + Player Shots for first player (4) - Par(4) = 2
And so on till 18th hole.
To make it even more complex, if the player started on 10th Tee, then the 10th hole ToPar value would be calculated as above, but for first hole you'd have to start with 18th ToPar value add Shots for 1st and subtract par for 1st, and so on through 9th.
Hopefully that makes sense, I'll send sample output as quickly as I can create it. Thanks.
July 27, 2012 at 12:57 pm
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.
July 27, 2012 at 1:06 pm
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.
_______________________________________________________________
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/
July 27, 2012 at 1:11 pm
Ok, attached are the queries to create the sample output table.
Thanks for any guidance on how to get there
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply