July 23, 2012 at 12:54 am
Hi I have an flat tabel Like:
MatchresultRoundResult1, MatchResultRoundResult2 and so on to MatchResultRoundResult8
MatchResultRankPoint1 to MatchResultRankPoint8.
MatchResultTable1 to MatchResultTable8
MatchResultRound1 to MatchResultRound8
MatchResultRoundNbr1 to MatchResultRoundNbr8
This Field with 1 to 8 i want o get in other direction row by row not in an singel row
but i dont want rows with no result.
later on in an new Result tabel
Se example:
Best regards
Gert Lindholm
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[bo_MatchResultTest](
[MatchResultMatchId] [int] NOT NULL,
[MatchResultLicNbr] [nchar](20) NOT NULL,
[MatchResultTeamId] [int] NOT NULL,
[MatchResultShirtNo] [int] NOT NULL,
[MatchResultRoundResult1] [int] NOT NULL,
[MatchResultRoundResult2] [int] NOT NULL,
[MatchResultRoundResult3] [int] NOT NULL,
[MatchResultRoundResult4] [int] NOT NULL,
[MatchResultRoundResult5] [int] NOT NULL,
[MatchResultRoundResult6] [int] NOT NULL,
[MatchResultRoundResult7] [int] NOT NULL,
[MatchResultRoundResult8] [int] NOT NULL,
[MatchResultHomeOrAwayTeam] [char](1) NOT NULL,
[MatchResultPlace] [int] NOT NULL,
[MatchResultRankPoint1] [int] NOT NULL,
[MatchResultRankPoint2] [int] NOT NULL,
[MatchResultRankPoint3] [int] NOT NULL,
[MatchResultRankPoint4] [int] NOT NULL,
[MatchResultRankPoint5] [int] NOT NULL,
[MatchResultRankPoint6] [int] NOT NULL,
[MatchResultRankPoint7] [int] NOT NULL,
[MatchResultRankPoint8] [int] NOT NULL,
[MatchResultHcp] [int] NOT NULL,
[MatchResultTable1] [int] NOT NULL,
[MatchResultTable2] [int] NOT NULL,
[MatchResultTable3] [int] NOT NULL,
[MatchResultTable4] [int] NOT NULL,
[MatchResultTable5] [int] NOT NULL,
[MatchResultTable6] [int] NOT NULL,
[MatchResultTable7] [int] NOT NULL,
[MatchResultTable8] [int] NOT NULL,
[MatchResultRound1] [int] NOT NULL,
[MatchResultRound2] [int] NOT NULL,
[MatchResultRound3] [int] NOT NULL,
[MatchResultRound4] [int] NOT NULL,
[MatchResultRound5] [int] NOT NULL,
[MatchResultRound6] [int] NOT NULL,
[MatchResultRound7] [int] NOT NULL,
[MatchResultRound8] [int] NOT NULL,
[MatchResultRoundNbr1] [int] NOT NULL,
[MatchResultRoundNbr2] [int] NOT NULL,
[MatchResultRoundNbr3] [int] NOT NULL,
[MatchResultRoundNbr4] [int] NOT NULL,
[MatchResultRoundNbr5] [int] NOT NULL,
[MatchResultRoundNbr6] [int] NOT NULL,
[MatchResultRoundNbr7] [int] NOT NULL,
[MatchResultRoundNbr8] [int] NOT NULL
CONSTRAINT [PK_bo_MatchResultTest] PRIMARY KEY CLUSTERED
(
[MatchResultMatchId] ASC,
[MatchResultLicNbr] ASC,
[MatchResultTeamId] 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
SET ANSI_PADDING OFF
GO
INSERT [dbo].[bo_MatchResultTest]
(
[MatchResultMatchId]
, [MatchResultLicNbr]
, [MatchResultTeamId]
, [MatchResultShirtNo]
, [MatchResultRoundResult1]
, [MatchResultRoundResult2]
, [MatchResultRoundResult3]
, [MatchResultRoundResult4]
, [MatchResultRoundResult5]
, [MatchResultRoundResult6]
, [MatchResultRoundResult7]
, [MatchResultRoundResult8]
, [MatchResultHomeOrAwayTeam]
, [MatchResultPlace]
, [MatchResultRankPoint1]
, [MatchResultRankPoint2]
, [MatchResultRankPoint3]
, [MatchResultRankPoint4]
, [MatchResultRankPoint5]
, [MatchResultRankPoint6]
, [MatchResultRankPoint7]
, [MatchResultRankPoint8]
, [MatchResultHcp]
, [MatchResultTable1]
, [MatchResultTable2]
, [MatchResultTable3]
, [MatchResultTable4]
, [MatchResultTable5]
, [MatchResultTable6]
, [MatchResultTable7]
, [MatchResultTable8]
, [MatchResultRound1]
, [MatchResultRound2]
, [MatchResultRound3]
, [MatchResultRound4]
, [MatchResultRound5]
, [MatchResultRound6]
, [MatchResultRound7]
, [MatchResultRound8]
, [MatchResultRoundNbr1]
, [MatchResultRoundNbr2]
, [MatchResultRoundNbr3]
, [MatchResultRoundNbr4]
, [MatchResultRoundNbr5]
, [MatchResultRoundNbr6]
, [MatchResultRoundNbr7]
, [MatchResultRoundNbr8]
)
VALUES
(
3017864
, N'M180360GER01'
, 90577
, 6
, 200
, 232
, 182
, 182
, 0
, 0
, 0
, 0
, N'B'
, 9
, 0
, 1
, 1
, 0
, 0
, 0
, 0
, 0
, 0
, 1
, 2
, 3
, 4
, 0
, 0
, 0
, 0
, 3
, 2
, 4
, 1
, 0
, 0
, 0
, 0
, 2
, 2
, 2
, 2
, 0
, 0
, 0
, 0
)
GO
SELECT * FROM bo_MatchResultTest;
SELECT DISTINCT MatchResultMatchId
, MatchResultLicNbr
, MatchResultShirtNo
, CONVERT(VARCHAR(12), MatchResultRoundResult) [Round]
, Result
FROM
(
SELECT MatchResultMatchId
, MatchResultLicNbr
, MatchResultShirtNo
, MatchResultRoundResult1 [1]
, MatchResultRoundResult2 [2]
, MatchResultRoundResult3 [3]
, MatchResultRoundResult4 [4]
, MatchResultRoundResult5 [5]
, MatchResultRoundResult6 [6]
, MatchResultRoundResult7 [7]
, MatchResultRoundResult8 [8]
, MatchResultRankPoint1 [rp1]
, MatchResultRankPoint2 [rp2]
, MatchResultRankPoint3 [rp3]
, MatchResultRankPoint4 [rp4]
, MatchResultRankPoint5 [rp5]
, MatchResultRankPoint6 [rp6]
, MatchResultRankPoint7 [rp7]
, MatchResultRankPoint8 [rp8]
FROM bo_MatchResultTest
) mr
UNPIVOT
(
Result
FOR
MatchResultRoundResult
IN
(
[1], [2], [3], [4], [5], [6], [7], [8]
)
) as mresult
July 23, 2012 at 2:24 am
Do you mean something like this?
SELECT MatchResultMatchId
, MatchResultLicNbr
, MatchResultShirtNo
, CONVERT(VARCHAR(12), [Round]) [Round]
, Result
FROM dbo.bo_MatchResultTest
CROSS APPLY (
VALUES (1, MatchResultRoundResult1)
,(2, MatchResultRoundResult2)
,(3, MatchResultRoundResult3)
,(4, MatchResultRoundResult4)
,(5, MatchResultRoundResult5)
,(6, MatchResultRoundResult6)
,(7, MatchResultRoundResult7)
,(8, MatchResultRoundResult8)) a ([Round], Result)
WHERE Result <> 0
Note that I despise the UNPIVOT syntax so I have converted it to using the CROSS APPLY VALUES approach. You may even find this to be more efficient on many rows.
I am not certain whether distinct is required either so I removed that as well (for a slight performance improvement).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 23, 2012 at 5:31 am
Dwain Thanks a lot.:-P:cool:
July 23, 2012 at 6:25 am
Gert - You're most welcome.
I have a brother named Gert, although he is loathe to use that name. He uses his middle name instead.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 23, 2012 at 1:22 pm
Hi again Dwain
When i was yunger i also loathe my name.
But older i get i get used to it.
My middle name is Erik.
And i´m named by an old canoeist or rowsers named Gert Fredriksson, who is an old Olympic Champion backin the 50's
Best regards
Gert
July 23, 2012 at 9:52 pm
gert.lindholm (7/23/2012)
Hi again DwainWhen i was yunger i also loathe my name.
But older i get i get used to it.
My middle name is Erik.
And i´m named by an old canoeist or rowsers named Gert Fredriksson, who is an old Olympic Champion backin the 50's
Best regards
Gert
My brother's like 66 now so I don't think he's gonna change any time soon.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 24, 2012 at 4:57 pm
Hello again Dwain
Is ther an other or better way for CURSOR
What i mean is when for ex. i want to select from one tabel and list one post
for players in an game of Bowling we have some MatchHead information.
example In my League we have 192 matches, every match contains atleast 16 Results.
These Matchresult for each player, i wana put in two tables.
Res_Result and res_ResultDetails
However the old data is in one flat table like the one you helped med with Cross Join.
So for each Match i got 16 to 18 seperate results
These 16 to 18 seperate results has played from 1 to 4 Rounds which I wana save to row instead of flat table.
Best regards in advance
Gert
INSERT INTO Res_Result
(
[ResultType]
, [ResultLicNbr]
, [ResultMatch_CompId]
, [ResultClassId]
, [ResultSquad_RoundId]
, [ResultNbrOfSeries]
, [ResultTotResult]
, [ResultRPPoints]
, [ResultLanePoints]
, [ResultHcp]
, [ResultPlaceTot]
, [ResultPlaceSquad]
, [ResultDate]
, [ResultTeamId]
, [ResultHomeOrAwayTeam]
)
SELECT
1 Resulttype
, MatchResultLicNbr
, MatchResultMatchId
, 0 ResultClassId
, m.MatchRoundId ResultSquad_RoundId
, SUM(
CASE
WHEN MatchResultRoundResult1 > 0
THEN
1
ELSE
0
END
+ CASE
WHEN MatchResultRoundResult2 > 0
THEN
1
ELSE
0
END
+ CASE
WHEN MatchResultRoundResult3 > 0
THEN
1
ELSE
0
END
+ CASE
WHEN MatchResultRoundResult4 > 0
THEN
1
ELSE
0
END
+ CASE
WHEN MatchResultRoundResult5 > 0
THEN
1
ELSE
0
END
+ CASE
WHEN MatchResultRoundResult6 > 0
THEN
1
ELSE
0
END
+ CASE
WHEN MatchResultRoundResult7 > 0
THEN
1
ELSE
0
END
+ CASE
WHEN MatchResultRoundResult8 > 0
THEN
1
ELSE
0
END
) AS MatchRounds
, SUM
(
MatchResultRoundResult1
+ MatchResultRoundResult2
+ MatchResultRoundResult3
+ MatchResultRoundResult4
+ MatchResultRoundResult5
+ MatchResultRoundResult6
+ MatchResultRoundResult7
+ MatchResultRoundResult8
) AS MatchResult
, SUM
(
MatchResultRankPoint1
+ MatchResultRankPoint2
+ MatchResultRankPoint3
+ MatchResultRankPoint4
+ MatchResultRankPoint5
+ MatchResultRankPoint6
+ MatchResultRankPoint7
+ MatchResultRankPoint8
) AS MatchLanePoints
, ISNULL(r.RankPoints, 0 ) RankPoints
, mr.MatchResultHcp
, mr.MatchResultPlace
, 0 ResultPlaceSquad
, m.MatchDate
, CASE WHEN mr.MatchResultHomeOrAwayTeam = 'H'
THEN
m.MatchHomeTeamId
ELSE
m.MatchAwayTeamId
END ResultTeamId
, mr.MatchResultHomeOrAwayTeam
FROM bo_MatchResult mr
JOIN bo_Match m
ON mr.MatchResultMatchId = m. MatchId
LEFT OUTER JOIN bo_Cup c
ON c.CupDivisionId = m.MatchDivisionId
AND c.CupSeasonId = m.MatchSeason
LEFT OUTER JOIN bo_LeagueSeasonLevelDivision lsld
ON lsld.LeagueSeasonLevelDivisionDivisionId = m.MatchDivisionId
AND lsld.LeagueSeasonLevelDivisionSeasonId = m.MatchSeason
LEFT OUTER JOIN bo_LeagueSeasonLevel lsl
ON (
(lsl.LeagueSeasonLevelLeagueId = lsld.LeagueSeasonLevelDivisionLeagueId
AND lsl.LeagueSeasonLevelSeasonId = lsld.LeagueSeasonLevelDivisionSeasonId
AND lsl.LeagueSeasonLevelLevelId = lsld.LeagueSeasonLevelDivisionLevelId
)
OR
(LeagueSeasonLevelLeagueId = c.CupLeagueId
AND lsl.LeagueSeasonLevelSeasonId = c.CupSeasonId
AND lsl.LeagueSeasonLevelLevelId = c.CupLevelId1
)
)
LEFT OUTER JOIN bo_RankType rt
ON rt.RankTypeType = LeagueSeasonLevelRankType
AND rt.RankTypePurpose = 2
LEFT OUTER JOIN bo_rank r
ON (r.RankType = rt.RankTypeType
AND r.RankPlace = mr.MatchResultPlace)
WHERE mr.MatchResultLicNbr = 'M180360GER01'
GROUP BY mr.MatchResultMatchId
, mr.MatchResultLicNbr
, m.MatchRoundId
, r.RankPoints
, mr.MatchResultHcp
, mr.MatchResultPlace
, m.MatchDate
, mr.MatchResultHomeOrAwayTeam
, m.MatchHomeTeamId
, m.MatchAwayTeamId
ORDER BY MatchResult DESC
INSERT INTO Res_ResultDetail
(
ResultDetailType
,[ResultDetailId]
,[ResultDetailId1]
,[ResultDetailId2]
,[ResultDetailLicNbr]
,[ResultDetailRoundId]
,[ResultDetailResult]
,[ResultDetailTable]
,[ResultDetailRankPoint]
,[ResultDetailRoundNbr]
,[ResultDetailRound]
)
SELECT
1 ResultDetailType
, MatchResultMatchId ResultDetailId
, 0 ResultDetailId1
, RoundId [ResultDetailId2 SquadId]
, MatchResultLicNbr
, mrTable [ResultDetailRoundId Serie]
, Result
, mrRound Bord
, mrRp Rankingpoäng
, mrRoundNbr
, 0
FROM dbo.bo_MatchResult
CROSS APPLY
(
VALUES
(1, MatchResultRoundResult1, MatchResultRankPoint1, MatchResultTable1, MatchResultRound1, MatchResultRoundNbr1 )
,(2, MatchResultRoundResult2, MatchResultRankPoint2, MatchResultTable2, MatchResultRound2, MatchResultRoundNbr2 )
,(3, MatchResultRoundResult3, MatchResultRankPoint3, MatchResultTable3, MatchResultRound3, MatchResultRoundNbr3 )
,(4, MatchResultRoundResult4, MatchResultRankPoint4, MatchResultTable4, MatchResultRound4, MatchResultRoundNbr4 )
,(5, MatchResultRoundResult5, MatchResultRankPoint5, MatchResultTable5, MatchResultRound5, MatchResultRoundNbr5 )
,(6, MatchResultRoundResult6, MatchResultRankPoint6, MatchResultTable6, MatchResultRound6, MatchResultRoundNbr6 )
,(7, MatchResultRoundResult7, MatchResultRankPoint7, MatchResultTable7, MatchResultRound7, MatchResultRoundNbr7 )
,(8, MatchResultRoundResult8, MatchResultRankPoint8, MatchResultTable8, MatchResultRound8, MatchResultRoundNbr8 )
) a (RoundId, Result, mrRp, mrTable, mrRound, mrRoundNbr)
WHERE Result <> 0
AND MatchResultLicNbr = 'M180360GER01'
ORDER BY MatchResultMatchId
, MatchResultShirtNo
, MatchResultLicNbr
, mrTable
July 24, 2012 at 5:34 pm
Would it be possible to provide the schema, some sample data, and the expected results from that data? The first question was simple enough to extrapolate for me but I'm afraid I'm a little lost looking at only the code in this next request. If you take a look at the first link in my signature it'll help you understand what will help us the most.
I'm not particularly sure I understand it because I'm not sure where all the different moving parts are supposed to end up.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 24, 2012 at 6:27 pm
Evil Kraig F (7/24/2012)
Would it be possible to provide the schema, some sample data, and the expected results from that data? The first question was simple enough to extrapolate for me but I'm afraid I'm a little lost looking at only the code in this next request. If you take a look at the first link in my signature it'll help you understand what will help us the most.I'm not particularly sure I understand it because I'm not sure where all the different moving parts are supposed to end up.
I have to second Evil's request. That's a lot of code to look at without a frame of reference.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 25, 2012 at 1:28 am
Here is some Data For bo_MatchTest and MatchResultTest
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[bo_MatchResultTest](
[MatchResultMatchId] [int] NOT NULL,
[MatchResultLicNbr] [nchar](20) NOT NULL,
[MatchResultTeamId] [int] NOT NULL,
[MatchResultShirtNo] [int] NOT NULL,
[MatchResultRoundResult1] [int] NOT NULL,
[MatchResultRoundResult2] [int] NOT NULL,
[MatchResultRoundResult3] [int] NOT NULL,
[MatchResultRoundResult4] [int] NOT NULL,
[MatchResultRoundResult5] [int] NOT NULL,
[MatchResultRoundResult6] [int] NOT NULL,
[MatchResultRoundResult7] [int] NOT NULL,
[MatchResultRoundResult8] [int] NOT NULL,
[MatchResultHomeOrAwayTeam] [char](1) NOT NULL,
[MatchResultPlace] [int] NOT NULL,
[MatchResultRankPoint1] [int] NOT NULL,
[MatchResultRankPoint2] [int] NOT NULL,
[MatchResultRankPoint3] [int] NOT NULL,
[MatchResultRankPoint4] [int] NOT NULL,
[MatchResultRankPoint5] [int] NOT NULL,
[MatchResultRankPoint6] [int] NOT NULL,
[MatchResultRankPoint7] [int] NOT NULL,
[MatchResultRankPoint8] [int] NOT NULL,
[MatchResultHcp] [int] NOT NULL,
[MatchResultTable1] [int] NOT NULL,
[MatchResultTable2] [int] NOT NULL,
[MatchResultTable3] [int] NOT NULL,
[MatchResultTable4] [int] NOT NULL,
[MatchResultTable5] [int] NOT NULL,
[MatchResultTable6] [int] NOT NULL,
[MatchResultTable7] [int] NOT NULL,
[MatchResultTable8] [int] NOT NULL,
[MatchResultRound1] [int] NOT NULL,
[MatchResultRound2] [int] NOT NULL,
[MatchResultRound3] [int] NOT NULL,
[MatchResultRound4] [int] NOT NULL,
[MatchResultRound5] [int] NOT NULL,
[MatchResultRound6] [int] NOT NULL,
[MatchResultRound7] [int] NOT NULL,
[MatchResultRound8] [int] NOT NULL,
[MatchResultRoundNbr1] [int] NOT NULL,
[MatchResultRoundNbr2] [int] NOT NULL,
[MatchResultRoundNbr3] [int] NOT NULL,
[MatchResultRoundNbr4] [int] NOT NULL,
[MatchResultRoundNbr5] [int] NOT NULL,
[MatchResultRoundNbr6] [int] NOT NULL,
[MatchResultRoundNbr7] [int] NOT NULL,
[MatchResultRoundNbr8] [int] NOT NULL,
CONSTRAINT [PK_bo_MatchResultTest] PRIMARY KEY CLUSTERED
(
[MatchResultMatchId] ASC,
[MatchResultLicNbr] ASC,
[MatchResultTeamId] 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
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[bo_MatchTest] Script Date: 2012-07-25 09:13:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[bo_MatchTest](
[MatchId] [int] NOT NULL,
[MatchNo] [nchar](20) NULL,
[MatchHomeTeamId] [int] NOT NULL,
[MatchAwayTeamId] [int] NOT NULL,
[MatchDate] [datetime] NOT NULL,
[MatchTime] [int] NOT NULL,
[MatchSeason] [int] NOT NULL,
[MatchRoundId] [int] NOT NULL,
[MatchStatus] [int] NOT NULL,
[MatchDivisionId] [int] NOT NULL,
[MatchHallId] [int] NOT NULL,
[MatchAlleyGroup] [int] NOT NULL,
[MatchNbrOfLanes] [int] NOT NULL,
[MatchFinished] [bit] NOT NULL,
[MatchNbrOfPlayers] [int] NOT NULL,
[MatchHcp] [bit] NOT NULL,
[MatchBossGroup] [int] NOT NULL,
[MatchLanePoints] [int] NOT NULL,
[MatchHomeTeamScore] [int] NOT NULL,
[MatchAwayTeamScore] [int] NOT NULL,
[MatchStrikeOut] [bit] NOT NULL,
[MatchHomeTeamResult] [int] NOT NULL,
[MatchAwayTeamResult] [int] NOT NULL,
[MatchDateOld] [smalldatetime] NOT NULL,
[MatchTimeOld] [int] NOT NULL,
[MatchStrikeOutNbrOfRounds] [int] NOT NULL,
[MatchOmit] [bit] NOT NULL,
[MatchOilPattern] [int] NOT NULL
) ON [PRIMARY]
GO
INSERT [dbo].[bo_MatchResultTest] ([MatchResultMatchId], [MatchResultLicNbr], [MatchResultTeamId], [MatchResultShirtNo], [MatchResultRoundResult1], [MatchResultRoundResult2], [MatchResultRoundResult3], [MatchResultRoundResult4], [MatchResultRoundResult5], [MatchResultRoundResult6], [MatchResultRoundResult7], [MatchResultRoundResult8], [MatchResultHomeOrAwayTeam], [MatchResultPlace], [MatchResultRankPoint1], [MatchResultRankPoint2], [MatchResultRankPoint3], [MatchResultRankPoint4], [MatchResultRankPoint5], [MatchResultRankPoint6], [MatchResultRankPoint7], [MatchResultRankPoint8], [MatchResultHcp], [MatchResultTable1], [MatchResultTable2], [MatchResultTable3], [MatchResultTable4], [MatchResultTable5], [MatchResultTable6], [MatchResultTable7], [MatchResultTable8], [MatchResultRound1], [MatchResultRound2], [MatchResultRound3], [MatchResultRound4], [MatchResultRound5], [MatchResultRound6], [MatchResultRound7], [MatchResultRound8], [MatchResultRoundNbr1], [MatchResultRoundNbr2], [MatchResultRoundNbr3], [MatchResultRoundNbr4], [MatchResultRoundNbr5], [MatchResultRoundNbr6], [MatchResultRoundNbr7], [MatchResultRoundNbr8]) VALUES (3017864, N'M011170JAN01 ', 90577, 5, 212, 213, 244, 198, 0, 0, 0, 0, N'B', 1, 0, 1, 1, 0, 0, 0, 0, 0, 0, 1, 2, 3, 4, 0, 0, 0, 0, 3, 2, 4, 1, 0, 0, 0, 0, 1, 1, 1, 1, 0, 0, 0, 0)
GO
INSERT [dbo].[bo_MatchResultTest] ([MatchResultMatchId], [MatchResultLicNbr], [MatchResultTeamId], [MatchResultShirtNo], [MatchResultRoundResult1], [MatchResultRoundResult2], [MatchResultRoundResult3], [MatchResultRoundResult4], [MatchResultRoundResult5], [MatchResultRoundResult6], [MatchResultRoundResult7], [MatchResultRoundResult8], [MatchResultHomeOrAwayTeam], [MatchResultPlace], [MatchResultRankPoint1], [MatchResultRankPoint2], [MatchResultRankPoint3], [MatchResultRankPoint4], [MatchResultRankPoint5], [MatchResultRankPoint6], [MatchResultRankPoint7], [MatchResultRankPoint8], [MatchResultHcp], [MatchResultTable1], [MatchResultTable2], [MatchResultTable3], [MatchResultTable4], [MatchResultTable5], [MatchResultTable6], [MatchResultTable7], [MatchResultTable8], [MatchResultRound1], [MatchResultRound2], [MatchResultRound3], [MatchResultRound4], [MatchResultRound5], [MatchResultRound6], [MatchResultRound7], [MatchResultRound8], [MatchResultRoundNbr1], [MatchResultRoundNbr2], [MatchResultRoundNbr3], [MatchResultRoundNbr4], [MatchResultRoundNbr5], [MatchResultRoundNbr6], [MatchResultRoundNbr7], [MatchResultRoundNbr8]) VALUES (3017864, N'M021185CHR01 ', 171165, 7, 221, 201, 137, 148, 0, 0, 0, 0, N'H', 15, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 2, 3, 4, 0, 0, 0, 0, 4, 2, 1, 3, 0, 0, 0, 0, 1, 1, 1, 1, 0, 0, 0, 0)
GO
INSERT [dbo].[bo_MatchResultTest] ([MatchResultMatchId], [MatchResultLicNbr], [MatchResultTeamId], [MatchResultShirtNo], [MatchResultRoundResult1], [MatchResultRoundResult2], [MatchResultRoundResult3], [MatchResultRoundResult4], [MatchResultRoundResult5], [MatchResultRoundResult6], [MatchResultRoundResult7], [MatchResultRoundResult8], [MatchResultHomeOrAwayTeam], [MatchResultPlace], [MatchResultRankPoint1], [MatchResultRankPoint2], [MatchResultRankPoint3], [MatchResultRankPoint4], [MatchResultRankPoint5], [MatchResultRankPoint6], [MatchResultRankPoint7], [MatchResultRankPoint8], [MatchResultHcp], [MatchResultTable1], [MatchResultTable2], [MatchResultTable3], [MatchResultTable4], [MatchResultTable5], [MatchResultTable6], [MatchResultTable7], [MatchResultTable8], [MatchResultRound1], [MatchResultRound2], [MatchResultRound3], [MatchResultRound4], [MatchResultRound5], [MatchResultRound6], [MatchResultRound7], [MatchResultRound8], [MatchResultRoundNbr1], [MatchResultRoundNbr2], [MatchResultRoundNbr3], [MatchResultRoundNbr4], [MatchResultRoundNbr5], [MatchResultRoundNbr6], [MatchResultRoundNbr7], [MatchResultRoundNbr8]) VALUES (3017864, N'M021271MAG01 ', 171165, 8, 244, 226, 176, 201, 0, 0, 0, 0, N'H', 2, 1, 0, 0, 0, 0, 0, 0, 0, 0, 1, 2, 3, 4, 0, 0, 0, 0, 4, 2, 1, 3, 0, 0, 0, 0, 2, 2, 2, 2, 0, 0, 0, 0)
GO
INSERT [dbo].[bo_MatchResultTest] ([MatchResultMatchId], [MatchResultLicNbr], [MatchResultTeamId], [MatchResultShirtNo], [MatchResultRoundResult1], [MatchResultRoundResult2], [MatchResultRoundResult3], [MatchResultRoundResult4], [MatchResultRoundResult5], [MatchResultRoundResult6], [MatchResultRoundResult7], [MatchResultRoundResult8], [MatchResultHomeOrAwayTeam], [MatchResultPlace], [MatchResultRankPoint1], [MatchResultRankPoint2], [MatchResultRankPoint3], [MatchResultRankPoint4], [MatchResultRankPoint5], [MatchResultRankPoint6], [MatchResultRankPoint7], [MatchResultRankPoint8], [MatchResultHcp], [MatchResultTable1], [MatchResultTable2], [MatchResultTable3], [MatchResultTable4], [MatchResultTable5], [MatchResultTable6], [MatchResultTable7], [MatchResultTable8], [MatchResultRound1], [MatchResultRound2], [MatchResultRound3], [MatchResultRound4], [MatchResultRound5], [MatchResultRound6], [MatchResultRound7], [MatchResultRound8], [MatchResultRoundNbr1], [MatchResultRoundNbr2], [MatchResultRoundNbr3], [MatchResultRoundNbr4], [MatchResultRoundNbr5], [MatchResultRoundNbr6], [MatchResultRoundNbr7], [MatchResultRoundNbr8]) VALUES (3017864, N'M040183GLE01 ', 171165, 4, 208, 173, 225, 204, 0, 0, 0, 0, N'H', 6, 1, 0, 0, 1, 0, 0, 0, 0, 0, 1, 2, 3, 4, 0, 0, 0, 0, 2, 4, 3, 1, 0, 0, 0, 0, 2, 2, 2, 2, 0, 0, 0, 0)
GO
INSERT [dbo].[bo_MatchResultTest] ([MatchResultMatchId], [MatchResultLicNbr], [MatchResultTeamId], [MatchResultShirtNo], [MatchResultRoundResult1], [MatchResultRoundResult2], [MatchResultRoundResult3], [MatchResultRoundResult4], [MatchResultRoundResult5], [MatchResultRoundResult6], [MatchResultRoundResult7], [MatchResultRoundResult8], [MatchResultHomeOrAwayTeam], [MatchResultPlace], [MatchResultRankPoint1], [MatchResultRankPoint2], [MatchResultRankPoint3], [MatchResultRankPoint4], [MatchResultRankPoint5], [MatchResultRankPoint6], [MatchResultRankPoint7], [MatchResultRankPoint8], [MatchResultHcp], [MatchResultTable1], [MatchResultTable2], [MatchResultTable3], [MatchResultTable4], [MatchResultTable5], [MatchResultTable6], [MatchResultTable7], [MatchResultTable8], [MatchResultRound1], [MatchResultRound2], [MatchResultRound3], [MatchResultRound4], [MatchResultRound5], [MatchResultRound6], [MatchResultRound7], [MatchResultRound8], [MatchResultRoundNbr1], [MatchResultRoundNbr2], [MatchResultRoundNbr3], [MatchResultRoundNbr4], [MatchResultRoundNbr5], [MatchResultRoundNbr6], [MatchResultRoundNbr7], [MatchResultRoundNbr8]) VALUES (3017864, N'M070283MIK01 ', 171165, 1, 201, 182, 182, 245, 0, 0, 0, 0, N'H', 6, 0, 1, 0, 1, 0, 0, 0, 0, 0, 1, 2, 3, 4, 0, 0, 0, 0, 1, 3, 4, 2, 0, 0, 0, 0, 1, 1, 1, 1, 0, 0, 0, 0)
GO
INSERT [dbo].[bo_MatchResultTest] ([MatchResultMatchId], [MatchResultLicNbr], [MatchResultTeamId], [MatchResultShirtNo], [MatchResultRoundResult1], [MatchResultRoundResult2], [MatchResultRoundResult3], [MatchResultRoundResult4], [MatchResultRoundResult5], [MatchResultRoundResult6], [MatchResultRoundResult7], [MatchResultRoundResult8], [MatchResultHomeOrAwayTeam], [MatchResultPlace], [MatchResultRankPoint1], [MatchResultRankPoint2], [MatchResultRankPoint3], [MatchResultRankPoint4], [MatchResultRankPoint5], [MatchResultRankPoint6], [MatchResultRankPoint7], [MatchResultRankPoint8], [MatchResultHcp], [MatchResultTable1], [MatchResultTable2], [MatchResultTable3], [MatchResultTable4], [MatchResultTable5], [MatchResultTable6], [MatchResultTable7], [MatchResultTable8], [MatchResultRound1], [MatchResultRound2], [MatchResultRound3], [MatchResultRound4], [MatchResultRound5], [MatchResultRound6], [MatchResultRound7], [MatchResultRound8], [MatchResultRoundNbr1], [MatchResultRoundNbr2], [MatchResultRoundNbr3], [MatchResultRoundNbr4], [MatchResultRoundNbr5], [MatchResultRoundNbr6], [MatchResultRoundNbr7], [MatchResultRoundNbr8]) VALUES (3017864, N'M090577ERI01 ', 90577, 1, 159, 186, 213, 195, 0, 0, 0, 0, N'B', 12, 1, 1, 1, 1, 0, 0, 0, 0, 0, 1, 2, 3, 4, 0, 0, 0, 0, 1, 4, 2, 3, 0, 0, 0, 0, 1, 1, 1, 1, 0, 0, 0, 0)
GO
INSERT [dbo].[bo_MatchResultTest] ([MatchResultMatchId], [MatchResultLicNbr], [MatchResultTeamId], [MatchResultShirtNo], [MatchResultRoundResult1], [MatchResultRoundResult2], [MatchResultRoundResult3], [MatchResultRoundResult4], [MatchResultRoundResult5], [MatchResultRoundResult6], [MatchResultRoundResult7], [MatchResultRoundResult8], [MatchResultHomeOrAwayTeam], [MatchResultPlace], [MatchResultRankPoint1], [MatchResultRankPoint2], [MatchResultRankPoint3], [MatchResultRankPoint4], [MatchResultRankPoint5], [MatchResultRankPoint6], [MatchResultRankPoint7], [MatchResultRankPoint8], [MatchResultHcp], [MatchResultTable1], [MatchResultTable2], [MatchResultTable3], [MatchResultTable4], [MatchResultTable5], [MatchResultTable6], [MatchResultTable7], [MatchResultTable8], [MatchResultRound1], [MatchResultRound2], [MatchResultRound3], [MatchResultRound4], [MatchResultRound5], [MatchResultRound6], [MatchResultRound7], [MatchResultRound8], [MatchResultRoundNbr1], [MatchResultRoundNbr2], [MatchResultRoundNbr3], [MatchResultRoundNbr4], [MatchResultRoundNbr5], [MatchResultRoundNbr6], [MatchResultRoundNbr7], [MatchResultRoundNbr8]) VALUES (3017864, N'M130771NIC02 ', 171165, 2, 151, 0, 0, 0, 0, 0, 0, 0, N'H', 17, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 1, 0, 0, 0, 0, 0, 0, 0, 2, 0, 0, 0, 0, 0, 0, 0)
GO
INSERT [dbo].[bo_MatchResultTest] ([MatchResultMatchId], [MatchResultLicNbr], [MatchResultTeamId], [MatchResultShirtNo], [MatchResultRoundResult1], [MatchResultRoundResult2], [MatchResultRoundResult3], [MatchResultRoundResult4], [MatchResultRoundResult5], [MatchResultRoundResult6], [MatchResultRoundResult7], [MatchResultRoundResult8], [MatchResultHomeOrAwayTeam], [MatchResultPlace], [MatchResultRankPoint1], [MatchResultRankPoint2], [MatchResultRankPoint3], [MatchResultRankPoint4], [MatchResultRankPoint5], [MatchResultRankPoint6], [MatchResultRankPoint7], [MatchResultRankPoint8], [MatchResultHcp], [MatchResultTable1], [MatchResultTable2], [MatchResultTable3], [MatchResultTable4], [MatchResultTable5], [MatchResultTable6], [MatchResultTable7], [MatchResultTable8], [MatchResultRound1], [MatchResultRound2], [MatchResultRound3], [MatchResultRound4], [MatchResultRound5], [MatchResultRound6], [MatchResultRound7], [MatchResultRound8], [MatchResultRoundNbr1], [MatchResultRoundNbr2], [MatchResultRoundNbr3], [MatchResultRoundNbr4], [MatchResultRoundNbr5], [MatchResultRoundNbr6], [MatchResultRoundNbr7], [MatchResultRoundNbr8]) VALUES (3017864, N'M150873TOM01 ', 90577, 8, 203, 207, 182, 255, 0, 0, 0, 0, N'B', 2, 0, 1, 1, 0, 0, 0, 0, 0, 0, 1, 2, 3, 4, 0, 0, 0, 0, 4, 1, 3, 2, 0, 0, 0, 0, 2, 2, 2, 2, 0, 0, 0, 0)
GO
INSERT [dbo].[bo_MatchResultTest] ([MatchResultMatchId], [MatchResultLicNbr], [MatchResultTeamId], [MatchResultShirtNo], [MatchResultRoundResult1], [MatchResultRoundResult2], [MatchResultRoundResult3], [MatchResultRoundResult4], [MatchResultRoundResult5], [MatchResultRoundResult6], [MatchResultRoundResult7], [MatchResultRoundResult8], [MatchResultHomeOrAwayTeam], [MatchResultPlace], [MatchResultRankPoint1], [MatchResultRankPoint2], [MatchResultRankPoint3], [MatchResultRankPoint4], [MatchResultRankPoint5], [MatchResultRankPoint6], [MatchResultRankPoint7], [MatchResultRankPoint8], [MatchResultHcp], [MatchResultTable1], [MatchResultTable2], [MatchResultTable3], [MatchResultTable4], [MatchResultTable5], [MatchResultTable6], [MatchResultTable7], [MatchResultTable8], [MatchResultRound1], [MatchResultRound2], [MatchResultRound3], [MatchResultRound4], [MatchResultRound5], [MatchResultRound6], [MatchResultRound7], [MatchResultRound8], [MatchResultRoundNbr1], [MatchResultRoundNbr2], [MatchResultRoundNbr3], [MatchResultRoundNbr4], [MatchResultRoundNbr5], [MatchResultRoundNbr6], [MatchResultRoundNbr7], [MatchResultRoundNbr8]) VALUES (3017864, N'M170266AND02 ', 171165, 5, 180, 201, 166, 187, 0, 0, 0, 0, N'H', 14, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 2, 3, 4, 0, 0, 0, 0, 3, 1, 2, 4, 0, 0, 0, 0, 1, 1, 1, 1, 0, 0, 0, 0)
GO
INSERT [dbo].[bo_MatchResultTest] ([MatchResultMatchId], [MatchResultLicNbr], [MatchResultTeamId], [MatchResultShirtNo], [MatchResultRoundResult1], [MatchResultRoundResult2], [MatchResultRoundResult3], [MatchResultRoundResult4], [MatchResultRoundResult5], [MatchResultRoundResult6], [MatchResultRoundResult7], [MatchResultRoundResult8], [MatchResultHomeOrAwayTeam], [MatchResultPlace], [MatchResultRankPoint1], [MatchResultRankPoint2], [MatchResultRankPoint3], [MatchResultRankPoint4], [MatchResultRankPoint5], [MatchResultRankPoint6], [MatchResultRankPoint7], [MatchResultRankPoint8], [MatchResultHcp], [MatchResultTable1], [MatchResultTable2], [MatchResultTable3], [MatchResultTable4], [MatchResultTable5], [MatchResultTable6], [MatchResultTable7], [MatchResultTable8], [MatchResultRound1], [MatchResultRound2], [MatchResultRound3], [MatchResultRound4], [MatchResultRound5], [MatchResultRound6], [MatchResultRound7], [MatchResultRound8], [MatchResultRoundNbr1], [MatchResultRoundNbr2], [MatchResultRoundNbr3], [MatchResultRoundNbr4], [MatchResultRoundNbr5], [MatchResultRoundNbr6], [MatchResultRoundNbr7], [MatchResultRoundNbr8]) VALUES (3017864, N'M180360GER01 ', 90577, 6, 200, 232, 182, 182, 0, 0, 0, 0, N'B', 9, 0, 1, 1, 0, 0, 0, 0, 0, 0, 1, 2, 3, 4, 0, 0, 0, 0, 3, 2, 4, 1, 0, 0, 0, 0, 2, 2, 2, 2, 0, 0, 0, 0)
GO
INSERT [dbo].[bo_MatchResultTest] ([MatchResultMatchId], [MatchResultLicNbr], [MatchResultTeamId], [MatchResultShirtNo], [MatchResultRoundResult1], [MatchResultRoundResult2], [MatchResultRoundResult3], [MatchResultRoundResult4], [MatchResultRoundResult5], [MatchResultRoundResult6], [MatchResultRoundResult7], [MatchResultRoundResult8], [MatchResultHomeOrAwayTeam], [MatchResultPlace], [MatchResultRankPoint1], [MatchResultRankPoint2], [MatchResultRankPoint3], [MatchResultRankPoint4], [MatchResultRankPoint5], [MatchResultRankPoint6], [MatchResultRankPoint7], [MatchResultRankPoint8], [MatchResultHcp], [MatchResultTable1], [MatchResultTable2], [MatchResultTable3], [MatchResultTable4], [MatchResultTable5], [MatchResultTable6], [MatchResultTable7], [MatchResultTable8], [MatchResultRound1], [MatchResultRound2], [MatchResultRound3], [MatchResultRound4], [MatchResultRound5], [MatchResultRound6], [MatchResultRound7], [MatchResultRound8], [MatchResultRoundNbr1], [MatchResultRoundNbr2], [MatchResultRoundNbr3], [MatchResultRoundNbr4], [MatchResultRoundNbr5], [MatchResultRoundNbr6], [MatchResultRoundNbr7], [MatchResultRoundNbr8]) VALUES (3017864, N'M180773STE03 ', 90577, 4, 182, 185, 223, 201, 0, 0, 0, 0, N'B', 10, 0, 0, 1, 1, 0, 0, 0, 0, 0, 1, 2, 3, 4, 0, 0, 0, 0, 2, 3, 1, 4, 0, 0, 0, 0, 2, 2, 2, 2, 0, 0, 0, 0)
GO
INSERT [dbo].[bo_MatchResultTest] ([MatchResultMatchId], [MatchResultLicNbr], [MatchResultTeamId], [MatchResultShirtNo], [MatchResultRoundResult1], [MatchResultRoundResult2], [MatchResultRoundResult3], [MatchResultRoundResult4], [MatchResultRoundResult5], [MatchResultRoundResult6], [MatchResultRoundResult7], [MatchResultRoundResult8], [MatchResultHomeOrAwayTeam], [MatchResultPlace], [MatchResultRankPoint1], [MatchResultRankPoint2], [MatchResultRankPoint3], [MatchResultRankPoint4], [MatchResultRankPoint5], [MatchResultRankPoint6], [MatchResultRankPoint7], [MatchResultRankPoint8], [MatchResultHcp], [MatchResultTable1], [MatchResultTable2], [MatchResultTable3], [MatchResultTable4], [MatchResultTable5], [MatchResultTable6], [MatchResultTable7], [MatchResultTable8], [MatchResultRound1], [MatchResultRound2], [MatchResultRound3], [MatchResultRound4], [MatchResultRound5], [MatchResultRound6], [MatchResultRound7], [MatchResultRound8], [MatchResultRoundNbr1], [MatchResultRoundNbr2], [MatchResultRoundNbr3], [MatchResultRoundNbr4], [MatchResultRoundNbr5], [MatchResultRoundNbr6], [MatchResultRoundNbr7], [MatchResultRoundNbr8]) VALUES (3017864, N'M210291ALE01 ', 171165, 9, 0, 170, 188, 214, 0, 0, 0, 0, N'H', 16, 0, 1, 0, 1, 0, 0, 0, 0, 0, 0, 2, 3, 4, 0, 0, 0, 0, 0, 3, 4, 2, 0, 0, 0, 0, 0, 2, 2, 2, 0, 0, 0, 0)
GO
INSERT [dbo].[bo_MatchResultTest] ([MatchResultMatchId], [MatchResultLicNbr], [MatchResultTeamId], [MatchResultShirtNo], [MatchResultRoundResult1], [MatchResultRoundResult2], [MatchResultRoundResult3], [MatchResultRoundResult4], [MatchResultRoundResult5], [MatchResultRoundResult6], [MatchResultRoundResult7], [MatchResultRoundResult8], [MatchResultHomeOrAwayTeam], [MatchResultPlace], [MatchResultRankPoint1], [MatchResultRankPoint2], [MatchResultRankPoint3], [MatchResultRankPoint4], [MatchResultRankPoint5], [MatchResultRankPoint6], [MatchResultRankPoint7], [MatchResultRankPoint8], [MatchResultHcp], [MatchResultTable1], [MatchResultTable2], [MatchResultTable3], [MatchResultTable4], [MatchResultTable5], [MatchResultTable6], [MatchResultTable7], [MatchResultTable8], [MatchResultRound1], [MatchResultRound2], [MatchResultRound3], [MatchResultRound4], [MatchResultRound5], [MatchResultRound6], [MatchResultRound7], [MatchResultRound8], [MatchResultRoundNbr1], [MatchResultRoundNbr2], [MatchResultRoundNbr3], [MatchResultRoundNbr4], [MatchResultRoundNbr5], [MatchResultRoundNbr6], [MatchResultRoundNbr7], [MatchResultRoundNbr8]) VALUES (3017864, N'M211264ROL01 ', 171165, 3, 226, 171, 166, 197, 0, 0, 0, 0, N'H', 11, 1, 0, 0, 1, 0, 0, 0, 0, 0, 1, 2, 3, 4, 0, 0, 0, 0, 2, 4, 3, 1, 0, 0, 0, 0, 1, 1, 1, 1, 0, 0, 0, 0)
GO
INSERT [dbo].[bo_MatchResultTest] ([MatchResultMatchId], [MatchResultLicNbr], [MatchResultTeamId], [MatchResultShirtNo], [MatchResultRoundResult1], [MatchResultRoundResult2], [MatchResultRoundResult3], [MatchResultRoundResult4], [MatchResultRoundResult5], [MatchResultRoundResult6], [MatchResultRoundResult7], [MatchResultRoundResult8], [MatchResultHomeOrAwayTeam], [MatchResultPlace], [MatchResultRankPoint1], [MatchResultRankPoint2], [MatchResultRankPoint3], [MatchResultRankPoint4], [MatchResultRankPoint5], [MatchResultRankPoint6], [MatchResultRankPoint7], [MatchResultRankPoint8], [MatchResultHcp], [MatchResultTable1], [MatchResultTable2], [MatchResultTable3], [MatchResultTable4], [MatchResultTable5], [MatchResultTable6], [MatchResultTable7], [MatchResultTable8], [MatchResultRound1], [MatchResultRound2], [MatchResultRound3], [MatchResultRound4], [MatchResultRound5], [MatchResultRound6], [MatchResultRound7], [MatchResultRound8], [MatchResultRoundNbr1], [MatchResultRoundNbr2], [MatchResultRoundNbr3], [MatchResultRoundNbr4], [MatchResultRoundNbr5], [MatchResultRoundNbr6], [MatchResultRoundNbr7], [MatchResultRoundNbr8]) VALUES (3017864, N'M260176JÖR01 ', 90577, 3, 227, 166, 195, 224, 0, 0, 0, 0, N'B', 5, 0, 0, 1, 1, 0, 0, 0, 0, 0, 1, 2, 3, 4, 0, 0, 0, 0, 2, 3, 1, 4, 0, 0, 0, 0, 1, 1, 1, 1, 0, 0, 0, 0)
GO
INSERT [dbo].[bo_MatchResultTest] ([MatchResultMatchId], [MatchResultLicNbr], [MatchResultTeamId], [MatchResultShirtNo], [MatchResultRoundResult1], [MatchResultRoundResult2], [MatchResultRoundResult3], [MatchResultRoundResult4], [MatchResultRoundResult5], [MatchResultRoundResult6], [MatchResultRoundResult7], [MatchResultRoundResult8], [MatchResultHomeOrAwayTeam], [MatchResultPlace], [MatchResultRankPoint1], [MatchResultRankPoint2], [MatchResultRankPoint3], [MatchResultRankPoint4], [MatchResultRankPoint5], [MatchResultRankPoint6], [MatchResultRankPoint7], [MatchResultRankPoint8], [MatchResultHcp], [MatchResultTable1], [MatchResultTable2], [MatchResultTable3], [MatchResultTable4], [MatchResultTable5], [MatchResultTable6], [MatchResultTable7], [MatchResultTable8], [MatchResultRound1], [MatchResultRound2], [MatchResultRound3], [MatchResultRound4], [MatchResultRound5], [MatchResultRound6], [MatchResultRound7], [MatchResultRound8], [MatchResultRoundNbr1], [MatchResultRoundNbr2], [MatchResultRoundNbr3], [MatchResultRoundNbr4], [MatchResultRoundNbr5], [MatchResultRoundNbr6], [MatchResultRoundNbr7], [MatchResultRoundNbr8]) VALUES (3017864, N'M270866KEN01 ', 171165, 6, 232, 180, 196, 201, 0, 0, 0, 0, N'H', 8, 0, 0, 0, 0, 0, 0, 0, 0, 0, 1, 2, 3, 4, 0, 0, 0, 0, 3, 1, 2, 4, 0, 0, 0, 0, 2, 2, 2, 2, 0, 0, 0, 0)
GO
INSERT [dbo].[bo_MatchResultTest] ([MatchResultMatchId], [MatchResultLicNbr], [MatchResultTeamId], [MatchResultShirtNo], [MatchResultRoundResult1], [MatchResultRoundResult2], [MatchResultRoundResult3], [MatchResultRoundResult4], [MatchResultRoundResult5], [MatchResultRoundResult6], [MatchResultRoundResult7], [MatchResultRoundResult8], [MatchResultHomeOrAwayTeam], [MatchResultPlace], [MatchResultRankPoint1], [MatchResultRankPoint2], [MatchResultRankPoint3], [MatchResultRankPoint4], [MatchResultRankPoint5], [MatchResultRankPoint6], [MatchResultRankPoint7], [MatchResultRankPoint8], [MatchResultHcp], [MatchResultTable1], [MatchResultTable2], [MatchResultTable3], [MatchResultTable4], [MatchResultTable5], [MatchResultTable6], [MatchResultTable7], [MatchResultTable8], [MatchResultRound1], [MatchResultRound2], [MatchResultRound3], [MatchResultRound4], [MatchResultRound5], [MatchResultRound6], [MatchResultRound7], [MatchResultRound8], [MatchResultRoundNbr1], [MatchResultRoundNbr2], [MatchResultRoundNbr3], [MatchResultRoundNbr4], [MatchResultRoundNbr5], [MatchResultRoundNbr6], [MatchResultRoundNbr7], [MatchResultRoundNbr8]) VALUES (3017864, N'M290879DEN03 ', 90577, 2, 196, 215, 166, 174, 0, 0, 0, 0, N'B', 13, 1, 1, 1, 1, 0, 0, 0, 0, 0, 1, 2, 3, 4, 0, 0, 0, 0, 1, 4, 2, 3, 0, 0, 0, 0, 2, 2, 2, 2, 0, 0, 0, 0)
GO
INSERT [dbo].[bo_MatchResultTest] ([MatchResultMatchId], [MatchResultLicNbr], [MatchResultTeamId], [MatchResultShirtNo], [MatchResultRoundResult1], [MatchResultRoundResult2], [MatchResultRoundResult3], [MatchResultRoundResult4], [MatchResultRoundResult5], [MatchResultRoundResult6], [MatchResultRoundResult7], [MatchResultRoundResult8], [MatchResultHomeOrAwayTeam], [MatchResultPlace], [MatchResultRankPoint1], [MatchResultRankPoint2], [MatchResultRankPoint3], [MatchResultRankPoint4], [MatchResultRankPoint5], [MatchResultRankPoint6], [MatchResultRankPoint7], [MatchResultRankPoint8], [MatchResultHcp], [MatchResultTable1], [MatchResultTable2], [MatchResultTable3], [MatchResultTable4], [MatchResultTable5], [MatchResultTable6], [MatchResultTable7], [MatchResultTable8], [MatchResultRound1], [MatchResultRound2], [MatchResultRound3], [MatchResultRound4], [MatchResultRound5], [MatchResultRound6], [MatchResultRound7], [MatchResultRound8], [MatchResultRoundNbr1], [MatchResultRoundNbr2], [MatchResultRoundNbr3], [MatchResultRoundNbr4], [MatchResultRoundNbr5], [MatchResultRoundNbr6], [MatchResultRoundNbr7], [MatchResultRoundNbr8]) VALUES (3017864, N'M300872MAG04 ', 90577, 9, 0, 0, 0, 0, 0, 0, 0, 0, N'B', 18, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0)
GO
INSERT [dbo].[bo_MatchResultTest] ([MatchResultMatchId], [MatchResultLicNbr], [MatchResultTeamId], [MatchResultShirtNo], [MatchResultRoundResult1], [MatchResultRoundResult2], [MatchResultRoundResult3], [MatchResultRoundResult4], [MatchResultRoundResult5], [MatchResultRoundResult6], [MatchResultRoundResult7], [MatchResultRoundResult8], [MatchResultHomeOrAwayTeam], [MatchResultPlace], [MatchResultRankPoint1], [MatchResultRankPoint2], [MatchResultRankPoint3], [MatchResultRankPoint4], [MatchResultRankPoint5], [MatchResultRankPoint6], [MatchResultRankPoint7], [MatchResultRankPoint8], [MatchResultHcp], [MatchResultTable1], [MatchResultTable2], [MatchResultTable3], [MatchResultTable4], [MatchResultTable5], [MatchResultTable6], [MatchResultTable7], [MatchResultTable8], [MatchResultRound1], [MatchResultRound2], [MatchResultRound3], [MatchResultRound4], [MatchResultRound5], [MatchResultRound6], [MatchResultRound7], [MatchResultRound8], [MatchResultRoundNbr1], [MatchResultRoundNbr2], [MatchResultRoundNbr3], [MatchResultRoundNbr4], [MatchResultRoundNbr5], [MatchResultRoundNbr6], [MatchResultRoundNbr7], [MatchResultRoundNbr8]) VALUES (3017864, N'M301286MAX01 ', 90577, 7, 180, 199, 237, 198, 0, 0, 0, 0, N'B', 4, 0, 1, 1, 0, 0, 0, 0, 0, 0, 1, 2, 3, 4, 0, 0, 0, 0, 4, 1, 3, 2, 0, 0, 0, 0, 1, 1, 1, 1, 0, 0, 0, 0)
GO
INSERT [dbo].[bo_MatchTest] ([MatchId], [MatchNo], [MatchHomeTeamId], [MatchAwayTeamId], [MatchDate], [MatchTime], [MatchSeason], [MatchRoundId], [MatchStatus], [MatchDivisionId], [MatchHallId], [MatchAlleyGroup], [MatchNbrOfLanes], [MatchFinished], [MatchNbrOfPlayers], [MatchHcp], [MatchBossGroup], [MatchLanePoints], [MatchHomeTeamScore], [MatchAwayTeamScore], [MatchStrikeOut], [MatchHomeTeamResult], [MatchAwayTeamResult], [MatchDateOld], [MatchTimeOld], [MatchStrikeOutNbrOfRounds], [MatchOmit], [MatchOilPattern]) VALUES (3017864, N' ', 171165, 90577, CAST(0x00009FFC00000000 AS DateTime), 900, 2011, 17, 0, 1, 735, 5, 8, 1, 8, 0, 0, 1, 0, 0, 0, 6, 13, CAST(0x00000000 AS SmallDateTime), 0, 0, 0, 2)
GO
Here Is the T-SQL above i did some changes so i could minimise the Joins from Cup and so on to get RankPoints.
INSERT INTO Res_Result
(
[ResultType]
, [ResultLicNbr]
, [ResultMatch_CompId]
, [ResultClassId]
, [ResultSquad_RoundId]
, [ResultNbrOfSeries]
, [ResultTotResult]
, [ResultRPPoints]
, [ResultLanePoints]
, [ResultHcp]
, [ResultPlaceTot]
, [ResultPlaceSquad]
, [ResultDate]
, [ResultTeamId]
, [ResultHomeOrAwayTeam]
)
SELECT
1 Resulttype
, MatchResultLicNbr
, MatchResultMatchId
, 0 ResultClassId
, m.MatchRoundId ResultSquad_RoundId
, SUM(
CASE
WHEN MatchResultRoundResult1 > 0
THEN
1
ELSE
0
END
+ CASE
WHEN MatchResultRoundResult2 > 0
THEN
1
ELSE
0
END
+ CASE
WHEN MatchResultRoundResult3 > 0
THEN
1
ELSE
0
END
+ CASE
WHEN MatchResultRoundResult4 > 0
THEN
1
ELSE
0
END
+ CASE
WHEN MatchResultRoundResult5 > 0
THEN
1
ELSE
0
END
+ CASE
WHEN MatchResultRoundResult6 > 0
THEN
1
ELSE
0
END
+ CASE
WHEN MatchResultRoundResult7 > 0
THEN
1
ELSE
0
END
+ CASE
WHEN MatchResultRoundResult8 > 0
THEN
1
ELSE
0
END
) AS MatchRounds
, SUM
(
MatchResultRoundResult1
+ MatchResultRoundResult2
+ MatchResultRoundResult3
+ MatchResultRoundResult4
+ MatchResultRoundResult5
+ MatchResultRoundResult6
+ MatchResultRoundResult7
+ MatchResultRoundResult8
) AS MatchResult
, SUM
(
MatchResultRankPoint1
+ MatchResultRankPoint2
+ MatchResultRankPoint3
+ MatchResultRankPoint4
+ MatchResultRankPoint5
+ MatchResultRankPoint6
+ MatchResultRankPoint7
+ MatchResultRankPoint8
) AS MatchLanePoints
, 0 AS RankPoints
, mr.MatchResultHcp
, mr.MatchResultPlace
, 0 ResultPlaceSquad
, m.MatchDate
, CASE WHEN mr.MatchResultHomeOrAwayTeam = 'H'
THEN
m.MatchHomeTeamId
ELSE
m.MatchAwayTeamId
END ResultTeamId
, mr.MatchResultHomeOrAwayTeam
FROM bo_MatchResultTest mr
JOIN bo_MatchTest m
ON mr.MatchResultMatchId = m. MatchId
WHERE m.MatchId = 3017864
GROUP BY mr.MatchResultMatchId
, mr.MatchResultLicNbr
, m.MatchRoundId
, mr.MatchResultHcp
, mr.MatchResultPlace
, m.MatchDate
, mr.MatchResultHomeOrAwayTeam
, m.MatchHomeTeamId
, m.MatchAwayTeamId
ORDER BY MatchResult DESC
INSERT INTO Res_ResultDetail
(
ResultDetailType
,[ResultDetailId]
,[ResultDetailId1]
,[ResultDetailId2]
,[ResultDetailLicNbr]
,[ResultDetailRoundId]
,[ResultDetailResult]
,[ResultDetailTable]
,[ResultDetailRankPoint]
,[ResultDetailRoundNbr]
)
SELECT
1 ResultDetailType
, MatchResultMatchId ResultDetailId
, 0 ResultDetailId1
, RoundId [ResultDetailId2 SquadId]
, MatchResultLicNbr
, mrTable [ResultDetailRoundId Serie]
, Result
, mrRound Bord
, mrRp Rankingpoäng
, mrRoundNbr
FROM dbo.bo_MatchResult
CROSS APPLY
(
VALUES
(1, MatchResultRoundResult1, MatchResultRankPoint1, MatchResultTable1, MatchResultRound1, MatchResultRoundNbr1 )
,(2, MatchResultRoundResult2, MatchResultRankPoint2, MatchResultTable2, MatchResultRound2, MatchResultRoundNbr2 )
,(3, MatchResultRoundResult3, MatchResultRankPoint3, MatchResultTable3, MatchResultRound3, MatchResultRoundNbr3 )
,(4, MatchResultRoundResult4, MatchResultRankPoint4, MatchResultTable4, MatchResultRound4, MatchResultRoundNbr4 )
,(5, MatchResultRoundResult5, MatchResultRankPoint5, MatchResultTable5, MatchResultRound5, MatchResultRoundNbr5 )
,(6, MatchResultRoundResult6, MatchResultRankPoint6, MatchResultTable6, MatchResultRound6, MatchResultRoundNbr6 )
,(7, MatchResultRoundResult7, MatchResultRankPoint7, MatchResultTable7, MatchResultRound7, MatchResultRoundNbr7 )
,(8, MatchResultRoundResult8, MatchResultRankPoint8, MatchResultTable8, MatchResultRound8, MatchResultRoundNbr8 )
) a (RoundId, Result, mrRp, mrTable, mrRound, mrRoundNbr)
WHERE Result <> 0
AND MatchResultMatchId = 3017864
ORDER BY MatchResultMatchId
, MatchResultShirtNo
, MatchResultLicNbr
, mrTable
Brg
Gert
[/code]
July 25, 2012 at 6:53 pm
Gert,
I'm taking a look now.
Had a bit of a problem getting your last posted script to run. You had this (in the second SELECT):
FROM dbo.bo_MatchResult
And I had to change it to this:
FROM dbo.bo_MatchResultTest
Now it runs, but I also had to remove the 2 INSERTs as you provided no DDL for those.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
July 25, 2012 at 7:05 pm
Gert,
Now that I've looked, I'm not sure I understand what the remaining question is.
It appears that your second query is correctly using CROSS APPLY VALUES to UNPIVOT the flat table into the details that you want.
Can you provide an additional explanation?
Dwain
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
August 3, 2012 at 12:56 am
Hello Again Dwain
Sorry for my late answer, I've on vacation in Greece for one week.
What i mean is there an better alernative for CURSOR.
What i want to do is following:
I shall Aggregate two new tabel of bo_MatchResultTest
first one tabel Res_Result and then Res_ResultDetails
Res Result is the hed informationTablefor Each Player in one match
Like you see in the INSERT INTO Example
So when the SELECT Hits an new MatchId there is at least 16 rows to Res_Result
and for every Res_Result LIKE My MatchResultLicNbr'M180360GER01'.
i want to Aggregate the Information of the Rounds i Played in this case 4 Rounds to Res_ResultDetails.
Hope you understand
Thanks in advance
Gert
Viewing 13 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply