#### leaderboard.html ####
## 게임정보 조회
## getGameInfo
SELECT
gi01 as gameCode -- 대회코드
, gl04 as gameTitle -- 대회명
, gl05 as gameEngTitle -- 대회영문명
, gl02 as startDate -- 대회시작일
, gl03 as endDate -- 대회종료일
, gi75 as gameMethod -- 대회방식 (0:스트로크, 1:매치, 2:스테이블포드)
, gl08 as prizeMoney -- 총상금
, gl09 as moneyUnit -- 상금단위
, convert(int,subString(gi40,1,1))+convert(int,subString(gi40,2,1))+convert(int,subString(gi40,3,1))+convert(int,subString(gi40,4,1))
+convert(int,subString(gi40,5,1))+convert(int,subString(gi40,6,1))+convert(int,subString(gi40,7,1))+convert(int,subString(gi40,8,1))
+convert(int,subString(gi40,9,1))+convert(int,subString(gi40,10,1))+convert(int,subString(gi40,11,1))+convert(int,subString(gi40,12,1))
+convert(int,subString(gi40,13,1))+convert(int,subString(gi40,14,1))+convert(int,subString(gi40,15,1))+convert(int,subString(gi40,16,1))
+convert(int,subString(gi40,17,1))+convert(int,subString(gi40,18,1))
AS totalPar -- 기준 토탈파
, co03 as courseText -- 코스명
, co04 as courseEngText -- 코스영문명
, e.ci03 as outCourseText -- 아웃코스명
, isnull(e.ci05,'') as outCourseEngText -- 아웃코스영문명
, f.ci03 as inCourseText -- 인코스명
, isnull(f.ci05,'') as inCourseEngText -- 인코스영문명
, CONVERT(int,Gi10)-(SELECT COUNT(*) FROM Round_OFF WHERE ro01=Gi01) AS totalRound -- 전체라운드
, Ra01 AS seasonYear -- 시즌년도
, Gl17 AS tourType -- 투어구분 (RE:정규투어, DR:드림투어, JT:점프투어, SN:챔피언스투어)
, CASE
WHEN ISNULL(GI79,'S') = 'F' THEN 4
WHEN ISNULL(GI78,'S') = 'F' THEN 3
WHEN ISNULL(GI70,'S') = 'F' AND ISNULL(GI77,'S') = 'F' THEN 2
WHEN ISNULL(GI70,'S') <> 'F' AND ISNULL(GI77,'S') = 'F' THEN 1
WHEN ISNULL(GI76,'S') = 'F' THEN 1
END AS ingRound -- 조편성이 게시된 라운드
, CASE
WHEN Gi73='F' THEN 4
WHEN Gi72='F' THEN 3
WHEN Gi71='F' THEN 2
WHEN Gi70='F' THEN 1
ELSE 0
END AS finishRound -- 라운드마감된 라운드
, isNull(Gi60,'S') AS gameFinish -- 게임종료여부
, Gi40 AS stdParString -- 기준파텍스트 (18자리문자열, 1자리씩 홀별 기준파)
, Gl14 AS seriesCode -- 시리즈코드
, isNull((SELECT TOP 1 REPLACE(Gp09,':','') AS time FROM groupG WHERE Gp01=a.gi01 AND Gp03='1' ORDER BY Gp09 ASC),'') AS gameStartTime -- 게임시작시간 (1라운드 첫조출발시각)
, Gd02 AS sponsor -- 대회 스폰서
, Gd27 AS engSponsor -- 대회 스폰서명
, Gd06 AS agency -- 대행사
, Gd28 AS engAgency -- 대행사 영문명
, convert(int, SUBSTRING(GI41 ,1 ,3))+convert(int, SUBSTRING(GI41 ,4 ,3)) +convert(int, SUBSTRING(GI41 ,7 ,3))
+convert(int, SUBSTRING(GI41 ,10 ,3))+convert(int, SUBSTRING(GI41 ,13 ,3))+convert(int, SUBSTRING(GI41 ,16 ,3))
+convert(int, SUBSTRING(GI41 ,19 ,3))+convert(int, SUBSTRING(GI41 ,22 ,3))+convert(int, SUBSTRING(GI41 ,25 ,3))
+convert(int, SUBSTRING(GI41 ,28 ,3))+convert(int, SUBSTRING(GI41 ,31 ,3))+convert(int, SUBSTRING(GI41 ,34 ,3))
+convert(int, SUBSTRING(GI41 ,37 ,3))+convert(int, SUBSTRING(GI41 ,40 ,3))+convert(int, SUBSTRING(GI41 ,43 ,3))
+convert(int, SUBSTRING(GI41 ,46 ,3))+convert(int, SUBSTRING(GI41 ,49 ,3))+convert(int, SUBSTRING(GI41 ,52 ,3))
AS totalYds -- 예선 전체 yds
, convert(int, SUBSTRING(Gi105 ,1 ,3))+convert(int, SUBSTRING(Gi105 ,4 ,3))+convert(int, SUBSTRING(Gi105 ,7 ,3))
+convert(int, SUBSTRING(Gi105 ,10 ,3))+convert(int, SUBSTRING(Gi105 ,13 ,3))+convert(int, SUBSTRING(Gi105 ,16 ,3))
+convert(int, SUBSTRING(Gi105 ,19 ,3))+convert(int, SUBSTRING(Gi105 ,22 ,3))+convert(int, SUBSTRING(Gi105 ,25 ,3))
+convert(int, SUBSTRING(Gi105 ,28 ,3))+convert(int, SUBSTRING(Gi105 ,31 ,3))+convert(int, SUBSTRING(Gi105 ,34 ,3))
+convert(int, SUBSTRING(Gi105 ,37 ,3))+convert(int, SUBSTRING(Gi105 ,40 ,3))+convert(int, SUBSTRING(Gi105 ,43 ,3))
+convert(int, SUBSTRING(Gi105 ,46 ,3))+convert(int, SUBSTRING(Gi105 ,49 ,3))+convert(int, SUBSTRING(Gi105 ,52 ,3))
AS finalTotalYds -- 본선 전체 yds
FROM
game_info a
join game_list b on a.gi01 = b.gl01
inner join RecordDate c on b.gl02 between c.ra02 and c.ra03 and b.gl17 = c.ra06
LEFT JOIN Game_Info_Web ON a.gi01 = Gd01
left outer join KLPGA.dbo.Course d ON b.gl12 = d.Co01
left outer join KLPGA.dbo.Course_Info e on a.gi22 = e.ci01 and a.gi55 = e.ci02
left outer join KLPGA.dbo.Course_Info f on a.gi22 = f.ci01 and a.gi56 = f.ci02
WHERE
gi01 = #{gameCode} -- 대회코드
## 연장전 정보
## getExtraScore
SELECT
PS01 AS gameCode -- 대회코드
, PLAYER_CD AS playerCode -- 선수코드
, klpga.dbo.FN_PLAYER_NAME_QUERY(PLAYER_CD,'kor') AS playerName -- 선수명
, klpga.dbo.FN_PLAYER_NAME_QUERY(PLAYER_CD,'eng') AS playerEngName -- 선수영문명
,ISNULL(CASE WHEN PS04 != 'H0' THEN ISNULL(PS04,EXTRA_1H) ELSE EXTRA_1H END ,0) as extra1h
,ISNULL(CASE WHEN PS05 != 'H0' THEN ISNULL(PS05,EXTRA_2H) ELSE EXTRA_2H END ,0) as extra2h
,ISNULL(CASE WHEN PS06 != 'H0' THEN ISNULL(PS06,EXTRA_3H) ELSE EXTRA_3H END ,0) as extra3h
,ISNULL(CASE WHEN PS07 != 'H0' THEN ISNULL(PS07,EXTRA_4H) ELSE EXTRA_4H END ,0) as extra4h
,ISNULL(CASE WHEN PS08 != 'H0' THEN ISNULL(PS08,EXTRA_5H) ELSE EXTRA_5H END ,0) as extra5h
,ISNULL(CASE WHEN PS09 != 'H0' THEN ISNULL(PS09,EXTRA_6H) ELSE EXTRA_6H END ,0) as extra6h
,ISNULL(CASE WHEN PS10 != 'H0' THEN ISNULL(PS10,EXTRA_7H) ELSE EXTRA_7H END ,0) as extra7h
,ISNULL(CASE WHEN PS11 != 'H0' THEN ISNULL(PS11,EXTRA_8H) ELSE EXTRA_8H END ,0) as extra8h
,ISNULL(CASE WHEN PS12 != 'H0' THEN ISNULL(PS12,EXTRA_9H) ELSE EXTRA_9H END ,0) as extra9h
,ISNULL(CASE WHEN PS13 != 'H0' THEN ISNULL(PS13,EXTRA_10H) ELSE EXTRA_10H END ,0) as extra10h
,ISNULL(CASE WHEN PS14 != 'H0' THEN ISNULL(PS14,EXTRA_11H) ELSE EXTRA_11H END ,0) as extra11h
,ISNULL(CASE WHEN PS15 != 'H0' THEN ISNULL(PS15,EXTRA_12H) ELSE EXTRA_12H END ,0) as extra12h
,ISNULL(CASE WHEN PS16 != 'H0' THEN ISNULL(PS16,EXTRA_13H) ELSE EXTRA_13H END ,0) as extra13h
,ISNULL(CASE WHEN PS17 != 'H0' THEN ISNULL(PS17,EXTRA_14H) ELSE EXTRA_14H END ,0) as extra14h
,ISNULL(CASE WHEN PS18 != 'H0' THEN ISNULL(PS18,EXTRA_15H) ELSE EXTRA_15H END ,0) as extra15h
,ISNULL(CASE WHEN PS19 != 'H0' THEN ISNULL(PS19,EXTRA_16H) ELSE EXTRA_16H END ,0) as extra16h
,ISNULL(CASE WHEN PS20 != 'H0' THEN ISNULL(PS20,EXTRA_17H) ELSE EXTRA_17H END ,0) as extra17h
,ISNULL(CASE WHEN PS21 != 'H0' THEN ISNULL(PS21,EXTRA_18H) ELSE EXTRA_18H END ,0) as extra18h -- 1~18번째홀까지 스코어
,ISNULL(PS.PS24,0) as extraResult -- 승자구분플래그 1:승, 0:패
FROM
Game_Info
JOIN KLPGA.dbo.CNPS_EXTRAINNING EX ON Gi01=GAME_CD
LEFT OUTER JOIN KLPGA.dbo.POFF_SCORE PS ON GAME_CD = PS01 AND PLAYER_CD = PS03
WHERE
GAME_CD = #{gameCode} -- 대회코드
ORDER BY playerName
## 리더보드
## getLeaderboardList
* 하단 오더 구분있음 오더부분참고필요
DECLARE @gameCode VARCHAR(10);
SET @gameCode = #{gameCode}; -- 게임코드
SELECT
rowNum
, rank -- 순위
, sameRankCount -- 공동순위카운팅
, gameCode -- 게임코드
, round -- 라운드
, playerCode -- 선수코드
, playerName -- 선수명
, playerEngName -- 선수영문명
, playerImg -- 선수이미지파일명 https://klpga.co.kr/PHOTOIMG/[playerImg]
, nat -- 국가
, level -- 선수신분 (FU,FT,AS,AT ...)
, giveupFlag -- 기권여부 (1 : 정상 , 3:다음라운드기권, 4:현재라운드기권, 5:실격, 7:불참, 8:무단불참, 9:기준타이상컷)
, round1score -- 1라운드 스코어
, round2score -- 2라운드 스코어
, round3score -- 3라운드 스코어
, round4score -- 4라운드 스코어
, totScore -- 전체
, todayUnderPar -- 투데이언더파
, totUnderPar -- 전체언더파
, outin -- outin 구분 (0:out , 1:in)
, groupNo -- 조번호 표출용
, groupNo2 -- 조번호 (백엔드 이용 사용안해도됨)
, groupOrder -- 조 내의 선수 index
, teeTime -- 출발시각
, finishHole -- 완료홀수
, ingHole -- 현재진행중인홀
, sponsorCode -- 스폰서코드 https://klpga.co.kr/images/web/sponser/[sponsorCode].png 추후 URL변경예정
, sponsorURL -- 스폰서 URL
, ballModelText -- 사용볼명
, prevRank -- 전라운드 랭크 (등락을 위함)
, outScore -- 아웃 스코어
, inScore -- 인 스코어
, score1h, score2h, score3h, score4h, score5h, score6h, score7h, score8h, score9h
, score10h, score11h, score12h, score13h, score14h, score15h, score16h, score17h, score18h -- 1~18홀 스코어
, stdScore1h, stdScore2h, stdScore3h, stdScore4h, stdScore5h, stdScore6h, stdScore7h, stdScore8h, stdScore9h
, stdScore10h, stdScore11h, stdScore12h, stdScore13h, stdScore14h, stdScore15h, stdScore16h, stdScore17h, stdScore18h -- 1~18홀 기준파
, COUNT(1) OVER(PARTITION BY round, groupNo) AS sameGroupCount -- 같은그룹 카운팅
, row_number() OVER(partition by round, groupNo ORDER BY groupOrder) AS groupOrder2 -- 조내의 선수 index2 (백엔드이용 사용안해도됨)
FROM
(
-- MAIN
SELECT
row_number() OVER(partition by Gp03 ORDER BY extraScore, totScore, finishHole DESC, inScore, inScore6, inScore3
, SCORE18H, SCORE17H, SCORE16H, SCORE15H, SCORE14H, SCORE13H, SCORE12H, SCORE11H, SCORE10H, SCORE9H, SCORE8H
, SCORE7H, SCORE6H, SCORE5H, SCORE4H, SCORE3H, SCORE2H, SCORE1H, backcountPrevRank, Gp06, gp07, gp09, pr02)
AS rowNum
, CASE
WHEN
(CASE
WHEN Gp03 = '1' AND GI20 = 0 THEN 0
WHEN Gp03 = '2' AND isNull(GI114,GI20) = 0 THEN 0
WHEN Gp03 = '3' AND isNull(GI115,GI20) = 0 THEN 0
WHEN Gp03 = '4' AND isNull(GI116,GI20) = 0 THEN 0
WHEN Gp03 = '5' AND isNull(GI117,GI20) = 0 THEN 0
ELSE 1
END) = 0
THEN RANK() OVER(partition by Gp03 ORDER BY extraScore, totScore)
ELSE row_number() OVER(partition by Gp03 ORDER BY extraScore, totScore, finishHole DESC, inScore, inScore6, inScore3
, SCORE18H, SCORE17H, SCORE16H, SCORE15H, SCORE14H, SCORE13H, SCORE12H, SCORE11H, SCORE10H, SCORE9H, SCORE8H
, SCORE7H, SCORE6H, SCORE5H, SCORE4H, SCORE3H, SCORE2H, SCORE1H, backcountPrevRank, Gp06, gp07, gp09, pr02)
END AS rank
, CASE
WHEN
(CASE
WHEN Gp03 = '1' AND GI20 = 0 THEN 0
WHEN Gp03 = '2' AND isNull(GI114,GI20) = 0 THEN 0
WHEN Gp03 = '3' AND isNull(GI115,GI20) = 0 THEN 0
WHEN Gp03 = '4' AND isNull(GI116,GI20) = 0 THEN 0
WHEN Gp03 = '5' AND isNull(GI117,GI20) = 0 THEN 0
ELSE 1
END) = 0
THEN COUNT(1) OVER(PARTITION BY extraScore, totScore)
ELSE 1
END AS sameRankCount
, Gp01 AS gameCode
, Gp03 AS round
, Gp02 AS playerCode
, klpga.dbo.FN_PLAYER_NAME_QUERY(Gp02,'kor') AS playerName
, klpga.dbo.FN_PLAYER_NAME_QUERY(Gp02,'eng') AS playerEngName
, Pr21 AS playerImg
, Pr07 AS nat
, Pr19 AS level
, Gp11 AS giveupFlag
, (CASE WHEN Gp03 >= 1 THEN CONVERT(varchar,round1score) ELSE '' END) AS round1score
, (CASE WHEN Gp03 >= 2 THEN CONVERT(varchar,round2score) ELSE '' END) AS round2score
, (CASE WHEN Gp03 >= 3 THEN CONVERT(varchar,round3score) ELSE '' END) AS round3score
, (CASE WHEN Gp03 >= 4 THEN CONVERT(varchar,round4score) ELSE '' END) AS round4score
, CONVERT(varchar, totScore) AS totScore
, CONVERT(varchar, todayUnderPar) AS todayUnderPar
, CONVERT(varchar, totUnderPar) AS totUnderPar
, Gp08 AS outin
, Gp06 AS groupNo
, Gp05 AS groupNo2
, Gp07 AS groupOrder
, Gp09 AS teeTime
, finishHole
, ingHole
, isNull((SELECT HIS.SPONSOR_CODE FROM SPONSOR_HISTORY HIS JOIN SPONSOR_LOGO LOGO ON HIS.SPONSOR_CODE = LOGO.SPONSOR_CODE WHERE HIS.PLAYER_CODE = Gp02),'') AS sponsorCode
, isNull((SELECT LOGO.SPONSOR_URL FROM SPONSOR_HISTORY HIS JOIN SPONSOR_LOGO LOGO ON HIS.SPONSOR_CODE = LOGO.SPONSOR_CODE WHERE HIS.PLAYER_CODE = Gp02),'') AS sponsorURL
, isNull((SELECT ballKeyText FROM titlelist_ball_player WHERE playerCode=GP02),'') AS ballModelText
, prevRank AS prevRank
, CONVERT(int,outScore) AS outScore
, CONVERT(int,inScore) AS inScore
, CONVERT(int,score1h) AS score1h, CONVERT(int,score2h) AS score2h, CONVERT(int,score3h) AS score3h, CONVERT(int,score4h) AS score4h, CONVERT(int,score5h) AS score5h
, CONVERT(int,score6h) AS score6h, CONVERT(int,score7h) AS score7h, CONVERT(int,score8h) AS score8h, CONVERT(int,score9h) AS score9h
, CONVERT(int,score10h) AS score10h, CONVERT(int,score11h) AS score11h, CONVERT(int,score12h) AS score12h, CONVERT(int,score13h) AS score13h, CONVERT(int,score14h) AS score14h
, CONVERT(int,score15h) AS score15h, CONVERT(int,score16h) AS score16h, CONVERT(int,score17h) AS score17h, CONVERT(int,score18h) AS score18h
, stdScore1h, stdScore2h, stdScore3h, stdScore4h, stdScore5h, stdScore6h, stdScore7h, stdScore8h, stdScore9h
, stdScore10h, stdScore11h, stdScore12h, stdScore13h, stdScore14h, stdScore15h, stdScore16h, stdScore17h, stdScore18h
FROM
(SELECT
score.*
, pr.*
, isnull((
SELECT
CASE
WHEN PS04 IS NOT NULL THEN
( CASE WHEN ISNULL(CAST(RIGHT(PS04,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(PS04,1) as smallint),20) END + CASE WHEN ISNULL(CAST(RIGHT(PS05,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(PS05,1) as smallint),0) END + CASE WHEN ISNULL(CAST(RIGHT(PS06,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(PS06,1) as smallint),0) END + CASE WHEN ISNULL(CAST(RIGHT(PS07,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(PS07,1) as smallint),0) END
+ CASE WHEN ISNULL(CAST(RIGHT(PS08,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(PS08,1) as smallint),20) END + CASE WHEN ISNULL(CAST(RIGHT(PS09,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(PS09,1) as smallint),0) END + CASE WHEN ISNULL(CAST(RIGHT(PS10,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(PS10,1) as smallint),0) END
+ CASE WHEN ISNULL(CAST(RIGHT(PS11,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(PS11,1) as smallint),20) END + CASE WHEN ISNULL(CAST(RIGHT(PS12,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(PS12,1) as smallint),0) END + CASE WHEN ISNULL(CAST(RIGHT(PS13,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(PS13,1) as smallint),0) END
+ CASE WHEN ISNULL(CAST(RIGHT(PS14,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(PS14,1) as smallint),20) END + CASE WHEN ISNULL(CAST(RIGHT(PS15,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(PS15,1) as smallint),0) END + CASE WHEN ISNULL(CAST(RIGHT(PS16,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(PS16,1) as smallint),0) END
+ CASE WHEN ISNULL(CAST(RIGHT(PS17,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(PS17,1) as smallint),20) END + CASE WHEN ISNULL(CAST(RIGHT(PS18,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(PS18,1) as smallint),0) END + CASE WHEN ISNULL(CAST(RIGHT(PS19,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(PS19,1) as smallint),0) END
+ CASE WHEN ISNULL(CAST(RIGHT(PS20,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(PS20,1) as smallint),20) END + CASE WHEN ISNULL(CAST(RIGHT(PS21,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(PS21,1) as smallint),0) END)
ELSE
CASE
WHEN EXTRA_1H IS NOT NULL THEN
( CASE WHEN ISNULL(CAST(RIGHT(EXTRA_1H,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(EXTRA_1H,1) as smallint),20) END + CASE WHEN ISNULL(CAST(RIGHT(EXTRA_2H,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(EXTRA_2H,1) as smallint),20) END
+ CASE WHEN ISNULL(CAST(RIGHT(EXTRA_3H,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(EXTRA_3H,1) as smallint),20) END + CASE WHEN ISNULL(CAST(RIGHT(EXTRA_4H,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(EXTRA_4H,1) as smallint),20) END
+ CASE WHEN ISNULL(CAST(RIGHT(EXTRA_5H,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(EXTRA_5H,1) as smallint),20) END + CASE WHEN ISNULL(CAST(RIGHT(EXTRA_6H,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(EXTRA_6H,1) as smallint),20) END
+ CASE WHEN ISNULL(CAST(RIGHT(EXTRA_7H,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(EXTRA_7H,1) as smallint),20) END + CASE WHEN ISNULL(CAST(RIGHT(EXTRA_8H,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(EXTRA_8H,1) as smallint),20) END
+ CASE WHEN ISNULL(CAST(RIGHT(EXTRA_9H,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(EXTRA_9H,1) as smallint),20) END + CASE WHEN ISNULL(CAST(RIGHT(EXTRA_10H,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(EXTRA_10H,1) as smallint),20) END
+ CASE WHEN ISNULL(CAST(RIGHT(EXTRA_11H,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(EXTRA_11H,1) as smallint),20) END + CASE WHEN ISNULL(CAST(RIGHT(EXTRA_12H,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(EXTRA_12H,1) as smallint),20) END
+ CASE WHEN ISNULL(CAST(RIGHT(EXTRA_13H,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(EXTRA_13H,1) as smallint),20) END + CASE WHEN ISNULL(CAST(RIGHT(EXTRA_14H,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(EXTRA_14H,1) as smallint),20) END
+ CASE WHEN ISNULL(CAST(RIGHT(EXTRA_15H,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(EXTRA_15H,1) as smallint),20) END + CASE WHEN ISNULL(CAST(RIGHT(EXTRA_16H,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(EXTRA_16H,1) as smallint),20) END
+ CASE WHEN ISNULL(CAST(RIGHT(EXTRA_17H,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(EXTRA_17H,1) as smallint),20) END + CASE WHEN ISNULL(CAST(RIGHT(EXTRA_18H,1) as smallint),20) = 0 THEN 20 ELSE ISNULL(CAST(RIGHT(EXTRA_18H,1) as smallint),20) END)
ELSE 0
END
END AS extraScore
FROM
KLPGA.dbo.CNPS_EXTRAINNING EX
LEFT OUTER JOIN KLPGA.dbo.POFF_SCORE PS ON GAME_CD = PS01 AND PLAYER_CD = PS03
WHERE
GAME_CD = Gp01
AND PLAYER_CD = GP02
AND convert(int,Gi10) - (SELECT COUNT(*) FROM round_off WHERE ro01=Gp01)= Gp03
),999) AS extraScore
, (score1h+score2h+score3h+score4h+score5h+score6h+score7h+score8h+score9h+score10h+score11h+score12h+score13h+score14h+score15h+score16h+score17h+score18h)
+ isNull((SELECT sum(score) FROM hole_score_fs WHERE gameCode=score.Gp01 AND playerCode = score.Gp02 AND round < score.Gp03),0)
AS totScore
, (score1h+score2h+score3h+score4h+score5h+score6h+score7h+score8h+score9h) AS outScore
, (score10h+score11h+score12h+score13h+score14h+score15h+score16h+score17h+score18h) AS inScore
, (score13h+score14h+score15h+score16h+score17h+score18h) AS inScore6
, (score16h+score17h+score18h) AS inScore3
, (CASE WHEN score1h > 0 THEN score1h - stdScore1h ELSE 0 END
+ CASE WHEN score2h > 0 THEN score2h - stdScore2h ELSE 0 END
+ CASE WHEN score3h > 0 THEN score3h - stdScore3h ELSE 0 END
+ CASE WHEN score4h > 0 THEN score4h - stdScore4h ELSE 0 END
+ CASE WHEN score5h > 0 THEN score5h - stdScore5h ELSE 0 END
+ CASE WHEN score6h > 0 THEN score6h - stdScore6h ELSE 0 END
+ CASE WHEN score7h > 0 THEN score7h - stdScore7h ELSE 0 END
+ CASE WHEN score8h > 0 THEN score8h - stdScore8h ELSE 0 END
+ CASE WHEN score9h > 0 THEN score9h - stdScore9h ELSE 0 END
+ CASE WHEN score10h > 0 THEN score10h - stdScore10h ELSE 0 END
+ CASE WHEN score11h > 0 THEN score11h - stdScore11h ELSE 0 END
+ CASE WHEN score12h > 0 THEN score12h - stdScore12h ELSE 0 END
+ CASE WHEN score13h > 0 THEN score13h - stdScore13h ELSE 0 END
+ CASE WHEN score14h > 0 THEN score14h - stdScore14h ELSE 0 END
+ CASE WHEN score15h > 0 THEN score15h - stdScore15h ELSE 0 END
+ CASE WHEN score16h > 0 THEN score16h - stdScore16h ELSE 0 END
+ CASE WHEN score17h > 0 THEN score17h - stdScore17h ELSE 0 END
+ CASE WHEN score18h > 0 THEN score18h - stdScore18h ELSE 0 END
) AS todayUnderPar
, (CASE WHEN score1h > 0 THEN score1h - stdScore1h ELSE 0 END
+ CASE WHEN score2h > 0 THEN score2h - stdScore2h ELSE 0 END
+ CASE WHEN score3h > 0 THEN score3h - stdScore3h ELSE 0 END
+ CASE WHEN score4h > 0 THEN score4h - stdScore4h ELSE 0 END
+ CASE WHEN score5h > 0 THEN score5h - stdScore5h ELSE 0 END
+ CASE WHEN score6h > 0 THEN score6h - stdScore6h ELSE 0 END
+ CASE WHEN score7h > 0 THEN score7h - stdScore7h ELSE 0 END
+ CASE WHEN score8h > 0 THEN score8h - stdScore8h ELSE 0 END
+ CASE WHEN score9h > 0 THEN score9h - stdScore9h ELSE 0 END
+ CASE WHEN score10h > 0 THEN score10h - stdScore10h ELSE 0 END
+ CASE WHEN score11h > 0 THEN score11h - stdScore11h ELSE 0 END
+ CASE WHEN score12h > 0 THEN score12h - stdScore12h ELSE 0 END
+ CASE WHEN score13h > 0 THEN score13h - stdScore13h ELSE 0 END
+ CASE WHEN score14h > 0 THEN score14h - stdScore14h ELSE 0 END
+ CASE WHEN score15h > 0 THEN score15h - stdScore15h ELSE 0 END
+ CASE WHEN score16h > 0 THEN score16h - stdScore16h ELSE 0 END
+ CASE WHEN score17h > 0 THEN score17h - stdScore17h ELSE 0 END
+ CASE WHEN score18h > 0 THEN score18h - stdScore18h ELSE 0 END
) + isNull((SELECT sum(underPar) FROM hole_score_fs WHERE gameCode=score.Gp01 AND playerCode = score.Gp02 AND round < score.Gp03),0)
AS totUnderPar
, (CASE WHEN score1h > 0 THEN 1 ELSE 0 END
+ CASE WHEN score2h > 0 THEN 1 ELSE 0 END
+ CASE WHEN score3h > 0 THEN 1 ELSE 0 END
+ CASE WHEN score4h > 0 THEN 1 ELSE 0 END
+ CASE WHEN score5h > 0 THEN 1 ELSE 0 END
+ CASE WHEN score6h > 0 THEN 1 ELSE 0 END
+ CASE WHEN score7h > 0 THEN 1 ELSE 0 END
+ CASE WHEN score8h > 0 THEN 1 ELSE 0 END
+ CASE WHEN score9h > 0 THEN 1 ELSE 0 END
+ CASE WHEN score10h > 0 THEN 1 ELSE 0 END
+ CASE WHEN score11h > 0 THEN 1 ELSE 0 END
+ CASE WHEN score12h > 0 THEN 1 ELSE 0 END
+ CASE WHEN score13h > 0 THEN 1 ELSE 0 END
+ CASE WHEN score14h > 0 THEN 1 ELSE 0 END
+ CASE WHEN score15h > 0 THEN 1 ELSE 0 END
+ CASE WHEN score16h > 0 THEN 1 ELSE 0 END
+ CASE WHEN score17h > 0 THEN 1 ELSE 0 END
+ CASE WHEN score18h > 0 THEN 1 ELSE 0 END
) AS finishHole
, CASE
WHEN Gp08 = '0' THEN
CASE
WHEN score18h > 0 THEN '18'
WHEN score17h > 0 THEN '18'
WHEN score16h > 0 THEN '17'
WHEN score15h > 0 THEN '16'
WHEN score14h > 0 THEN '15'
WHEN score13h > 0 THEN '14'
WHEN score12h > 0 THEN '13'
WHEN score11h > 0 THEN '12'
WHEN score10h > 0 THEN '11'
WHEN score9h > 0 THEN '10'
WHEN score8h > 0 THEN '9'
WHEN score7h > 0 THEN '8'
WHEN score6h > 0 THEN '7'
WHEN score5h > 0 THEN '6'
WHEN score4h > 0 THEN '5'
WHEN score3h > 0 THEN '4'
WHEN score2h > 0 THEN '3'
WHEN score1h > 0 THEN '2'
ELSE '1'
END
ELSE
CASE
WHEN score9h > 0 THEN '9'
WHEN score8h > 0 THEN '9'
WHEN score7h > 0 THEN '8'
WHEN score6h > 0 THEN '7'
WHEN score5h > 0 THEN '6'
WHEN score4h > 0 THEN '5'
WHEN score3h > 0 THEN '4'
WHEN score2h > 0 THEN '3'
WHEN score1h > 0 THEN '2'
WHEN score18h > 0 THEN '1'
WHEN score17h > 0 THEN '18'
WHEN score16h > 0 THEN '17'
WHEN score15h > 0 THEN '16'
WHEN score14h > 0 THEN '15'
WHEN score13h > 0 THEN '14'
WHEN score12h > 0 THEN '13'
WHEN score11h > 0 THEN '12'
WHEN score10h > 0 THEN '11'
ELSE '10'
END
END AS ingHole
, (SELECT CASE
WHEN score.Gp03 = 1 THEN 0
WHEN score.Gp03 = 2 THEN Rt70
WHEN score.Gp03 = 3 THEN Rt76
WHEN score.Gp03 = 4 THEN Rt77
ELSE 0
END
FROM RankT
WHERE Rt01=score.Gp01 AND Rt02=score.Gp02
) AS prevRank
, (SELECT Rf42 FROM round_score_fs WHERE Rf01=score.Gp01 AND Rf02=score.Gp02 AND Rf03=score.Gp03-1) AS backcountPrevRank
, isNull((SELECT Rf24 FROM KLPGA.dbo.ROUND_SCORE_FS WHERE Gp01 = RF01 AND RF02 = GP02 AND RF03 = 1),'') AS round1Score
, isNull((SELECT Rf24 FROM KLPGA.dbo.ROUND_SCORE_FS WHERE Gp01 = RF01 AND RF02 = GP02 AND RF03 = 2),'') AS round2Score
, isNull((SELECT Rf24 FROM KLPGA.dbo.ROUND_SCORE_FS WHERE Gp01 = RF01 AND RF02 = GP02 AND RF03 = 3),'') AS round3Score
, isNull((SELECT Rf24 FROM KLPGA.dbo.ROUND_SCORE_FS WHERE Gp01 = RF01 AND RF02 = GP02 AND RF03 = 4),'') AS round4Score
FROM
(SELECT
gp.*
, gi.*
, isNull(isNull(Rf04, SCORE_1H),0) AS score1h
, isNull(isNull(Rf05, SCORE_2H),0) AS score2h
, isNull(isNull(Rf06, SCORE_3H),0) AS score3h
, isNull(isNull(Rf07, SCORE_4H),0) AS score4h
, isNull(isNull(Rf08, SCORE_5H),0) AS score5h
, isNull(isNull(Rf09, SCORE_6H),0) AS score6h
, isNull(isNull(Rf10, SCORE_7H),0) AS score7h
, isNull(isNull(Rf11, SCORE_8H),0) AS score8h
, isNull(isNull(Rf12, SCORE_9H),0) AS score9h
, isNull(isNull(Rf13, SCORE_10H),0) AS score10h
, isNull(isNull(Rf14, SCORE_11H),0) AS score11h
, isNull(isNull(Rf15, SCORE_12H),0) AS score12h
, isNull(isNull(Rf16, SCORE_13H),0) AS score13h
, isNull(isNull(Rf17, SCORE_14H),0) AS score14h
, isNull(isNull(Rf18, SCORE_15H),0) AS score15h
, isNull(isNull(Rf19, SCORE_16H),0) AS score16h
, isNull(isNull(Rf20, SCORE_17H),0) AS score17h
, isNull(isNull(Rf21, SCORE_18H),0) AS score18h
, CONVERT(INT, SUBSTRING(GI40, 1, 1)) AS stdScore1h
, CONVERT(INT, SUBSTRING(GI40, 2, 1)) AS stdScore2h
, CONVERT(INT, SUBSTRING(GI40, 3, 1)) AS stdScore3h
, CONVERT(INT, SUBSTRING(GI40, 4, 1)) AS stdScore4h
, CONVERT(INT, SUBSTRING(GI40, 5, 1)) AS stdScore5h
, CONVERT(INT, SUBSTRING(GI40, 6, 1)) AS stdScore6h
, CONVERT(INT, SUBSTRING(GI40, 7, 1)) AS stdScore7h
, CONVERT(INT, SUBSTRING(GI40, 8, 1)) AS stdScore8h
, CONVERT(INT, SUBSTRING(GI40, 9, 1)) AS stdScore9h
, CONVERT(INT, SUBSTRING(GI40, 10, 1)) AS stdScore10h
, CONVERT(INT, SUBSTRING(GI40, 11, 1)) AS stdScore11h
, CONVERT(INT, SUBSTRING(GI40, 12, 1)) AS stdScore12h
, CONVERT(INT, SUBSTRING(GI40, 13, 1)) AS stdScore13h
, CONVERT(INT, SUBSTRING(GI40, 14, 1)) AS stdScore14h
, CONVERT(INT, SUBSTRING(GI40, 15, 1)) AS stdScore15h
, CONVERT(INT, SUBSTRING(GI40, 16, 1)) AS stdScore16h
, CONVERT(INT, SUBSTRING(GI40, 17, 1)) AS stdScore17h
, CONVERT(INT, SUBSTRING(GI40, 18, 1)) AS stdScore18h
FROM
groupG gp
JOIN Game_Info gi ON Gp01 = Gi01
LEFT JOIN CNPS_SCORE ON GAME_CD=Gp01 AND PLAYER_CD=Gp02 AND ROUND_NO=Gp03
LEFT JOIN Round_Score_Fs ON Rf01=Gp01 AND Rf02=Gp02 AND Rf03= Gp03
WHERE
Gp01= @gameCode
--AND Gp03=@round
AND Gp11 IN ('1','3','9')
) score
JOIN profile pr ON score.Gp02=pr.pr01
) T
-- 현재라운드 기권
UNION ALL
SELECT
999 AS rowNum
, 999 AS rank
, 0 AS sameRankCount
, Gp01 AS gameCode
, Gp03 AS round
, Gp02 AS playerCode
, klpga.dbo.FN_PLAYER_NAME_QUERY(Gp02,'kor') AS playerName
, klpga.dbo.FN_PLAYER_NAME_QUERY(Gp02,'eng') AS playerEngName
, Pr21 AS playerImg
, Pr07 AS nat
, Pr19 AS level
, Gp11 AS giveupFlag
, (CASE WHEN Gp03 >= 1 THEN CONVERT(varchar,round1score) ELSE '' END) AS round1score
, (CASE WHEN Gp03 >= 2 THEN CONVERT(varchar,round2score) ELSE '' END) AS round2score
, (CASE WHEN Gp03 >= 3 THEN CONVERT(varchar,round3score) ELSE '' END) AS round3score
, (CASE WHEN Gp03 >= 4 THEN CONVERT(varchar,round4score) ELSE '' END) AS round4score
, CONVERT(int,totScore) AS totScore
, todayUnderPar AS todayUnderPar
, totUnderPar AS totUnderPar
, Gp08 AS outin
, Gp06 AS groupNo
, Gp05 AS groupNo2
, Gp07 AS groupOrder
, Gp09 AS teeTime
, finishHole
, ingHole
, isNull((SELECT HIS.SPONSOR_CODE FROM SPONSOR_HISTORY HIS JOIN SPONSOR_LOGO LOGO ON HIS.SPONSOR_CODE = LOGO.SPONSOR_CODE WHERE HIS.PLAYER_CODE = Gp02),'') AS sponsorCode
, isNull((SELECT LOGO.SPONSOR_URL FROM SPONSOR_HISTORY HIS JOIN SPONSOR_LOGO LOGO ON HIS.SPONSOR_CODE = LOGO.SPONSOR_CODE WHERE HIS.PLAYER_CODE = Gp02),'') AS sponsorURL
, isNull((SELECT ballKeyText FROM titlelist_ball_player WHERE playerCode=GP02),'') AS ballModelText
, prevRank AS prevRank
, CONVERT(int,outScore) AS outScore
, CONVERT(int,inScore) AS inScore
, CONVERT(int,score1h) AS score1h, CONVERT(int,score2h) AS score2h, CONVERT(int,score3h) AS score3h, CONVERT(int,score4h) AS score4h, CONVERT(int,score5h) AS score5h
, CONVERT(int,score6h) AS score6h, CONVERT(int,score7h) AS score7h, CONVERT(int,score8h) AS score8h, CONVERT(int,score9h) AS score9h
, CONVERT(int,score10h) AS score10h, CONVERT(int,score11h) AS score11h, CONVERT(int,score12h) AS score12h, CONVERT(int,score13h) AS score13h, CONVERT(int,score14h) AS score14h
, CONVERT(int,score15h) AS score15h, CONVERT(int,score16h) AS score16h, CONVERT(int,score17h) AS score17h, CONVERT(int,score18h) AS score18h
, stdScore1h, stdScore2h, stdScore3h, stdScore4h, stdScore5h, stdScore6h, stdScore7h, stdScore8h, stdScore9h
, stdScore10h, stdScore11h, stdScore12h, stdScore13h, stdScore14h, stdScore15h, stdScore16h, stdScore17h, stdScore18h
FROM
(SELECT
score.*
, pr.*
, 0 AS totScore
, (score1h+score2h+score3h+score4h+score5h+score6h+score7h+score8h+score9h) AS outScore
, (score10h+score11h+score12h+score13h+score14h+score15h+score16h+score17h+score18h) AS inScore
, 0 AS todayUnderPar
, 0 AS totUnderPar
, 0 AS finishHole
, 0 AS ingHole
, 0 AS prevRank
, 0 AS backcountPrevRank
, isNull((SELECT Rf24 FROM KLPGA.dbo.ROUND_SCORE_FS WHERE Gp01 = RF01 AND RF02 = GP02 AND RF03 = 1),'') AS round1Score
, isNull((SELECT Rf24 FROM KLPGA.dbo.ROUND_SCORE_FS WHERE Gp01 = RF01 AND RF02 = GP02 AND RF03 = 2),'') AS round2Score
, isNull((SELECT Rf24 FROM KLPGA.dbo.ROUND_SCORE_FS WHERE Gp01 = RF01 AND RF02 = GP02 AND RF03 = 3),'') AS round3Score
, isNull((SELECT Rf24 FROM KLPGA.dbo.ROUND_SCORE_FS WHERE Gp01 = RF01 AND RF02 = GP02 AND RF03 = 4),'') AS round4Score
FROM
(SELECT
gp.*
, gi.*
, isNull(isNull(Rf04, SCORE_1H),0) AS score1h
, isNull(isNull(Rf05, SCORE_2H),0) AS score2h
, isNull(isNull(Rf06, SCORE_3H),0) AS score3h
, isNull(isNull(Rf07, SCORE_4H),0) AS score4h
, isNull(isNull(Rf08, SCORE_5H),0) AS score5h
, isNull(isNull(Rf09, SCORE_6H),0) AS score6h
, isNull(isNull(Rf10, SCORE_7H),0) AS score7h
, isNull(isNull(Rf11, SCORE_8H),0) AS score8h
, isNull(isNull(Rf12, SCORE_9H),0) AS score9h
, isNull(isNull(Rf13, SCORE_10H),0) AS score10h
, isNull(isNull(Rf14, SCORE_11H),0) AS score11h
, isNull(isNull(Rf15, SCORE_12H),0) AS score12h
, isNull(isNull(Rf16, SCORE_13H),0) AS score13h
, isNull(isNull(Rf17, SCORE_14H),0) AS score14h
, isNull(isNull(Rf18, SCORE_15H),0) AS score15h
, isNull(isNull(Rf19, SCORE_16H),0) AS score16h
, isNull(isNull(Rf20, SCORE_17H),0) AS score17h
, isNull(isNull(Rf21, SCORE_18H),0) AS score18h
, CONVERT(INT, SUBSTRING(GI40, 1, 1)) AS stdScore1h
, CONVERT(INT, SUBSTRING(GI40, 2, 1)) AS stdScore2h
, CONVERT(INT, SUBSTRING(GI40, 3, 1)) AS stdScore3h
, CONVERT(INT, SUBSTRING(GI40, 4, 1)) AS stdScore4h
, CONVERT(INT, SUBSTRING(GI40, 5, 1)) AS stdScore5h
, CONVERT(INT, SUBSTRING(GI40, 6, 1)) AS stdScore6h
, CONVERT(INT, SUBSTRING(GI40, 7, 1)) AS stdScore7h
, CONVERT(INT, SUBSTRING(GI40, 8, 1)) AS stdScore8h
, CONVERT(INT, SUBSTRING(GI40, 9, 1)) AS stdScore9h
, CONVERT(INT, SUBSTRING(GI40, 10, 1)) AS stdScore10h
, CONVERT(INT, SUBSTRING(GI40, 11, 1)) AS stdScore11h
, CONVERT(INT, SUBSTRING(GI40, 12, 1)) AS stdScore12h
, CONVERT(INT, SUBSTRING(GI40, 13, 1)) AS stdScore13h
, CONVERT(INT, SUBSTRING(GI40, 14, 1)) AS stdScore14h
, CONVERT(INT, SUBSTRING(GI40, 15, 1)) AS stdScore15h
, CONVERT(INT, SUBSTRING(GI40, 16, 1)) AS stdScore16h
, CONVERT(INT, SUBSTRING(GI40, 17, 1)) AS stdScore17h
, CONVERT(INT, SUBSTRING(GI40, 18, 1)) AS stdScore18h
FROM
groupG gp
JOIN Game_Info gi ON Gp01 = Gi01
LEFT JOIN CNPS_SCORE ON GAME_CD=Gp01 AND PLAYER_CD=Gp02 AND ROUND_NO=Gp03
LEFT JOIN Round_Score_Fs ON Rf01=Gp01 AND Rf02=Gp02 AND Rf03= Gp03
WHERE
Gp01= @gameCode
--AND Gp03=@round
AND Gp11 NOT IN ('1','3','9')
) score
JOIN profile pr ON score.Gp02=pr.pr01
) T
-- 다음 라운드 기권, 88타컷
UNION ALL
SELECT
999 AS rowNum
, 999 AS rank
, 0 AS sameRankCount
, Gp01 AS gameCode
, CONVERT(int,Gp03)-1 AS round
, Gp02 AS playerCode
, klpga.dbo.FN_PLAYER_NAME_QUERY(Gp02,'kor') AS playerName
, klpga.dbo.FN_PLAYER_NAME_QUERY(Gp02,'eng') AS playerEngName
, Pr21 AS playerImg
, Pr07 AS nat
, Pr19 AS level
, Gp11 AS giveupFlag
, (CASE WHEN Gp03 >= 1 THEN CONVERT(varchar,round1score) ELSE '' END) AS round1score
, (CASE WHEN Gp03 >= 2 THEN CONVERT(varchar,round2score) ELSE '' END) AS round2score
, (CASE WHEN Gp03 >= 3 THEN CONVERT(varchar,round3score) ELSE '' END) AS round3score
, (CASE WHEN Gp03 >= 4 THEN CONVERT(varchar,round4score) ELSE '' END) AS round4score
, CONVERT(int,totScore) AS totScore
, todayUnderPar AS todayUnderPar
, totUnderPar AS totUnderPar
, Gp08 AS outin
, Gp06 AS groupNo
, Gp05 AS groupNo2
, Gp07 AS groupOrder
, Gp09 AS teeTime
, finishHole
, ingHole
, isNull((SELECT HIS.SPONSOR_CODE FROM SPONSOR_HISTORY HIS JOIN SPONSOR_LOGO LOGO ON HIS.SPONSOR_CODE = LOGO.SPONSOR_CODE WHERE HIS.PLAYER_CODE = Gp02),'') AS sponsorCode
, isNull((SELECT LOGO.SPONSOR_URL FROM SPONSOR_HISTORY HIS JOIN SPONSOR_LOGO LOGO ON HIS.SPONSOR_CODE = LOGO.SPONSOR_CODE WHERE HIS.PLAYER_CODE = Gp02),'') AS sponsorURL
, isNull((SELECT ballKeyText FROM titlelist_ball_player WHERE playerCode=GP02),'') AS ballModelText
, prevRank AS prevRank
, CONVERT(int,outScore) AS outScore
, CONVERT(int,inScore) AS inScore
, CONVERT(int,score1h) AS score1h, CONVERT(int,score2h) AS score2h, CONVERT(int,score3h) AS score3h, CONVERT(int,score4h) AS score4h, CONVERT(int,score5h) AS score5h
, CONVERT(int,score6h) AS score6h, CONVERT(int,score7h) AS score7h, CONVERT(int,score8h) AS score8h, CONVERT(int,score9h) AS score9h
, CONVERT(int,score10h) AS score10h, CONVERT(int,score11h) AS score11h, CONVERT(int,score12h) AS score12h, CONVERT(int,score13h) AS score13h, CONVERT(int,score14h) AS score14h
, CONVERT(int,score15h) AS score15h, CONVERT(int,score16h) AS score16h, CONVERT(int,score17h) AS score17h, CONVERT(int,score18h) AS score18h
, stdScore1h, stdScore2h, stdScore3h, stdScore4h, stdScore5h, stdScore6h, stdScore7h, stdScore8h, stdScore9h
, stdScore10h, stdScore11h, stdScore12h, stdScore13h, stdScore14h, stdScore15h, stdScore16h, stdScore17h, stdScore18h
FROM
(SELECT
score.*
, pr.*
, 0 AS totScore
, (score1h+score2h+score3h+score4h+score5h+score6h+score7h+score8h+score9h) AS outScore
, (score10h+score11h+score12h+score13h+score14h+score15h+score16h+score17h+score18h) AS inScore
, 0 AS todayUnderPar
, 0 AS totUnderPar
, 0 AS finishHole
, 0 AS ingHole
, 0 AS prevRank
, 0 AS backcountPrevRank
, isNull((SELECT Rf24 FROM KLPGA.dbo.ROUND_SCORE_FS WHERE Gp01 = RF01 AND RF02 = GP02 AND RF03 = 1),'') AS round1Score
, isNull((SELECT Rf24 FROM KLPGA.dbo.ROUND_SCORE_FS WHERE Gp01 = RF01 AND RF02 = GP02 AND RF03 = 2),'') AS round2Score
, isNull((SELECT Rf24 FROM KLPGA.dbo.ROUND_SCORE_FS WHERE Gp01 = RF01 AND RF02 = GP02 AND RF03 = 3),'') AS round3Score
, isNull((SELECT Rf24 FROM KLPGA.dbo.ROUND_SCORE_FS WHERE Gp01 = RF01 AND RF02 = GP02 AND RF03 = 4),'') AS round4Score
FROM
(SELECT
gp.*
, gi.*
, isNull(isNull(Rf04, SCORE_1H),0) AS score1h
, isNull(isNull(Rf05, SCORE_2H),0) AS score2h
, isNull(isNull(Rf06, SCORE_3H),0) AS score3h
, isNull(isNull(Rf07, SCORE_4H),0) AS score4h
, isNull(isNull(Rf08, SCORE_5H),0) AS score5h
, isNull(isNull(Rf09, SCORE_6H),0) AS score6h
, isNull(isNull(Rf10, SCORE_7H),0) AS score7h
, isNull(isNull(Rf11, SCORE_8H),0) AS score8h
, isNull(isNull(Rf12, SCORE_9H),0) AS score9h
, isNull(isNull(Rf13, SCORE_10H),0) AS score10h
, isNull(isNull(Rf14, SCORE_11H),0) AS score11h
, isNull(isNull(Rf15, SCORE_12H),0) AS score12h
, isNull(isNull(Rf16, SCORE_13H),0) AS score13h
, isNull(isNull(Rf17, SCORE_14H),0) AS score14h
, isNull(isNull(Rf18, SCORE_15H),0) AS score15h
, isNull(isNull(Rf19, SCORE_16H),0) AS score16h
, isNull(isNull(Rf20, SCORE_17H),0) AS score17h
, isNull(isNull(Rf21, SCORE_18H),0) AS score18h
, CONVERT(INT, SUBSTRING(GI40, 1, 1)) AS stdScore1h
, CONVERT(INT, SUBSTRING(GI40, 2, 1)) AS stdScore2h
, CONVERT(INT, SUBSTRING(GI40, 3, 1)) AS stdScore3h
, CONVERT(INT, SUBSTRING(GI40, 4, 1)) AS stdScore4h
, CONVERT(INT, SUBSTRING(GI40, 5, 1)) AS stdScore5h
, CONVERT(INT, SUBSTRING(GI40, 6, 1)) AS stdScore6h
, CONVERT(INT, SUBSTRING(GI40, 7, 1)) AS stdScore7h
, CONVERT(INT, SUBSTRING(GI40, 8, 1)) AS stdScore8h
, CONVERT(INT, SUBSTRING(GI40, 9, 1)) AS stdScore9h
, CONVERT(INT, SUBSTRING(GI40, 10, 1)) AS stdScore10h
, CONVERT(INT, SUBSTRING(GI40, 11, 1)) AS stdScore11h
, CONVERT(INT, SUBSTRING(GI40, 12, 1)) AS stdScore12h
, CONVERT(INT, SUBSTRING(GI40, 13, 1)) AS stdScore13h
, CONVERT(INT, SUBSTRING(GI40, 14, 1)) AS stdScore14h
, CONVERT(INT, SUBSTRING(GI40, 15, 1)) AS stdScore15h
, CONVERT(INT, SUBSTRING(GI40, 16, 1)) AS stdScore16h
, CONVERT(INT, SUBSTRING(GI40, 17, 1)) AS stdScore17h
, CONVERT(INT, SUBSTRING(GI40, 18, 1)) AS stdScore18h
FROM
groupG gp
JOIN Game_Info gi ON Gp01 = Gi01
LEFT JOIN CNPS_SCORE ON GAME_CD=Gp01 AND PLAYER_CD=Gp02 AND ROUND_NO=Gp03
LEFT JOIN Round_Score_Fs ON Rf01=Gp01 AND Rf02=Gp02 AND Rf03= Gp03
WHERE
Gp01= @gameCode
--AND Gp03=@round-1
AND Gp11 IN ('3','9')
) score
JOIN profile pr ON score.Gp02=pr.pr01
) T
)T2
WHERE
1=1
-- 조별보기 조회시 오더
AND rowNum != '999'
ORDER BY
round, groupNo, groupOrder
-- 조별보기외 다른 리스트 조회시 오더
ORDER BY
round, rowNum
## 선수스코어카드
## getPlayerScoreCard
DECLARE @gameCode varchar(10);
DECLARE @playerCode varchar(8);
SET @gameCode = '대회코드';
SET @playerCode = '선수코드';
SELECT
playerCode -- 선수코드
, playerName -- 선수명
, playerEngName -- 선수영문명
, gameCode -- 게임코드
, round -- 라운드
, outin -- outin(0:아웃,1:인)
, score1h
, score2h
, score3h
, score4h
, score5h
, score6h
, score7h
, score8h
, score9h
, score10h
, score11h
, score12h
, score13h
, score14h
, score15h
, score16h
, score17h
, score18h -- 1~18 스코어
, stdScore1h
, stdScore2h
, stdScore3h
, stdScore4h
, stdScore5h
, stdScore6h
, stdScore7h
, stdScore8h
, stdScore9h
, stdScore10h
, stdScore11h
, stdScore12h
, stdScore13h
, stdScore14h
, stdScore15h
, stdScore16h
, stdScore17h
, stdScore18h -- 1~18 기준파
, (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole<=1)
+ CASE WHEN outin = 1 THEN (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole between 10 AND 18)
ELSE 0
END AS status1h
, (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole<=2)
+ CASE WHEN outin = 1 THEN (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole between 10 AND 18)
ELSE 0
END AS status2h
, (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole<=3)
+ CASE WHEN outin = 1 THEN (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole between 10 AND 18)
ELSE 0
END AS status3h
, (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole<=4)
+ CASE WHEN outin = 1 THEN (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole between 10 AND 18)
ELSE 0
END AS status4h
, (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole<=5)
+ CASE WHEN outin = 1 THEN (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole between 10 AND 18)
ELSE 0
END AS status5h
, (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole<=6)
+ CASE WHEN outin = 1 THEN (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole between 10 AND 18)
ELSE 0
END AS status6h
, (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole<=7)
+ CASE WHEN outin = 1 THEN (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole between 10 AND 18)
ELSE 0
END AS status7h
, (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole<=8)
+ CASE WHEN outin = 1 THEN (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole between 10 AND 18)
ELSE 0
END AS status8h
, (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole<=9)
+ CASE WHEN outin = 1 THEN (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole between 10 AND 18)
ELSE 0
END AS status9h
, (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole > 9 AND hole <=10)
+ CASE WHEN outin = 0 THEN (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole between 1 AND 9)
ELSE 0
END AS status10h
, (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole > 9 AND hole <=11)
+ CASE WHEN outin = 0 THEN (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole between 1 AND 9)
ELSE 0
END AS status11h
, (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole > 9 AND hole <=12)
+ CASE WHEN outin = 0 THEN (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole between 1 AND 9)
ELSE 0
END AS status12h
, (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole > 9 AND hole <=13)
+ CASE WHEN outin = 0 THEN (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole between 1 AND 9)
ELSE 0
END AS status13h
, (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole > 9 AND hole <=14)
+ CASE WHEN outin = 0 THEN (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole between 1 AND 9)
ELSE 0
END AS status14h
, (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole > 9 AND hole <=15)
+ CASE WHEN outin = 0 THEN (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole between 1 AND 9)
ELSE 0
END AS status15h
, (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole > 9 AND hole <=16)
+ CASE WHEN outin = 0 THEN (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole between 1 AND 9)
ELSE 0
END AS status16h
, (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole > 9 AND hole <=17)
+ CASE WHEN outin = 0 THEN (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole between 1 AND 9)
ELSE 0
END AS status17h
, (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole > 9 AND hole <=18)
+ CASE WHEN outin = 0 THEN (SELECT sum(underPar) FROM CNPS_HOLE_SCORE WHERE gameCode=T.gameCode AND playerCode=T.playerCode AND round=T.round AND hole between 1 AND 9)
ELSE 0
END AS status18h -- 1~18홀까지 statusScore
, score1h+score2h+score3h+score4h+score5h+score6h+score7h+score8h+score9h
AS outScore -- outScore
, score10h+score11h+score12h+score13h+score14h+score15h+score16h+score17h+score18h
AS inScore -- inScore
FROM
(SELECT
pr01 AS playerCode
, KLPGA.dbo.FN_PLAYER_NAME(gp02, 'kor') AS playerName
, KLPGA.dbo.FN_PLAYER_NAME(gp02, 'eng') AS playerEngName
, Gp01 AS gameCode
, CONVERT(int,Gp03) AS round
, CONVERT(int,Gp08) AS outin
, CONVERT(int,isNull(isNull(Rf04, SCORE_1H),0)) AS score1h
, CONVERT(int,isNull(isNull(Rf05, SCORE_2H),0)) AS score2h
, CONVERT(int,isNull(isNull(Rf06, SCORE_3H),0)) AS score3h
, CONVERT(int,isNull(isNull(Rf07, SCORE_4H),0)) AS score4h
, CONVERT(int,isNull(isNull(Rf08, SCORE_5H),0)) AS score5h
, CONVERT(int,isNull(isNull(Rf09, SCORE_6H),0)) AS score6h
, CONVERT(int,isNull(isNull(Rf10, SCORE_7H),0)) AS score7h
, CONVERT(int,isNull(isNull(Rf11, SCORE_8H),0)) AS score8h
, CONVERT(int,isNull(isNull(Rf12, SCORE_9H),0)) AS score9h
, CONVERT(int,isNull(isNull(Rf13, SCORE_10H),0)) AS score10h
, CONVERT(int,isNull(isNull(Rf14, SCORE_11H),0)) AS score11h
, CONVERT(int,isNull(isNull(Rf15, SCORE_12H),0)) AS score12h
, CONVERT(int,isNull(isNull(Rf16, SCORE_13H),0)) AS score13h
, CONVERT(int,isNull(isNull(Rf17, SCORE_14H),0)) AS score14h
, CONVERT(int,isNull(isNull(Rf18, SCORE_15H),0)) AS score15h
, CONVERT(int,isNull(isNull(Rf19, SCORE_16H),0)) AS score16h
, CONVERT(int,isNull(isNull(Rf20, SCORE_17H),0)) AS score17h
, CONVERT(int,isNull(isNull(Rf21, SCORE_18H),0)) AS score18h
, CONVERT(INT, SUBSTRING(GI40, 1, 1)) AS stdScore1h
, CONVERT(INT, SUBSTRING(GI40, 2, 1)) AS stdScore2h
, CONVERT(INT, SUBSTRING(GI40, 3, 1)) AS stdScore3h
, CONVERT(INT, SUBSTRING(GI40, 4, 1)) AS stdScore4h
, CONVERT(INT, SUBSTRING(GI40, 5, 1)) AS stdScore5h
, CONVERT(INT, SUBSTRING(GI40, 6, 1)) AS stdScore6h
, CONVERT(INT, SUBSTRING(GI40, 7, 1)) AS stdScore7h
, CONVERT(INT, SUBSTRING(GI40, 8, 1)) AS stdScore8h
, CONVERT(INT, SUBSTRING(GI40, 9, 1)) AS stdScore9h
, CONVERT(INT, SUBSTRING(GI40, 10, 1)) AS stdScore10h
, CONVERT(INT, SUBSTRING(GI40, 11, 1)) AS stdScore11h
, CONVERT(INT, SUBSTRING(GI40, 12, 1)) AS stdScore12h
, CONVERT(INT, SUBSTRING(GI40, 13, 1)) AS stdScore13h
, CONVERT(INT, SUBSTRING(GI40, 14, 1)) AS stdScore14h
, CONVERT(INT, SUBSTRING(GI40, 15, 1)) AS stdScore15h
, CONVERT(INT, SUBSTRING(GI40, 16, 1)) AS stdScore16h
, CONVERT(INT, SUBSTRING(GI40, 17, 1)) AS stdScore17h
, CONVERT(INT, SUBSTRING(GI40, 18, 1)) AS stdScore18h
FROM
groupG
JOIN game_info ON Gp01=Gi01
LEFT JOIN profile ON Gp02=pr01
LEFT JOIN KLPGA.dbo.CNPS_SCORE ON Gp01=GAME_CD AND Gp02=PLAYER_CD AND gp03=ROUND_NO
LEFT JOIN Round_Score_Fs ON Gp01 = Rf01 AND Gp02= Rf02 AND gp03=Rf03
WHERE
1=1
AND Gp01 = @gameCode
AND Gp02 = @playerCode
) T
ORDER BY
round
## 홀정보조회
## getHoleInfo
DECLARE @gameCode varchar(10); -- 대회코드
DECLARE @round varchar(2); -- 라운드
DECLARE @hole varchar(2); -- 홀번호
SET @gameCode=#{gameCode};
SET @round=#{round};
SET @hole =#{hole};
SELECT
T.hole -- 홀번호
, T.stdScore -- 기준파
, T.avgScore -- 평균스코어
, T.rank -- 순위
, T.yds -- 야드
FROM
(SELECT
hp02 AS hole
, hp03 AS stdScore
, isNull(round(avg(convert(float,score)),2),0) AS avgScore
, RANK() OVER (PARTITION BY hp03 ORDER BY round(avg(convert(float,score)),2) - hp03 DESC) AS rank
, CASE
WHEN hp04 is null OR hp04 = '' OR @round <= 2 THEN hp04
ELSE hp06
END AS yds
FROM
klpga_data.dbo.vw_HoleParInfo
JOIN groupG ON hp01=gp01 AND gp11 in ('1','3','9')
LEFT JOIN CNPS_HOLE_SCORE ON hp01=gameCode AND hp02=hole AND gp02=playerCode AND Gp03=@round
WHERE
hp01=@gameCode
AND Gp03=@round
GROUP BY
hp02, hp03, hp04, hp06
) T
WHERE
T.hole=@hole
## 샷트래커 baseHoleInfo (홀 좌표정보)
## getBaseHoleInfo
SELECT
bi_code AS gameCode -- 대회코드
, bi_round AS round -- 라운드
, bi_hole AS hole -- 홀
, bi_sy -- 티샷좌표y축
, bi_sx -- 티샷좌표x축
, bi_gpy -- 티샷좌표y축 그린기준
, bi_gpx -- 티샷좌표x축 그린기준
, bi_gx -- 홀컵위치y축 그린기준
, bi_gy -- 홀컵위치x 그린기준
FROM
Basehole_info
WHERE
bi_code=#{gameCode} -- 대회코드
AND bi_round=#{round} -- 라운드
AND bi_hole=#{hole} -- 홀번호
## 선수 샷트래커 조회
## getShotTrackerList
SELECT
convert(varchar, (convert(int, pp_pcode))) AS playerCode -- 선수코드
, pp_round AS round -- 라운드
, pp_hole AS hole -- 홀
, pp_shot AS shot -- 샷번호
, pp_state -- 샷상태값*
, pp_x -- x축
, pp_y -- y축
, isNull(pp_greenx,0) AS pp_greenx -- 그린y축
, isNull(pp_greeny,0) AS pp_greeny -- 그린x축
, CONVERT(varchar,CONVERT(numeric(8,1),pp_distance)) AS pp_distance -- 남은거리
, CONVERT(varchar,CONVERT(numeric(8,1),pp_distanceLen)) AS pp_distanceLen --비거리
FROM
player_Play
WHERE
pp_code=#{gameCode} -- 게임코드
AND pp_round= #{round} -- 라운드
AND pp_hole=#{hole} -- 홀번호
AND convert(varchar, (convert(int, pp_pcode))) = #{playerCode} -- 선수코드
ORDER BY
pp_shot
*샷 상태값 참고
switch(v.pp_state){
case "1" : state_html += "| 페어웨이 | "; break;
case "2" : state_html += "러프 | "; break;
case "3" : state_html += "그린 | "; break;
case "4" : state_html += "OB | "; break;
case "5" : state_html += "패널티구역 | "; break;
case "6" : state_html += "벙커 | "; break;
case "7" : state_html += "분실구 | "; break;
case "8" : state_html += "벌타 | "; break;
case "9" : state_html += "그린주변벙커 | "; break;
case "10" : state_html += "홀인 | "; break;
case "12" : state_html += "프린지 | "; break;
}
## 조별보기
## getGroupPlayerScoreCard
DECLARE @gameCode varchar(10);
DECLARE @round varchar(2);
DECLARE @groupNo varchar(2);
SET @gameCode = '대회코드';
SET @round = '라운드';
SET @groupNo = '조번호';
SELECT
playerCode -- 선수코드
, playerName -- 선수명
, playerEngName -- 선수영문명
, gameCode -- 게임코드
, round -- 라운드
, outin -- outin(0:아웃,1:인)
, score1h
, score2h
, score3h
, score4h
, score5h
, score6h
, score7h
, score8h
, score9h
, score10h
, score11h
, score12h
, score13h
, score14h
, score15h
, score16h
, score17h
, score18h -- 1~18 스코어
, stdScore1h
, stdScore2h
, stdScore3h
, stdScore4h
, stdScore5h
, stdScore6h
, stdScore7h
, stdScore8h
, stdScore9h
, stdScore10h
, stdScore11h
, stdScore12h
, stdScore13h
, stdScore14h
, stdScore15h
, stdScore16h
, stdScore17h
, stdScore18h -- 1~18 기준파
, score1h+score2h+score3h+score4h+score5h+score6h+score7h+score8h+score9h
AS outScore
, score10h+score11h+score12h+score13h+score14h+score15h+score16h+score17h+score18h
AS inScore
FROM
(SELECT
KLPGA.dbo.FN_PLAYER_NAME(gp02, 'kor') AS playerName
, KLPGA.dbo.FN_PLAYER_NAME(gp02, 'eng') AS playerEngName
, pr01 AS playerCode
, Gp01 AS gameCode
, CONVERT(int,Gp03) AS round
, CONVERT(int,Gp08) AS outin
, CONVERT(int,Gp07) AS groupOrder
, CONVERT(int,isNull(isNull(Rf04, SCORE_1H),0)) AS score1h
, CONVERT(int,isNull(isNull(Rf05, SCORE_2H),0)) AS score2h
, CONVERT(int,isNull(isNull(Rf06, SCORE_3H),0)) AS score3h
, CONVERT(int,isNull(isNull(Rf07, SCORE_4H),0)) AS score4h
, CONVERT(int,isNull(isNull(Rf08, SCORE_5H),0)) AS score5h
, CONVERT(int,isNull(isNull(Rf09, SCORE_6H),0)) AS score6h
, CONVERT(int,isNull(isNull(Rf10, SCORE_7H),0)) AS score7h
, CONVERT(int,isNull(isNull(Rf11, SCORE_8H),0)) AS score8h
, CONVERT(int,isNull(isNull(Rf12, SCORE_9H),0)) AS score9h
, CONVERT(int,isNull(isNull(Rf13, SCORE_10H),0)) AS score10h
, CONVERT(int,isNull(isNull(Rf14, SCORE_11H),0)) AS score11h
, CONVERT(int,isNull(isNull(Rf15, SCORE_12H),0)) AS score12h
, CONVERT(int,isNull(isNull(Rf16, SCORE_13H),0)) AS score13h
, CONVERT(int,isNull(isNull(Rf17, SCORE_14H),0)) AS score14h
, CONVERT(int,isNull(isNull(Rf18, SCORE_15H),0)) AS score15h
, CONVERT(int,isNull(isNull(Rf19, SCORE_16H),0)) AS score16h
, CONVERT(int,isNull(isNull(Rf20, SCORE_17H),0)) AS score17h
, CONVERT(int,isNull(isNull(Rf21, SCORE_18H),0)) AS score18h
, CONVERT(INT, SUBSTRING(GI40, 1, 1)) AS stdScore1h
, CONVERT(INT, SUBSTRING(GI40, 2, 1)) AS stdScore2h
, CONVERT(INT, SUBSTRING(GI40, 3, 1)) AS stdScore3h
, CONVERT(INT, SUBSTRING(GI40, 4, 1)) AS stdScore4h
, CONVERT(INT, SUBSTRING(GI40, 5, 1)) AS stdScore5h
, CONVERT(INT, SUBSTRING(GI40, 6, 1)) AS stdScore6h
, CONVERT(INT, SUBSTRING(GI40, 7, 1)) AS stdScore7h
, CONVERT(INT, SUBSTRING(GI40, 8, 1)) AS stdScore8h
, CONVERT(INT, SUBSTRING(GI40, 9, 1)) AS stdScore9h
, CONVERT(INT, SUBSTRING(GI40, 10, 1)) AS stdScore10h
, CONVERT(INT, SUBSTRING(GI40, 11, 1)) AS stdScore11h
, CONVERT(INT, SUBSTRING(GI40, 12, 1)) AS stdScore12h
, CONVERT(INT, SUBSTRING(GI40, 13, 1)) AS stdScore13h
, CONVERT(INT, SUBSTRING(GI40, 14, 1)) AS stdScore14h
, CONVERT(INT, SUBSTRING(GI40, 15, 1)) AS stdScore15h
, CONVERT(INT, SUBSTRING(GI40, 16, 1)) AS stdScore16h
, CONVERT(INT, SUBSTRING(GI40, 17, 1)) AS stdScore17h
, CONVERT(INT, SUBSTRING(GI40, 18, 1)) AS stdScore18h
FROM
groupG
JOIN game_info ON Gp01=Gi01
LEFT JOIN profile ON Gp02=pr01
LEFT JOIN KLPGA.dbo.CNPS_SCORE ON Gp01=GAME_CD AND Gp02=PLAYER_CD AND gp03=ROUND_NO
LEFT JOIN Round_Score_Fs ON Gp01 = Rf01 AND Gp02= Rf02 AND gp03=Rf03
WHERE
1=1
AND Gp01 = @gameCode
AND Gp03 = @round
AND Gp06 = @groupNo
) T
ORDER BY
groupOrder ASC
## STATS - 스트로크게인드 조회
## getStrokesGained
declare @gameCode varchar(10), @rnd int, @playerCode varchar(8), @prevgameCode varchar(10)
declare @avg1_0 float,@avg2_0 float,@avg3_0 float,@avg4_0 float,@avg5_0 float,@avg6_0 float
declare @avg1_1 float,@avg2_1 float,@avg3_1 float,@avg4_1 float,@avg5_1 float,@avg6_1 float
declare @avg1_2 float,@avg2_2 float,@avg3_2 float,@avg4_2 float,@avg5_2 float,@avg6_2 float
declare @avg1_3 float,@avg2_3 float,@avg3_3 float,@avg4_3 float,@avg5_3 float,@avg6_3 float
declare @avg1_4 float,@avg2_4 float,@avg3_4 float,@avg4_4 float,@avg5_4 float,@avg6_4 float
set @gameCode = #{gameCode} -- 대회코드
set @playerCode = #{playerCode} -- 선수코드
select @avg1_1 = sum(case when sgtype = 1 then isnull(avgdata,0) end),
@avg2_1 = sum(case when sgtype = 2 then isnull(avgdata,0) end),
@avg3_1 = sum(case when sgtype = 3 then isnull(avgdata,0) end),
@avg4_1= sum(case when sgtype = 4 then isnull(avgdata,0) end)
from (
select sum(avgdata) avgdata, sgtype
from (
select sum(sg) sg, count(*) cnt, pp_hole, sgtype,
sum(sg) / count(*) avgdata
from (
select sum(sg) sg, pp_hole, sgtype, pp_pcode
From sgData a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
where pp_code = @gameCode
and pp_round = '1'
group by pp_hole, sgtype, pp_pcode
)a
group by pp_hole, sgtype
) a
group by sgtype
)a
set @avg5_1 = @avg1_1+@avg2_1+@avg3_1+@avg4_1
set @avg6_1 = @avg1_1+@avg2_1+@avg3_1
select @avg1_2 = sum(case when sgtype = 1 then isnull(avgdata,0) end),
@avg2_2 = sum(case when sgtype = 2 then isnull(avgdata,0) end),
@avg3_2 = sum(case when sgtype = 3 then isnull(avgdata,0) end),
@avg4_2 = sum(case when sgtype = 4 then isnull(avgdata,0) end)
from (
select sum(avgdata) avgdata, sgtype
from (
select sum(sg) sg, count(*) cnt, pp_hole, sgtype,
sum(sg) / count(*) avgdata
from (
select sum(sg) sg, pp_hole, sgtype, pp_pcode
From sgData a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
where pp_code = @gameCode
and pp_round = '2'
group by pp_hole, sgtype, pp_pcode
)a
group by pp_hole, sgtype
) a
group by sgtype
)a
set @avg5_2 = @avg1_2+@avg2_2+@avg3_2+@avg4_2
set @avg6_2 = @avg1_2+@avg2_2+@avg3_2
select @avg1_3 = sum(case when sgtype = 1 then isnull(avgdata,0) end),
@avg2_3 = sum(case when sgtype = 2 then isnull(avgdata,0) end),
@avg3_3 = sum(case when sgtype = 3 then isnull(avgdata,0) end),
@avg4_3 = sum(case when sgtype = 4 then isnull(avgdata,0) end)
from (
select sum(avgdata) avgdata, sgtype
from (
select sum(sg) sg, count(*) cnt, pp_hole, sgtype,
sum(sg) / count(*) avgdata
from (
select sum(sg) sg, pp_hole, sgtype, pp_pcode
From sgData a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
where pp_code = @gameCode
and pp_round = '3'
group by pp_hole, sgtype, pp_pcode
)a
group by pp_hole, sgtype
) a
group by sgtype
)a
set @avg5_3 = @avg1_3+@avg2_3+@avg3_3+@avg4_3
set @avg6_3 = @avg1_3+@avg2_3+@avg3_3
select @avg1_4 = sum(case when sgtype = 1 then isnull(avgdata,0) end),
@avg2_4 = sum(case when sgtype = 2 then isnull(avgdata,0) end),
@avg3_4 = sum(case when sgtype = 3 then isnull(avgdata,0) end),
@avg4_4 = sum(case when sgtype = 4 then isnull(avgdata,0) end)
from (
select sum(avgdata) avgdata, sgtype
from (
select sum(sg) sg, count(*) cnt, pp_hole, sgtype,
sum(sg) / count(*) avgdata
from (
select sum(sg) sg, pp_hole, sgtype, pp_pcode
From sgData a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
where pp_code = @gameCode
and pp_round = '4'
group by pp_hole, sgtype, pp_pcode
)a
group by pp_hole, sgtype
) a
group by sgtype
)a
set @avg5_4 = @avg1_4+@avg2_4+@avg3_4+@avg4_4
set @avg6_4 = @avg1_4+@avg2_4+@avg3_4
select @avg1_0 = sum(case when sgtype = 1 then isnull(avgdata,0) end),
@avg2_0 = sum(case when sgtype = 2 then isnull(avgdata,0) end),
@avg3_0 = sum(case when sgtype = 3 then isnull(avgdata,0) end),
@avg4_0 = sum(case when sgtype = 4 then isnull(avgdata,0) end)
from (
select sum(avgdata) avgdata, sgtype
from (
select sum(sg) sg, count(*) cnt, pp_hole, sgtype,
sum(sg) / count(*) avgdata
from (
select sum(sg) sg, pp_hole, sgtype, pp_pcode
From sgData a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
where pp_code = @gameCode
group by pp_hole, sgtype, pp_pcode
)a
group by pp_hole, sgtype
) a
group by sgtype
)a
set @avg5_0 = @avg1_0+@avg2_0+@avg3_0+@avg4_0
set @avg6_0 = @avg1_0+@avg2_0+@avg3_0
select rnk -- 순위
, round(
sg - case when sgType = 1 then @avg1_1
when sgType = 2 then @avg2_1
when sgType = 3 then @avg3_1
when sgType = 4 then @avg4_1
when sgType = 5 then @avg5_1
when sgType = 6 then @avg6_1 end ,2) sg -- 기록
, sgtype -- 기록종류 1:티샷,2:2nd3rd, 3:그린주변, 4:퍼팅, 5:total, 6:티샷~그린
, '1' AS round -- 라운드
from (
select rank() over (partition by sgType order by sum(sg) desc) rnk, sum(sg) sg, sgType, pp_pcode
From sgData a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
where pp_code = @gameCode
and pp_round = 1
group by sgType, pp_pcode
union all
select rank() over (order by sum(sg) desc) rnk, sum(sg) sg, '5' sgType, pp_pcode
From sgData a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
where pp_code = @gameCode
and pp_round = 1
group by pp_pcode
union all
select rank() over (order by sum(sg) desc) rnk, sum(sg) sg, '6' sgType, pp_pcode
From sgData a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
where pp_code = @gameCode
and pp_round = 1
and sgType <> 4
group by pp_pcode
) a
where convert(varchar,convert(int,pp_pcode)) = @playerCode
UNION ALL
select rnk,
round(
sg - case when sgType = 1 then @avg1_2
when sgType = 2 then @avg2_2
when sgType = 3 then @avg3_2
when sgType = 4 then @avg4_2
when sgType = 5 then @avg5_2
when sgType = 6 then @avg6_2 end ,2) sg,
sgtype
, '2' AS round
from (
select rank() over (partition by sgType order by sum(sg) desc) rnk, sum(sg) sg, sgType, pp_pcode
From sgData a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
where pp_code = @gameCode
and pp_round = 2
group by sgType, pp_pcode
union all
select rank() over (order by sum(sg) desc) rnk, sum(sg) sg, '5' sgType, pp_pcode
From sgData a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
where pp_code = @gameCode
and pp_round = 2
group by pp_pcode
union all
select rank() over (order by sum(sg) desc) rnk, sum(sg) sg, '6' sgType, pp_pcode
From sgData a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
where pp_code = @gameCode
and pp_round = 2
and sgType <> 4
group by pp_pcode
) a
where convert(varchar,convert(int,pp_pcode)) = @playerCode
UNION ALL
select rnk,
round(
sg - case when sgType = 1 then @avg1_3
when sgType = 2 then @avg2_3
when sgType = 3 then @avg3_3
when sgType = 4 then @avg4_3
when sgType = 5 then @avg5_3
when sgType = 6 then @avg6_3 end ,2) sg,
sgtype
, '3' AS round
from (
select rank() over (partition by sgType order by sum(sg) desc) rnk, sum(sg) sg, sgType, pp_pcode
From sgData a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
where pp_code = @gameCode
and pp_round = 3
group by sgType, pp_pcode
union all
select rank() over (order by sum(sg) desc) rnk, sum(sg) sg, '5' sgType, pp_pcode
From sgData a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
where pp_code = @gameCode
and pp_round = 3
group by pp_pcode
union all
select rank() over (order by sum(sg) desc) rnk, sum(sg) sg, '6' sgType, pp_pcode
From sgData a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
where pp_code = @gameCode
and pp_round = 3
and sgType <> 4
group by pp_pcode
) a
where convert(varchar,convert(int,pp_pcode)) = @playerCode
UNION ALL
select rnk,
round(
sg - case when sgType = 1 then @avg1_4
when sgType = 2 then @avg2_4
when sgType = 3 then @avg3_4
when sgType = 4 then @avg4_4
when sgType = 5 then @avg5_4
when sgType = 6 then @avg6_4 end ,2) sg,
sgtype
, '4' AS round
from (
select rank() over (partition by sgType order by sum(sg) desc) rnk, sum(sg) sg, sgType, pp_pcode
From sgData a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
where pp_code = @gameCode
and pp_round = 4
group by sgType, pp_pcode
union all
select rank() over (order by sum(sg) desc) rnk, sum(sg) sg, '5' sgType, pp_pcode
From sgData a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
where pp_code = @gameCode
and pp_round = 4
group by pp_pcode
union all
select rank() over (order by sum(sg) desc) rnk, sum(sg) sg, '6' sgType, pp_pcode
From sgData a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
where pp_code = @gameCode
and pp_round = 4
and sgType <> 4
group by pp_pcode
) a
where convert(varchar,convert(int,pp_pcode)) = @playerCode
UNION ALL
select rnk,
round(
sg - case when sgType = 1 then @avg1_0
when sgType = 2 then @avg2_0
when sgType = 3 then @avg3_0
when sgType = 4 then @avg4_0
when sgType = 5 then @avg5_0
when sgType = 6 then @avg6_0 end ,2) sg,
sgtype
, '0' AS round
from (
select rank() over (partition by sgType order by sum(sg) desc) rnk, sum(sg) sg, sgType, pp_pcode
From sgData a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
where pp_code = @gameCode
group by sgType, pp_pcode
union all
select rank() over (order by sum(sg) desc) rnk, sum(sg) sg, '5' sgType, pp_pcode
From sgData a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
where pp_code = @gameCode
group by pp_pcode
union all
select rank() over (order by sum(sg) desc) rnk, sum(sg) sg, '6' sgType, pp_pcode
From sgData a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
where pp_code = @gameCode
and sgType <> 4
group by pp_pcode
) a
where convert(varchar,convert(int,pp_pcode)) = @playerCode
## STATS - 스코어링 조회
## getScoring
declare @gameCode varchar(10), @playerCode varchar(8)
set @gameCode = #{gameCode}
set @playerCode = #{playerCode}
select '0' gbn -- 선수/전체 구분 0:선수, 1:대회전체
, round -- 라운드(라운드가0이면 라운드전체 1R~FR)
, eagle, birdie, par, bogey, bogeyOver -- 각기록 갯수
, round(convert(float,eagle) / cnt * 100,0) eagleYul
, round(convert(float,birdie) / cnt * 100,0) birdieYul
, round(convert(float,par) / cnt * 100,0) parYul
, round(convert(float,bogey) / cnt * 100,0) bogeyYul
, round(convert(float,bogeyOver) / cnt * 100,0) bogeyOverYul -- 각 기록율
, cnt -- count
from (
select
sum(case when underpar <= -2 then 1 else 0 end) eagle,
sum(case when underpar = -1 then 1 else 0 end) birdie,
sum(case when underpar = 0 then 1 else 0 end) par,
sum(case when underpar = 1 then 1 else 0 end) bogey,
sum(case when underpar >= 2 then 1 else 0 end) bogeyOver,
count(*) cnt,
round
from CNPS_HOLE_SCORE a
inner join groupg b on a.gameCode = b.gp01 and a.playerCode = b.gp02 and a.round = b.gp03 and gp11 in ('1','3','9')
where gamecode = @gameCode
and playerCode = @playerCode
group by round
) a
union all
select '0' gbn, round,
eagle, birdie, par, bogey, bogeyOver,
round(convert(float,eagle) / cnt * 100,0) eagleYul,
round(convert(float,birdie) / cnt * 100,0) birdieYul,
round(convert(float,par) / cnt * 100,0) parYul,
round(convert(float,bogey) / cnt * 100,0) bogeyYul,
round(convert(float,bogeyOver) / cnt * 100,0) bogeyOverYul,
cnt
from (
select
sum(case when underpar <= -2 then 1 else 0 end) eagle,
sum(case when underpar = -1 then 1 else 0 end) birdie,
sum(case when underpar = 0 then 1 else 0 end) par,
sum(case when underpar = 1 then 1 else 0 end) bogey,
sum(case when underpar >= 2 then 1 else 0 end) bogeyOver,
count(*) cnt,
0 AS round
from CNPS_HOLE_SCORE a
inner join groupg b on a.gameCode = b.gp01 and a.playerCode = b.gp02 and a.round = b.gp03 and gp11 in ('1','3','9')
where gamecode = @gameCode
and playerCode = @playerCode
) a
union all
select '1' gbn, round,
eagle, birdie, par, bogey, bogeyOver,
round(convert(float,eagle) / cnt * 100,0) eagleYul,
round(convert(float,birdie) / cnt * 100,0) birdieYul,
round(convert(float,par) / cnt * 100,0) parYul,
round(convert(float,bogey) / cnt * 100,0) bogeyYul,
round(convert(float,bogeyOver) / cnt * 100,0) bogeyOverYul,
cnt
from (
select
sum(case when underpar <= -2 then 1 else 0 end) eagle,
sum(case when underpar = -1 then 1 else 0 end) birdie,
sum(case when underpar = 0 then 1 else 0 end) par,
sum(case when underpar = 1 then 1 else 0 end) bogey,
sum(case when underpar >= 2 then 1 else 0 end) bogeyOver,
count(*) cnt,
round
from CNPS_HOLE_SCORE a
inner join groupg b on a.gameCode = b.gp01 and a.playerCode = b.gp02 and a.round = b.gp03 and gp11 in ('1','3','9')
where gamecode = @gameCode
group by round
) a
union all
select '1' gbn, round,
eagle, birdie, par, bogey, bogeyOver,
round(convert(float,eagle) / cnt * 100,0) eagleYul,
round(convert(float,birdie) / cnt * 100,0) birdieYul,
round(convert(float,par) / cnt * 100,0) parYul,
round(convert(float,bogey) / cnt * 100,0) bogeyYul,
round(convert(float,bogeyOver) / cnt * 100,0) bogeyOverYul,
cnt
from (
select
sum(case when underpar <= -2 then 1 else 0 end) eagle,
sum(case when underpar = -1 then 1 else 0 end) birdie,
sum(case when underpar = 0 then 1 else 0 end) par,
sum(case when underpar = 1 then 1 else 0 end) bogey,
sum(case when underpar >= 2 then 1 else 0 end) bogeyOver,
count(*) cnt,
0 AS round
from CNPS_HOLE_SCORE a
inner join groupg b on a.gameCode = b.gp01 and a.playerCode = b.gp02 and a.round = b.gp03 and gp11 in ('1','3','9')
where gamecode = @gameCode
) a
## STATS - 티샷 조회
## getTeeShotList
declare @gameCode varchar(10)
declare @pcode varchar(8)
set @gameCode = #{gameCode} -- 대회코드
set @pcode = #{playerCode} -- 선수코드
select '개인' gbn -- 구분
, rnd AS round -- 라운드
, stdPar -- 기준파
, case when totCnt = 0 then 0
else round(convert(float,fwCnt) / totCnt * 100,2) end fwYul -- 페어웨이안착율
, fwCnt -- 페어웨이카운트
, totCnt -- 전체카운드
, totDis -- 전체거리
, case when totDisCnt = 0 then 0
else round(convert(float,totDis) / totDisCnt,2) end totDisAvg -- 전체비거리평균
, fwDis -- 페어웨이비거리
, case when fwDisCnt = 0 then 0
else round(convert(float,fwDis) / fwDisCnt,2) end fwDisAvg -- 페어웨이비거리 평균
, totRemain -- 남은거리
, case when totDisCnt = 0 then 0
else round(convert(float,totRemain) / totDisCnt,2) end totRemainAvg -- 남은거리평균
from (
select distinct pp_round rnd from klpga.dbo.player_play where pp_code = @gameCode ) x
left outer join (
select c.stdPar,
sum(case when pp_state = '1' then 1 else 0 end) fwCnt,
count(*) totCnt,
sum(case when a.pp_state not in ('4','5','7','8') then pp_distanceLen else 0 end) totDis,
sum(case when a.pp_state not in ('4','5','7','8') then 1 else 0 end) totDisCnt,
sum(case when a.pp_state = '1' then pp_distanceLen else 0 end) fwDis,
sum(case when a.pp_state = '1' then 1 else 0 end) fwDisCnt,
sum(case when a.pp_state not in ('4','5','7','8') then pp_distance else 0 end) totRemain,
a.pp_round
from player_play a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
inner join cnps_hole_score c on a.pp_code = c.gameCode and convert(varchar,convert(int,a.pp_pcode)) = c.playerCode and a.pp_round = c.round and a.pp_hole = c.hole
where a.pp_code = @gameCode
and convert(varchar,convert(int,a.pp_pcode)) = @pcode
and c.stdPar in ('4','5')
and a.pp_shot = '1'
group by a.pp_round, c.stdPar
) a on a.pp_round = x.rnd
union all
select '전체' gbn, pp_round, stdPar,
case when totCnt = 0 then 0
else round(convert(float,fwCnt) / totCnt * 100,2) end fwYul,
fwCnt , totCnt,
totDis,
case when totDisCnt = 0 then 0
else round(convert(float,totDis) / totDisCnt,2) end totDisAvg,
fwDis,
case when fwDisCnt = 0 then 0
else round(convert(float,fwDis) / fwDisCnt,2) end fwDisAvg,
totRemain,
case when totDisCnt = 0 then 0
else round(convert(float,totRemain) / totDisCnt,2) end totRemainAvg
from (
select c.stdPar,
sum(case when pp_state = '1' then 1 else 0 end) fwCnt,
count(*) totCnt,
sum(case when a.pp_state not in ('4','5','7','8') then pp_distanceLen else 0 end) totDis,
sum(case when a.pp_state not in ('4','5','7','8') then 1 else 0 end) totDisCnt,
sum(case when a.pp_state = '1' then pp_distanceLen else 0 end) fwDis,
sum(case when a.pp_state = '1' then 1 else 0 end) fwDisCnt,
sum(case when a.pp_state not in ('4','5','7','8') then pp_distance else 0 end) totRemain,
a.pp_round
from player_play a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
inner join cnps_hole_score c on a.pp_code = c.gameCode and convert(varchar,convert(int,a.pp_pcode)) = c.playerCode and a.pp_round = c.round and a.pp_hole = c.hole
where a.pp_code = @gameCode
and c.stdPar in ('4','5')
and a.pp_shot = '1'
group by a.pp_round, c.stdPar
) a
UNION ALL
select '개인' gbn,0 as round, stdPar,
case when totCnt = 0 then 0
else round(convert(float,fwCnt) / totCnt * 100,2) end fwYul,
fwCnt , totCnt,
totDis,
case when totDisCnt = 0 then 0
else round(convert(float,totDis) / totDisCnt,2) end totDisAvg,
fwDis,
case when fwDisCnt = 0 then 0
else round(convert(float,fwDis) / fwDisCnt,2) end fwDisAvg
,totRemain
,case when totDisCnt = 0 then 0
else round(convert(float,totRemain) / totDisCnt,2) end totRemainAvg
from (
select c.stdPar,
sum(case when pp_state = '1' then 1 else 0 end) fwCnt,
count(*) totCnt,
sum(case when a.pp_state not in ('4','5','7','8') then pp_distanceLen else 0 end) totDis,
sum(case when a.pp_state not in ('4','5','7','8') then 1 else 0 end) totDisCnt,
sum(case when a.pp_state = '1' then pp_distanceLen else 0 end) fwDis,
sum(case when a.pp_state = '1' then 1 else 0 end) fwDisCnt,
sum(case when a.pp_state not in ('4','5','7','8') then pp_distance else 0 end) totRemain
from player_play a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
inner join cnps_hole_score c on a.pp_code = c.gameCode and convert(varchar,convert(int,a.pp_pcode)) = c.playerCode and a.pp_round = c.round and a.pp_hole = c.hole
where a.pp_code = @gameCode
and convert(varchar,convert(int,a.pp_pcode)) = @pcode
and c.stdPar in ('4','5')
and a.pp_shot = '1'
group by c.stdPar
) a
union all
select '전체' gbn, 0 AS round, stdPar,
case when totCnt = 0 then 0
else round(convert(float,fwCnt) / totCnt * 100,2) end fwYul,
fwCnt , totCnt,
totDis,
case when totDisCnt = 0 then 0
else round(convert(float,totDis) / totDisCnt,2) end totDisAvg,
fwDis,
case when fwDisCnt = 0 then 0
else round(convert(float,fwDis) / fwDisCnt,2) end fwDisAvg,
totRemain,
case when totDisCnt = 0 then 0
else round(convert(float,totRemain) / totDisCnt,2) end totRemainAvg
from (
select c.stdPar,
sum(case when pp_state = '1' then 1 else 0 end) fwCnt,
count(*) totCnt,
sum(case when a.pp_state not in ('4','5','7','8') then pp_distanceLen else 0 end) totDis,
sum(case when a.pp_state not in ('4','5','7','8') then 1 else 0 end) totDisCnt,
sum(case when a.pp_state = '1' then pp_distanceLen else 0 end) fwDis,
sum(case when a.pp_state = '1' then 1 else 0 end) fwDisCnt,
sum(case when a.pp_state not in ('4','5','7','8') then pp_distance else 0 end) totRemain
from player_play a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
inner join cnps_hole_score c on a.pp_code = c.gameCode and convert(varchar,convert(int,a.pp_pcode)) = c.playerCode and a.pp_round = c.round and a.pp_hole = c.hole
where a.pp_code = @gameCode
and c.stdPar in ('4','5')
and a.pp_shot = '1'
group by c.stdPar
) a
ORDER BY round, stdPar
////////////////////////////////////////////////////////////////여기서 부터
## STATS - 2nd,3rd SHOT
## getScondThirdList
declare @gameCode varchar(10)
declare @rnd varchar(1)
declare @pcode varchar(8)
set @gameCode = #{gameCode}
set @pcode = #{playerCode}
select '개인' gbn, rnd AS round, '5' AS stdPar,
case when totCnt = 0 then 0
else round(convert(float,onGreen) / totCnt * 100,2) end gir,
onGreen, totCnt,
girRemain,
case when onGreen = 0 then 0
else round(girRemain / onGreen,2) end girRemainAvg,
uBirdie,
case when onGreen = 0 then 0
else round(convert(float,uBirdie) / onGreen * 100,2) end uBirdieYul,
fwCnt_2nd,
case when totCnt = 0 then 0
else round(convert(float,fwCnt_2nd) / totCnt * 100,2) end fw_2ndYul,
dis_2nd,
case when disCnt_2nd = 0 then 0
else round(convert(float,dis_2nd) / disCnt_2nd,2) end disAvg_2nd,
disRemain_2nd,
case when disCnt_2nd = 0 then 0
else round(convert(float,disRemain_2nd) / disCnt_2nd,2) end disRemainAvg_2nd,
dis_3rd,
case when disCnt_3rd = 0 then 0
else round(convert(float,dis_3rd) / disCnt_3rd,2) end disAvg_3nd,
disRemain_3rd,
case when disCnt_3rd = 0 then 0
else round(convert(float,disRemain_3rd) / disCnt_3rd,2) end disRemainAvg_3nd,
disCnt_2nd,
disCnt_3rd
from (
select distinct pp_round rnd from klpga.dbo.player_play where pp_code = @gameCode ) x
left outer join (
select a.pp_round,
sum(c.onGreen) onGreen,
count(*) totCnt,
sum(case when c.onGreen = 1 then a.pp_distance else 0 end) girRemain,
sum(case when c.onGreen = 1 and c.underPar < 0 then 1 else 0 end) uBirdie,
sum(case when a2.pp_state = 1 then 1 else 0 end) fwCnt_2nd,
sum(a2.pp_distanceLen) dis_2nd,
sum(case when a2.pp_distanceLen = 0 then 0 else 1 end) disCnt_2nd,
sum(case when a2.pp_distanceLen = 0 then 0 else a2.pp_distance end) disRemain_2nd,
sum(a3.pp_distanceLen) dis_3rd,
sum(case when a3.pp_distanceLen = 0 then 0 else 1 end) disCnt_3rd,
sum(case when a3.pp_distanceLen = 0 then 0 else a3.pp_distance end) disRemain_3rd
from player_play a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
inner join cnps_hole_score c on a.pp_code = c.gameCode and convert(varchar,convert(int,a.pp_pcode)) = c.playerCode and a.pp_round = c.round and a.pp_hole = c.hole
left outer join player_play a2 on a.pp_code = a2.pp_code and a.pp_pcode = a2.pp_pcode and a.pp_round = a2.pp_round and a.pp_hole = a2.pp_hole and a2.pp_shot = 2
left outer join player_play a3 on a.pp_code = a3.pp_code and a.pp_pcode = a3.pp_pcode and a.pp_round = a3.pp_round and a.pp_hole = a3.pp_hole and a3.pp_shot = 3
where a.pp_code = @gameCode
and convert(varchar,convert(int,a.pp_pcode)) = @pcode
and c.stdPar = '5'
and a.pp_shot = score-puttCount
group by a.pp_round
) a on a.pp_round = x.rnd
union all
select '전체' gbn, pp_round, '5' AS stdPar,
case when totCnt = 0 then 0
else round(convert(float,onGreen) / totCnt * 100,2) end gir,
onGreen, totCnt,
girRemain,
case when onGreen = 0 then 0
else round(girRemain / onGreen,2) end girRemainAvg,
uBirdie,
case when onGreen = 0 then 0
else round(convert(float,uBirdie) / onGreen * 100,2) end uBirdieYul,
fwCnt_2nd,
case when totCnt = 0 then 0
else round(convert(float,fwCnt_2nd) / totCnt * 100,2) end fw_2ndYul,
dis_2nd,
case when disCnt_2nd = 0 then 0
else round(convert(float,dis_2nd) / disCnt_2nd,2) end disAvg_2nd,
disRemain_2nd,
case when disCnt_2nd = 0 then 0
else round(convert(float,disRemain_2nd) / disCnt_2nd,2) end disRemainAvg_2nd,
dis_3rd,
case when disCnt_3rd = 0 then 0
else round(convert(float,dis_3rd) / disCnt_3rd,2) end disAvg_3nd,
disRemain_3rd,
case when disCnt_3rd = 0 then 0
else round(convert(float,disRemain_3rd) / disCnt_3rd,2) end disRemainAvg_3nd,
disCnt_2nd,
disCnt_3rd
from (
select a.pp_round,
sum(c.onGreen) onGreen,
count(*) totCnt,
sum(case when c.onGreen = 1 then a.pp_distance else 0 end) girRemain,
sum(case when c.onGreen = 1 and c.underPar < 0 then 1 else 0 end) uBirdie,
sum(case when a2.pp_state = 1 then 1 else 0 end) fwCnt_2nd,
sum(a2.pp_distanceLen) dis_2nd,
sum(case when a2.pp_distanceLen = 0 then 0 else 1 end) disCnt_2nd,
sum(case when a2.pp_distanceLen = 0 then 0 else a2.pp_distance end) disRemain_2nd,
sum(a3.pp_distanceLen) dis_3rd,
sum(case when a3.pp_distanceLen = 0 then 0 else 1 end) disCnt_3rd,
sum(case when a3.pp_distanceLen = 0 then 0 else a3.pp_distance end) disRemain_3rd
from player_play a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
inner join cnps_hole_score c on a.pp_code = c.gameCode and convert(varchar,convert(int,a.pp_pcode)) = c.playerCode and a.pp_round = c.round and a.pp_hole = c.hole
left outer join player_play a2 on a.pp_code = a2.pp_code and a.pp_pcode = a2.pp_pcode and a.pp_round = a2.pp_round and a.pp_hole = a2.pp_hole and a2.pp_shot = 2
left outer join player_play a3 on a.pp_code = a3.pp_code and a.pp_pcode = a3.pp_pcode and a.pp_round = a3.pp_round and a.pp_hole = a3.pp_hole and a3.pp_shot = 3
where a.pp_code = @gameCode
and c.stdPar = '5'
and a.pp_shot = score-puttCount
group by a.pp_round
) a
UNION ALL
select '개인' gbn, 0 AS round, '5' AS stdPar,
case when totCnt = 0 then 0
else round(convert(float,onGreen) / totCnt * 100,2) end gir,
onGreen, totCnt,
girRemain,
case when onGreen = 0 then 0
else round(girRemain / onGreen,2) end girRemainAvg,
uBirdie,
case when onGreen = 0 then 0
else round(convert(float,uBirdie) / onGreen * 100,2) end uBirdieYul,
fwCnt_2nd,
case when totCnt = 0 then 0
else round(convert(float,fwCnt_2nd) / totCnt * 100,2) end fw_2ndYul,
dis_2nd,
case when disCnt_2nd = 0 then 0
else round(convert(float,dis_2nd) / disCnt_2nd,2) end disAvg_2nd,
disRemain_2nd,
case when disCnt_2nd = 0 then 0
else round(convert(float,disRemain_2nd) / disCnt_2nd,2) end disRemainAvg_2nd,
dis_3rd,
case when disCnt_3rd = 0 then 0
else round(convert(float,dis_3rd) / disCnt_3rd,2) end disAvg_3nd,
disRemain_3rd,
case when disCnt_3rd = 0 then 0
else round(convert(float,disRemain_3rd) / disCnt_3rd,2) end disRemainAvg_3nd,
disCnt_2nd,
disCnt_3rd
from (
select
sum(c.onGreen) onGreen,
count(*) totCnt,
sum(case when c.onGreen = 1 then a.pp_distance else 0 end) girRemain,
sum(case when c.onGreen = 1 and c.underPar < 0 then 1 else 0 end) uBirdie,
sum(case when a2.pp_state = 1 then 1 else 0 end) fwCnt_2nd,
sum(a2.pp_distanceLen) dis_2nd,
sum(case when a2.pp_distanceLen = 0 then 0 else 1 end) disCnt_2nd,
sum(case when a2.pp_distanceLen = 0 then 0 else a2.pp_distance end) disRemain_2nd,
sum(a3.pp_distanceLen) dis_3rd,
sum(case when a3.pp_distanceLen = 0 then 0 else 1 end) disCnt_3rd,
sum(case when a3.pp_distanceLen = 0 then 0 else a3.pp_distance end) disRemain_3rd
from player_play a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
inner join cnps_hole_score c on a.pp_code = c.gameCode and convert(varchar,convert(int,a.pp_pcode)) = c.playerCode and a.pp_round = c.round and a.pp_hole = c.hole
left outer join player_play a2 on a.pp_code = a2.pp_code and a.pp_pcode = a2.pp_pcode and a.pp_round = a2.pp_round and a.pp_hole = a2.pp_hole and a2.pp_shot = 2
left outer join player_play a3 on a.pp_code = a3.pp_code and a.pp_pcode = a3.pp_pcode and a.pp_round = a3.pp_round and a.pp_hole = a3.pp_hole and a3.pp_shot = 3
where a.pp_code = @gameCode
and convert(varchar,convert(int,a.pp_pcode)) = @pcode
and c.stdPar = '5'
and a.pp_shot = score-puttCount
) a
union all
select '전체' gbn, 0, '5' AS stdPar,
case when totCnt = 0 then 0
else round(convert(float,onGreen) / totCnt * 100,2) end gir,
onGreen, totCnt,
girRemain,
case when onGreen = 0 then 0
else round(girRemain / onGreen,2) end girRemainAvg,
uBirdie,
case when onGreen = 0 then 0
else round(convert(float,uBirdie) / onGreen * 100,2) end uBirdieYul,
fwCnt_2nd,
case when totCnt = 0 then 0
else round(convert(float,fwCnt_2nd) / totCnt * 100,2) end fw_2ndYul,
dis_2nd,
case when disCnt_2nd = 0 then 0
else round(convert(float,dis_2nd) / disCnt_2nd,2) end disAvg_2nd,
disRemain_2nd,
case when disCnt_2nd = 0 then 0
else round(convert(float,disRemain_2nd) / disCnt_2nd,2) end disRemainAvg_2nd,
dis_3rd,
case when disCnt_3rd = 0 then 0
else round(convert(float,dis_3rd) / disCnt_3rd,2) end disAvg_3nd,
disRemain_3rd,
case when disCnt_3rd = 0 then 0
else round(convert(float,disRemain_3rd) / disCnt_3rd,2) end disRemainAvg_3nd,
disCnt_2nd,
disCnt_3rd
from (
select
sum(c.onGreen) onGreen,
count(*) totCnt,
sum(case when c.onGreen = 1 then a.pp_distance else 0 end) girRemain,
sum(case when c.onGreen = 1 and c.underPar < 0 then 1 else 0 end) uBirdie,
sum(case when a2.pp_state = 1 then 1 else 0 end) fwCnt_2nd,
sum(a2.pp_distanceLen) dis_2nd,
sum(case when a2.pp_distanceLen = 0 then 0 else 1 end) disCnt_2nd,
sum(case when a2.pp_distanceLen = 0 then 0 else a2.pp_distance end) disRemain_2nd,
sum(a3.pp_distanceLen) dis_3rd,
sum(case when a3.pp_distanceLen = 0 then 0 else 1 end) disCnt_3rd,
sum(case when a3.pp_distanceLen = 0 then 0 else a3.pp_distance end) disRemain_3rd
from player_play a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
inner join cnps_hole_score c on a.pp_code = c.gameCode and convert(varchar,convert(int,a.pp_pcode)) = c.playerCode and a.pp_round = c.round and a.pp_hole = c.hole
left outer join player_play a2 on a.pp_code = a2.pp_code and a.pp_pcode = a2.pp_pcode and a.pp_round = a2.pp_round and a.pp_hole = a2.pp_hole and a2.pp_shot = 2
left outer join player_play a3 on a.pp_code = a3.pp_code and a.pp_pcode = a3.pp_pcode and a.pp_round = a3.pp_round and a.pp_hole = a3.pp_hole and a3.pp_shot = 3
where a.pp_code = @gameCode
and c.stdPar = '5'
and a.pp_shot = score-puttCount
) a
UNION ALL
select '개인' gbn, rnd AS round, '4' AS stdPar,
case when totCnt = 0 then 0
else round(convert(float,onGreen) / totCnt * 100,2) end gir,
onGreen, totCnt,
girRemain,
case when onGreen = 0 then 0
else round(girRemain / onGreen,2) end girRemainAvg,
uBirdie,
case when onGreen = 0 then 0
else round(convert(float,uBirdie) / onGreen * 100,2) end uBirdieYul
, null, null, null, null, null, null, null, null, null, null, null, null
from (
select distinct pp_round rnd from klpga.dbo.player_play where pp_code = @gameCode ) x
left outer join (
select a.pp_round,
sum(c.onGreen) onGreen,
count(*) totCnt,
sum(case when c.onGreen = 1 then a.pp_distance else 0 end) girRemain,
sum(case when c.onGreen = 1 and c.underPar < 0 then 1 else 0 end) uBirdie
from player_play a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
inner join cnps_hole_score c on a.pp_code = c.gameCode and convert(varchar,convert(int,a.pp_pcode)) = c.playerCode and a.pp_round = c.round and a.pp_hole = c.hole
where a.pp_code = @gameCode
and convert(varchar,convert(int,a.pp_pcode)) = @pcode
and c.stdPar = '4'
and a.pp_shot = score-puttCount
group by a.pp_round
) a on a.pp_round = x.rnd
union all
select '전체' gbn, pp_round, '4' AS stdPar,
case when totCnt = 0 then 0
else round(convert(float,onGreen) / totCnt * 100,2) end gir,
onGreen, totCnt,
girRemain,
case when onGreen = 0 then 0
else round(girRemain / onGreen,2) end girRemainAvg,
uBirdie,
case when onGreen = 0 then 0
else round(convert(float,uBirdie) / onGreen * 100,2) end uBirdieYul
, null, null, null, null, null, null, null, null, null, null, null, null
from (
select a.pp_round,
sum(c.onGreen) onGreen,
count(*) totCnt,
sum(case when c.onGreen = 1 then a.pp_distance else 0 end) girRemain,
sum(case when c.onGreen = 1 and c.underPar < 0 then 1 else 0 end) uBirdie
from player_play a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
inner join cnps_hole_score c on a.pp_code = c.gameCode and convert(varchar,convert(int,a.pp_pcode)) = c.playerCode and a.pp_round = c.round and a.pp_hole = c.hole
where a.pp_code = @gameCode
and c.stdPar = '4'
and a.pp_shot = score-puttCount
group by a.pp_round
) a
UNION ALL
select '개인' gbn, 0 AS round, '4' AS stdPar,
case when totCnt = 0 then 0
else round(convert(float,onGreen) / totCnt * 100,2) end gir,
onGreen, totCnt,
girRemain,
case when onGreen = 0 then 0
else round(girRemain / onGreen,2) end girRemainAvg,
uBirdie,
case when onGreen = 0 then 0
else round(convert(float,uBirdie) / onGreen * 100,2) end uBirdieYul
, null, null, null, null, null, null, null, null, null, null, null, null
from (
select
sum(c.onGreen) onGreen,
count(*) totCnt,
sum(case when c.onGreen = 1 then a.pp_distance else 0 end) girRemain,
sum(case when c.onGreen = 1 and c.underPar < 0 then 1 else 0 end) uBirdie
from player_play a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
inner join cnps_hole_score c on a.pp_code = c.gameCode and convert(varchar,convert(int,a.pp_pcode)) = c.playerCode and a.pp_round = c.round and a.pp_hole = c.hole
where a.pp_code = @gameCode
and convert(varchar,convert(int,a.pp_pcode)) = @pcode
and c.stdPar = '4'
and a.pp_shot = score-puttCount
) a
union all
select '전체' gbn, 0 AS round, '4' AS stdPar,
case when totCnt = 0 then 0
else round(convert(float,onGreen) / totCnt * 100,2) end gir,
onGreen, totCnt,
girRemain,
case when onGreen = 0 then 0
else round(girRemain / onGreen,2) end girRemainAvg,
uBirdie,
case when onGreen = 0 then 0
else round(convert(float,uBirdie) / onGreen * 100,2) end uBirdieYul
, null, null, null, null, null, null, null, null, null, null, null, null
from (
select
sum(c.onGreen) onGreen,
count(*) totCnt,
sum(case when c.onGreen = 1 then a.pp_distance else 0 end) girRemain,
sum(case when c.onGreen = 1 and c.underPar < 0 then 1 else 0 end) uBirdie
from player_play a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
inner join cnps_hole_score c on a.pp_code = c.gameCode and convert(varchar,convert(int,a.pp_pcode)) = c.playerCode and a.pp_round = c.round and a.pp_hole = c.hole
where a.pp_code = @gameCode
and c.stdPar = '4'
and a.pp_shot = score-puttCount
) a
ORDER BY round, stdPar, gbn
///////////////////////////////////////////////////////////////
## STATS - 그린주변
## getGreenAroundList
declare @gameCode varchar(10)
declare @pcode varchar(8)
set @gameCode = #{gameCode}
set @pcode = #{playerCode}
SELECT
T1.gbn AS gbn -- 개인,전체 구분
, T1.round AS round -- 라운드 ()
, bnkSaveYul -- 벙커세이브율
, saveCnt -- 카운트
, bnkCnt -- 벙커카운트
, saveRemain -- 샌드세이브남은거리
, saveRemainAvg -- 샌드세이브남은거리평균
, recoverYul -- 리커버리율
, recoverCnt -- 리커버리카운트
, recoverTotCnt -- 리커버리전체카운트
, disScramRemainAvg -- 스크램브링시남은거리
, disRemain -- 남은거리
, yds60cnt -- 60야드미만 카운트
, disRemainAvg AS dis60remainAvg -- 그린주변샷후남은거리 (60야드미만)
FROM
(
-- 벙커세이브율, 세이브수, 시도수, 세이브시 남은거리
select '개인' gbn, rnd AS round,
case when bnkCnt = 0 then 0
else round(convert(float,saveCnt) / bnkCnt * 100,2) end bnkSaveYul,
saveCnt, bnkCnt,
saveRemain,
case when saveCnt = 0 then 0
else round(convert(float,saveRemain) / saveCnt,2) end saveRemainAvg
from (
select distinct pp_round rnd from klpga.dbo.player_play where pp_code = @gameCode ) x
left outer join (
select a.pp_round,
count(*) bnkCnt,
sum(case when underPar <= 0 then 1 else 0 end) saveCnt,
sum(case when underPar <= 0 then a1.pp_distance else 0 end) saveRemain
from (
select rank() over(partition by pp_code, pp_pcode, pp_round, pp_hole order by pp_shot) gbn, *
from player_play a
where a.pp_code = @gameCode
and convert(varchar,convert(int,a.pp_pcode)) = @pcode
--and case when @rnd = 0 then 0 else a.pp_round end = @rnd
and a.pp_state = 9
) a
inner join player_play a1 on a.pp_code = a1.pp_code and a.pp_pcode = a1.pp_pcode and a.pp_round = a1.pp_round and a.pp_hole = a1.pp_hole and a.pp_shot = a1.pp_shot - 1
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
inner join cnps_hole_score c on a.pp_code = c.gameCode and convert(varchar,convert(int,a.pp_pcode)) = c.playerCode and a.pp_round = c.round and a.pp_hole = c.hole
where gbn = 1 -- 첫번째 벙커
group by a.pp_round
) a on a.pp_round = x.rnd
union all
select '전체' gbn, pp_round,
case when bnkCnt = 0 then 0
else round(convert(float,saveCnt) / bnkCnt * 100,2) end bnkSaveYul,
saveCnt, bnkCnt,
saveRemain,
case when saveCnt = 0 then 0
else round(convert(float,saveRemain) / saveCnt,2) end saveRemainAvg
from (
select a.pp_round,
count(*) bnkCnt,
sum(case when underPar <= 0 then 1 else 0 end) saveCnt,
sum(case when underPar <= 0 then a1.pp_distance else 0 end) saveRemain
from (
select rank() over(partition by pp_code, pp_pcode, pp_round, pp_hole order by pp_shot) gbn, *
from player_play a
where a.pp_code = @gameCode
and a.pp_state = 9
) a
inner join player_play a1 on a.pp_code = a1.pp_code and a.pp_pcode = a1.pp_pcode and a.pp_round = a1.pp_round and a.pp_hole = a1.pp_hole and a.pp_shot = a1.pp_shot - 1
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
inner join cnps_hole_score c on a.pp_code = c.gameCode and convert(varchar,convert(int,a.pp_pcode)) = c.playerCode and a.pp_round = c.round and a.pp_hole = c.hole
where gbn = 1 -- 첫번째 벙커
group by a.pp_round
) a
UNION ALL
-- 벙커세이브율, 세이브수, 시도수, 세이브시 남은거리
select '개인' gbn, 0 AS round,
case when bnkCnt = 0 then 0
else round(convert(float,saveCnt) / bnkCnt * 100,2) end bnkSaveYul,
saveCnt, bnkCnt,
saveRemain,
case when saveCnt = 0 then 0
else round(convert(float,saveRemain) / saveCnt,2) end saveRemainAvg
from (
select count(*) bnkCnt,
sum(case when underPar <= 0 then 1 else 0 end) saveCnt,
sum(case when underPar <= 0 then a1.pp_distance else 0 end) saveRemain
from (
select rank() over(partition by pp_code, pp_pcode, pp_round, pp_hole order by pp_shot) gbn, *
from player_play a
where a.pp_code = @gameCode
and convert(varchar,convert(int,a.pp_pcode)) = @pcode
and a.pp_state = 9
) a
inner join player_play a1 on a.pp_code = a1.pp_code and a.pp_pcode = a1.pp_pcode and a.pp_round = a1.pp_round and a.pp_hole = a1.pp_hole and a.pp_shot = a1.pp_shot - 1
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
inner join cnps_hole_score c on a.pp_code = c.gameCode and convert(varchar,convert(int,a.pp_pcode)) = c.playerCode and a.pp_round = c.round and a.pp_hole = c.hole
where gbn = 1 -- 첫번째 벙커
) a
union all
select '전체' gbn, 0 AS round,
case when bnkCnt = 0 then 0
else round(convert(float,saveCnt) / bnkCnt * 100,2) end bnkSaveYul,
saveCnt, bnkCnt,
saveRemain,
case when saveCnt = 0 then 0
else round(convert(float,saveRemain) / saveCnt,2) end saveRemainAvg
from (
select count(*) bnkCnt,
sum(case when underPar <= 0 then 1 else 0 end) saveCnt,
sum(case when underPar <= 0 then a1.pp_distance else 0 end) saveRemain
from (
select rank() over(partition by pp_code, pp_pcode, pp_round, pp_hole order by pp_shot) gbn, *
from player_play a
where a.pp_code = @gameCode
and a.pp_state = 9
) a
inner join player_play a1 on a.pp_code = a1.pp_code and a.pp_pcode = a1.pp_pcode and a.pp_round = a1.pp_round and a.pp_hole = a1.pp_hole and a.pp_shot = a1.pp_shot - 1
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
inner join cnps_hole_score c on a.pp_code = c.gameCode and convert(varchar,convert(int,a.pp_pcode)) = c.playerCode and a.pp_round = c.round and a.pp_hole = c.hole
where gbn = 1 -- 첫번째 벙커
) a
) T1
JOIN
(
-- 리커버리율, 리커버리수, 적중실패수, 스크램블링 시 평균 남은거리
select '개인' gbn, round,
case when cnt = 0 then 0
else round(convert(float,recoverCnt) / cnt * 100, 2) end recoverYul,
recoverCnt, cnt AS recoverTotCnt,
case when disRemainCnt = 0 then '-'
else round(convert(float,disRemain) / disRemainCnt, 2) end disScramRemainAvg
from (
select distinct pp_round rnd from klpga.dbo.player_play where pp_code = @gameCode ) x
left outer join (
select count(*) cnt,
sum(case when a.underPar <= 0 then 1 else 0 end) recoverCnt,
sum(case when c.pp_distanceLen = 0 and a.underPar <= 0 then 0 else c.pp_distance end) disRemain,
sum(case when c.pp_distanceLen = 0 and a.underPar <= 0 then 0 else 1 end) disRemainCnt,
a.round
from cnps_hole_score a
inner join groupg b on a.gameCode = b.gp01 and a.playerCode = b.gp02 and a.round = b.gp03 and b.gp11 in ('1','3','9')
inner join player_play c on a.gameCode = c.pp_code and a.playerCode = convert(varchar,convert(int,c.pp_pcode)) and a.round = c.pp_round and a.hole = c.pp_hole and c.pp_shot = case when stdPar = 3 then 1 when stdPar = 4 then 2 when stdPar = 5 then 3 end
where a.gameCode = @gameCode
and a.playerCode = @pcode
and a.onGreen = 0
group by a.round
) a on a.round = x.rnd
union all
select '전체' gbn, round,
case when cnt = 0 then '-'
else round(convert(float,recoverCnt) / cnt * 100, 2) end recoverYul,
recoverCnt, cnt,
case when disRemainCnt = 0 then '-'
else round(convert(float,disRemain) / disRemainCnt, 2) end disRemainAvg
from (
select count(*) cnt,
sum(case when a.underPar <= 0 then 1 else 0 end) recoverCnt,
sum(case when c.pp_distanceLen = 0 and a.underPar <= 0 then 0 else c.pp_distance end) disRemain,
sum(case when c.pp_distanceLen = 0 and a.underPar <= 0 then 0 else 1 end) disRemainCnt,
a.round
from cnps_hole_score a
inner join groupg b on a.gameCode = b.gp01 and a.playerCode = b.gp02 and a.round = b.gp03 and b.gp11 in ('1','3','9')
inner join player_play c on a.gameCode = c.pp_code and a.playerCode = convert(varchar,convert(int,c.pp_pcode)) and a.round = c.pp_round and a.hole = c.pp_hole and c.pp_shot = case when stdPar = 3 then 1 when stdPar = 4 then 2 when stdPar = 5 then 3 end
where a.gameCode = @gameCode
and a.onGreen = 0
group by a.round
) a
UNION ALL
select '개인' gbn, 0 AS round,
case when cnt = 0 then 0
else round(convert(float,recoverCnt) / cnt * 100, 2) end recoverYul,
recoverCnt, cnt,
case when disRemainCnt = 0 then 0
else round(convert(float,disRemain) / disRemainCnt, 2) end disRemainAvg
from (
select count(*) cnt,
sum(case when a.underPar <= 0 then 1 else 0 end) recoverCnt,
sum(case when c.pp_distanceLen = 0 and a.underPar <= 0 then 0 else c.pp_distance end) disRemain,
sum(case when c.pp_distanceLen = 0 and a.underPar <= 0 then 0 else 1 end) disRemainCnt
from cnps_hole_score a
inner join groupg b on a.gameCode = b.gp01 and a.playerCode = b.gp02 and a.round = b.gp03 and b.gp11 in ('1','3','9')
inner join player_play c on a.gameCode = c.pp_code and a.playerCode = convert(varchar,convert(int,c.pp_pcode)) and a.round = c.pp_round and a.hole = c.pp_hole and c.pp_shot = case when stdPar = 3 then 1 when stdPar = 4 then 2 when stdPar = 5 then 3 end
where a.gameCode = @gameCode
and a.playerCode = @pcode
and a.onGreen = 0
) a
union all
select '전체' gbn, 0 AS round,
case when cnt = 0 then 0
else round(convert(float,recoverCnt) / cnt * 100, 2) end recoverYul,
recoverCnt, cnt,
case when disRemainCnt = 0 then 0
else round(convert(float,disRemain) / disRemainCnt, 2) end disRemainAvg
from (
select count(*) cnt,
sum(case when a.underPar <= 0 then 1 else 0 end) recoverCnt,
sum(case when c.pp_distanceLen = 0 and a.underPar <= 0 then 0 else c.pp_distance end) disRemain,
sum(case when c.pp_distanceLen = 0 and a.underPar <= 0 then 0 else 1 end) disRemainCnt
from cnps_hole_score a
inner join groupg b on a.gameCode = b.gp01 and a.playerCode = b.gp02 and a.round = b.gp03 and b.gp11 in ('1','3','9')
inner join player_play c on a.gameCode = c.pp_code and a.playerCode = convert(varchar,convert(int,c.pp_pcode)) and a.round = c.pp_round and a.hole = c.pp_hole and c.pp_shot = case when stdPar = 3 then 1 when stdPar = 4 then 2 when stdPar = 5 then 3 end
where a.gameCode = @gameCode
and a.onGreen = 0
) a
) T2 ON T1.gbn=T2.gbn AND T1.round = T2.round
JOIN
(
-- 60야드 미만 샷후 남은거리
select '개인' gbn, pp_round AS round,
disRemain,
cnt AS yds60cnt,
case when cnt = 0 then 0
else round(convert(float,disRemain) / cnt,2) end disRemainAvg
from (
select distinct pp_round rnd from klpga.dbo.player_play where pp_code = @gameCode ) x
left outer join (
select
count(*) cnt,
sum(a2.pp_distance) disRemain,
a.pp_round
from player_play a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
left outer join player_play a2 on a.pp_code = a2.pp_code and a.pp_pcode = a2.pp_pcode and a.pp_round = a2.pp_round and a.pp_hole = a2.pp_hole and a2.pp_shot = a.pp_shot+1
where a.pp_code = @gameCode
and convert(varchar,convert(int,a.pp_pcode)) = @pcode
--and case when @rnd = 0 then 0 else a.pp_round end = @rnd
and a.pp_distance < 60
and a.pp_distance <> 0
--and a.pp_state not in (3,10,12) 수정
and a.pp_state not in (3,10)
group by a.pp_round
) a on a.pp_round = x.rnd
union all
select '전체' gbn, pp_round,
disRemain,
cnt,
case when cnt = 0 then 0
else round(convert(float,disRemain) / cnt,2) end disRemainAvg
from (
select
count(*) cnt,
sum(a2.pp_distance) disRemain,
a.pp_round
from player_play a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
left outer join player_play a2 on a.pp_code = a2.pp_code and a.pp_pcode = a2.pp_pcode and a.pp_round = a2.pp_round and a.pp_hole = a2.pp_hole and a2.pp_shot = a.pp_shot+1
where a.pp_code = @gameCode
--and case when @rnd = 0 then 0 else a.pp_round end = @rnd
and a.pp_distance < 60
and a.pp_distance <> 0
--and a.pp_state not in (3,10,12) 수정
and a.pp_state not in (3,10)
group by a.pp_round
) a
UNION ALL
-- 60야드 미만 샷후 남은거리
select '개인' gbn, 0 AS round,
disRemain,
cnt AS yds60cnt,
case when cnt = 0 then 0
else round(convert(float,disRemain) / cnt,2) end disRemainAvg
from (
select
count(*) cnt,
sum(a2.pp_distance) disRemain
from player_play a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
left outer join player_play a2 on a.pp_code = a2.pp_code and a.pp_pcode = a2.pp_pcode and a.pp_round = a2.pp_round and a.pp_hole = a2.pp_hole and a2.pp_shot = a.pp_shot+1
where a.pp_code = @gameCode
and convert(varchar,convert(int,a.pp_pcode)) = @pcode
and a.pp_distance < 60
and a.pp_distance <> 0
--and a.pp_state not in (3,10,12) 수정
and a.pp_state not in (3,10)
) a
union all
select '전체' gbn, 0 AS round,
disRemain,
cnt AS yds60cnt,
case when cnt = 0 then 0
else round(convert(float,disRemain) / cnt,2) end disRemainAvg
from (
select
count(*) cnt,
sum(a2.pp_distance) disRemain
from player_play a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
left outer join player_play a2 on a.pp_code = a2.pp_code and a.pp_pcode = a2.pp_pcode and a.pp_round = a2.pp_round and a.pp_hole = a2.pp_hole and a2.pp_shot = a.pp_shot+1
where a.pp_code = @gameCode
and a.pp_distance < 60
and a.pp_distance <> 0
--and a.pp_state not in (3,10,12) 수정
and a.pp_state not in (3,10)
) a
) T3 ON T1.gbn = T3.gbn AND T1.round=T3.round
ORDER BY T1.round, T1.gbn
/////////////////////////////////////////////////////////////////////////////////////////////////////////////
## STATS - 퍼팅
## getPuttingList
declare @gameCode varchar(10)
declare @pcode varchar(8)
set @gameCode = #{gameCode}
set @pcode = #{playerCode}
SELECT
T1.gbn AS gbn -- 개인/전체구분
, T2.round AS round -- 라운드
, puttCnt, puttAvg, putt3Cnt, putt3Yul, girPuttCnt, girPuttAvg, totCnt, girCnt
, between_1_2_in_cnt, between_1_2_Yul,between_2_3_in_cnt, between_2_3_Yul, between_1_2_cnt, between_2_3_cnt -- 각기록
, disPuttAvg --퍼트평균거리
FROM
(
-- 퍼트수, 3퍼트율, 그린적중퍼트수
select '개인' gbn, round,
puttCnt,
case when totCnt = 0 then 0
else round(convert(float,puttCnt) / totCnt,2) end puttAvg,
putt3Cnt,
case when totCnt = 0 then 0
else round(convert(float,putt3Cnt) / totCnt * 100,2) end putt3Yul,
girPuttCnt,
case when girCnt = 0 then 0
else round(convert(float,girPuttCnt) / girCnt,2) end girPuttAvg,
totCnt,
girCnt
from (
select sum(puttCount) puttCnt,
count(*) totCnt,
sum(case when onGreen = 1 then puttCount else 0 end) girPuttCnt,
sum(case when onGreen = 1 then 1 else 0 end) girCnt,
sum(case when puttCount >= 3 then 1 else 0 end) putt3Cnt,
a.round
from cnps_hole_score a
inner join groupg b on a.gameCode = b.gp01 and a.playerCode = b.gp02 and a.round = b.gp03 and b.gp11 in ('1','3','9')
where a.gameCode = @gameCode
and a.playerCode = @pcode
group by a.round
) a
union all
select '전체' gbn, round,
puttCnt,
case when totCnt = 0 then 0
else round(convert(float,puttCnt) / totCnt,2) end puttAvg,
putt3Cnt,
case when totCnt = 0 then 0
else round(convert(float,putt3Cnt) / totCnt * 100,2) end putt3Yul,
girPuttCnt,
case when girCnt = 0 then 0
else round(convert(float,girPuttCnt) / girCnt,2) end girPuttAvg,
totCnt,
girCnt
from (
select sum(puttCount) puttCnt,
count(*) totCnt,
sum(case when onGreen = 1 then puttCount else 0 end) girPuttCnt,
sum(case when onGreen = 1 then 1 else 0 end) girCnt,
sum(case when puttCount >= 3 then 1 else 0 end) putt3Cnt,
a.round
from cnps_hole_score a
inner join groupg b on a.gameCode = b.gp01 and a.playerCode = b.gp02 and a.round = b.gp03 and b.gp11 in ('1','3','9')
where a.gameCode = @gameCode
group by a.round
) a
UNION ALL
-- 퍼트수, 3퍼트율, 그린적중퍼트수
select '개인' gbn, 0 AS round,
puttCnt,
case when totCnt = 0 then 0
else round(convert(float,puttCnt) / totCnt,2) end puttAvg,
putt3Cnt,
case when totCnt = 0 then 0
else round(convert(float,putt3Cnt) / totCnt * 100,2) end putt3Yul,
girPuttCnt,
case when girCnt = 0 then 0
else round(convert(float,girPuttCnt) / girCnt,2) end girPuttAvg,
totCnt,
girCnt
from (
select sum(puttCount) puttCnt,
count(*) totCnt,
sum(case when onGreen = 1 then puttCount else 0 end) girPuttCnt,
sum(case when onGreen = 1 then 1 else 0 end) girCnt,
sum(case when puttCount >= 3 then 1 else 0 end) putt3Cnt
from cnps_hole_score a
inner join groupg b on a.gameCode = b.gp01 and a.playerCode = b.gp02 and a.round = b.gp03 and b.gp11 in ('1','3','9')
where a.gameCode = @gameCode
and a.playerCode = @pcode
) a
union all
select '전체' gbn, 0 AS round,
puttCnt,
case when totCnt = 0 then 0
else round(convert(float,puttCnt) / totCnt,2) end puttAvg,
putt3Cnt,
case when totCnt = 0 then 0
else round(convert(float,putt3Cnt) / totCnt * 100,2) end putt3Yul,
girPuttCnt,
case when girCnt = 0 then 0
else round(convert(float,girPuttCnt) / girCnt,2) end girPuttAvg,
totCnt,
girCnt
from (
select sum(puttCount) puttCnt,
count(*) totCnt,
sum(case when onGreen = 1 then puttCount else 0 end) girPuttCnt,
sum(case when onGreen = 1 then 1 else 0 end) girCnt,
sum(case when puttCount >= 3 then 1 else 0 end) putt3Cnt
from cnps_hole_score a
inner join groupg b on a.gameCode = b.gp01 and a.playerCode = b.gp02 and a.round = b.gp03 and b.gp11 in ('1','3','9')
where a.gameCode = @gameCode
) a
) T1
JOIN
(
-- 1~2 퍼트성공율, 2~3퍼트성공율
select '개인' gbn, pp_round AS round,
between_1_2_in_cnt,
case when between_1_2_cnt = 0 then 0
else round(convert(float,between_1_2_in_cnt) / between_1_2_cnt * 100, 2) end between_1_2_Yul,
between_2_3_in_cnt,
case when between_2_3_cnt = 0 then 0
else round(convert(float,between_2_3_in_cnt) / between_2_3_cnt * 100, 2) end between_2_3_Yul,
between_1_2_cnt,
between_2_3_cnt
from (
select
sum(case when a.pp_distance between 1 and 2 and a2.pp_state = 10 then 1 else 0 end) between_1_2_in_cnt,
sum(case when a.pp_distance between 1 and 2 then 1 else 0 end) between_1_2_cnt,
sum(case when a.pp_distance between 2 and 3 and a2.pp_state = 10 then 1 else 0 end) between_2_3_in_cnt,
sum(case when a.pp_distance between 2 and 3 then 1 else 0 end) between_2_3_cnt,
a.pp_round
From player_play a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
left outer join player_play a2 on a.pp_code = a2.pp_code and a.pp_pcode = a2.pp_pcode and a.pp_round = a2.pp_round and a.pp_hole = a2.pp_hole and a2.pp_shot = a.pp_shot+1
where a.pp_code = @gameCode
and convert(varchar,convert(int,a.pp_pcode)) = @pcode
and a.pp_distance between 1 and 3
group by a.pp_round
) a
union all
select '전체' gbn, pp_round,
between_1_2_in_cnt,
case when between_1_2_cnt = 0 then 0
else round(convert(float,between_1_2_in_cnt) / between_1_2_cnt * 100, 2) end between_1_2_Yul,
between_2_3_in_cnt,
case when between_2_3_cnt = 0 then 0
else round(convert(float,between_2_3_in_cnt) / between_2_3_cnt * 100, 2) end between_2_3_Yul,
between_1_2_cnt,
between_2_3_cnt
from (
select
sum(case when a.pp_distance between 1 and 2 and a2.pp_state = 10 then 1 else 0 end) between_1_2_in_cnt,
sum(case when a.pp_distance between 1 and 2 then 1 else 0 end) between_1_2_cnt,
sum(case when a.pp_distance between 2 and 3 and a2.pp_state = 10 then 1 else 0 end) between_2_3_in_cnt,
sum(case when a.pp_distance between 2 and 3 then 1 else 0 end) between_2_3_cnt,
a.pp_round
From player_play a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
left outer join player_play a2 on a.pp_code = a2.pp_code and a.pp_pcode = a2.pp_pcode and a.pp_round = a2.pp_round and a.pp_hole = a2.pp_hole and a2.pp_shot = a.pp_shot+1
where a.pp_code = @gameCode
and a.pp_distance between 1 and 3
group by a.pp_round
) a
UNION ALL
select '개인' gbn, 0 AS round,
between_1_2_in_cnt,
case when between_1_2_cnt = 0 then 0
else round(convert(float,between_1_2_in_cnt) / between_1_2_cnt * 100, 2) end between_1_2_Yul,
between_2_3_in_cnt,
case when between_2_3_cnt = 0 then 0
else round(convert(float,between_2_3_in_cnt) / between_2_3_cnt * 100, 2) end between_2_3_Yul,
between_1_2_cnt,
between_2_3_cnt
from (
select
sum(case when a.pp_distance between 1 and 2 and a2.pp_state = 10 then 1 else 0 end) between_1_2_in_cnt,
sum(case when a.pp_distance between 1 and 2 then 1 else 0 end) between_1_2_cnt,
sum(case when a.pp_distance between 2 and 3 and a2.pp_state = 10 then 1 else 0 end) between_2_3_in_cnt,
sum(case when a.pp_distance between 2 and 3 then 1 else 0 end) between_2_3_cnt
From player_play a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
left outer join player_play a2 on a.pp_code = a2.pp_code and a.pp_pcode = a2.pp_pcode and a.pp_round = a2.pp_round and a.pp_hole = a2.pp_hole and a2.pp_shot = a.pp_shot+1
where a.pp_code = @gameCode
and convert(varchar,convert(int,a.pp_pcode)) = @pcode
and a.pp_distance between 1 and 3
) a
union all
select '전체' gbn, 0 AS round,
between_1_2_in_cnt,
case when between_1_2_cnt = 0 then 0
else round(convert(float,between_1_2_in_cnt) / between_1_2_cnt * 100, 2) end between_1_2_Yul,
between_2_3_in_cnt,
case when between_2_3_cnt = 0 then 0
else round(convert(float,between_2_3_in_cnt) / between_2_3_cnt * 100, 2) end between_2_3_Yul,
between_1_2_cnt,
between_2_3_cnt
from (
select
sum(case when a.pp_distance between 1 and 2 and a2.pp_state = 10 then 1 else 0 end) between_1_2_in_cnt,
sum(case when a.pp_distance between 1 and 2 then 1 else 0 end) between_1_2_cnt,
sum(case when a.pp_distance between 2 and 3 and a2.pp_state = 10 then 1 else 0 end) between_2_3_in_cnt,
sum(case when a.pp_distance between 2 and 3 then 1 else 0 end) between_2_3_cnt
From player_play a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
left outer join player_play a2 on a.pp_code = a2.pp_code and a.pp_pcode = a2.pp_pcode and a.pp_round = a2.pp_round and a.pp_hole = a2.pp_hole and a2.pp_shot = a.pp_shot+1
where a.pp_code = @gameCode
and a.pp_distance between 1 and 3
) a
) T2 ON T1.gbn=T2.gbn AND T1.round=T2.round
JOIN (
select '개인' gbn, pp_round AS round,
round(pp_distanceLen / tryCnt,2) disPuttAvg
from (
select
sum(a.pp_distanceLen) pp_distanceLen,
count(*) tryCnt,
a.pp_round
From player_play a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
left outer join player_play a2 on a.pp_code = a2.pp_code and a.pp_pcode = a2.pp_pcode and a.pp_round = a2.pp_round and a.pp_hole = a2.pp_hole and a2.pp_shot = a.pp_shot-1
where a.pp_code = @gameCode
and convert(varchar,convert(int,a.pp_pcode)) = @pcode
and a.pp_state = 10
and a2.pp_state = 3
group by a.pp_round
) a
union all
select '전체' gbn, pp_round,
round(pp_distanceLen / tryCnt,2) disPuttAvg
from (
select
sum(a.pp_distanceLen) pp_distanceLen,
count(*) tryCnt,
a.pp_round
From player_play a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
left outer join player_play a2 on a.pp_code = a2.pp_code and a.pp_pcode = a2.pp_pcode and a.pp_round = a2.pp_round and a.pp_hole = a2.pp_hole and a2.pp_shot = a.pp_shot-1
where a.pp_code = @gameCode
and a.pp_state = 10
and a2.pp_state = 3
group by a.pp_round
) a
UNION ALL
-- 평균 퍼팅거리
select '개인' gbn, 0 AS round,
round(pp_distanceLen / tryCnt,2) disAvg
from (
select
sum(a.pp_distanceLen) pp_distanceLen,
count(*) tryCnt
From player_play a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
left outer join player_play a2 on a.pp_code = a2.pp_code and a.pp_pcode = a2.pp_pcode and a.pp_round = a2.pp_round and a.pp_hole = a2.pp_hole and a2.pp_shot = a.pp_shot-1
where a.pp_code = @gameCode
and convert(varchar,convert(int,a.pp_pcode)) = @pcode
and a.pp_state = 10
and a2.pp_state = 3
) a
union all
select '전체' gbn, 0 AS round,
round(pp_distanceLen / tryCnt,2) disAvg
from (
select
sum(a.pp_distanceLen) pp_distanceLen,
count(*) tryCnt
From player_play a
inner join groupg b on a.pp_code = b.gp01 and convert(varchar,convert(int,a.pp_pcode)) = b.gp02 and a.pp_round = b.gp03 and b.gp11 in ('1','3','9')
left outer join player_play a2 on a.pp_code = a2.pp_code and a.pp_pcode = a2.pp_pcode and a.pp_round = a2.pp_round and a.pp_hole = a2.pp_hole and a2.pp_shot = a.pp_shot-1
where a.pp_code = @gameCode
and a.pp_state = 10
and a2.pp_state = 3
) a
) T3 ON T1.gbn=T3.gbn AND T1.round = T3.round
ORDER BY round, gbn
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
## 미디어 동영상조회
## getVideosList
SELECT T1.*
FROM (
SELECT T0.*
, FLOOR(((ROW_NUMBER() OVER (
ORDER BY SN DESC
)) - 1) / #{listSize} + 1) AS PAGE_NO
FROM (
SELECT
sn
, kind -- 게시물종류
, CONVERT(VARCHAR, REGDATE, 23) AS regDate -- 등록일
, title -- 제목
, isnull(title_eng,'') as engTitle -- 제목영문명
, pdesc as contents -- 내용
, cnt -- 조회수
, RTRIM(filename2) AS videosCode -- videosCode
, RTRIM(filename3) AS videosCode2 -- videosCode2
, isNull(filename1,'') AS thumbnail -- 썸네일 https://klpga.co.kr/DATA/${v.thumbnail}
, gameCode -- 대회코드
FROM REKLPGT.dbo.WFBOARD a
left outer join game_list b on a.gameCode = b.gl01
left outer join RecordDate c on b.gl02 between c.ra02 and c.ra03 and b.gl17 = c.ra06
WHERE
KIND = '26'
and gameCode = #{gameCode}
AND exists (
select sn
from REKLPGT.dbo.refMediaPlayer rmp
where rmp.sn = a.sn
and rmp.kind = a.kind
and rmp.playerCode = #{playerCode}
)
) T0
)T1
WHERE PAGE_NO = #{pageNum}
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
## 미디어 사진 조회
## getPhotoList
SELECT T1.*
FROM (
SELECT T0.*
, FLOOR(((ROW_NUMBER() OVER (
ORDER BY SN DESC
)) - 1) / #{listSize} + 1) AS PAGE_NO
FROM (
SELECT
sn -- seq
, kind -- 게시물종류
, CONVERT(VARCHAR, REGDATE, 23) AS regDate -- 등록일
, title -- 제목
, pdesc as contents -- 내용
, cnt -- 조회수
, fileName -- 파일명
, isNull(thumbnail,FILENAME) AS thumbnail -- 썸네일
FROM
REKLPGT.dbo.WFBOARD_PHOTO a
join game_list b on a.kind = b.gl01
inner join RecordDate c on b.gl02 between c.ra02 and c.ra03 and b.gl17 = c.ra06
WHERE
1=1
and KIND = #{gameCode}
AND EXISTS (
SELECT sn
FROM REKLPGT.dbo.refMediaPlayer rmp
WHERE rmp.sn = a.sn
AND rmp.kind = '1'
AND rmp.playerCode = #{playerCode}
)
) T0
)T1
WHERE PAGE_NO = #{pageNum}