NTPQaNWL

1use PolyBridge
2go
3
4create or alter procedure LoadCurrentScoresToHistory 
5(
6	@ScoreDate date
7)
8as
9set nocount on
10--exec LoadCurrentScoresToHistory
11--declare @ScoreDate date = '2021-01-05'
12
13/*
14select * from HighRankHistory
15select * from stgCurrentScores	
16*/
17
18/* Add All New records not in score history */
19	insert into HighRankHistory (ScoreID, Score, ScorePlayer, ScoreDate)
20	select i.ScoreID, i.Score, i.ScorePlayer, @ScoreDate as ScoreDate
21	from stgCurrentScores i
22	where not exists (select 1 from HighRankHistory h where i.ScoreID = h.ScoreID) --ScoreID does not exist in table currently
23
24	print 'Added ' + cast(@@ROWCOUNT as varchar(9)) + ' records to HighRankHistory.'
25
26/* Cheat Detection / Removal */
27	
28	/*
29		When removing cheated scores we could treat earlier scores by the player in two ways. this uses method 1
30			1) Remove cheated all scores of that player
31				- This may be a bit much as it will nuke a players history if they accidently submit a bad score and have it removed
32			2) Remove only the new cheated score.
33				- This may cause issues if a cheated score is improved by the same player with a better cheated score and only the new one is removed.
34
35	Note: This may identify scores as being cheated if the number of scores tracked is reduced after logging has started.
36	*/
37	-- Scores in History not in current scores.
38	declare @maxScore int = (select max(score) from stgCurrentScores)
39	;with CheatedScores as (
40		select Score, ScorePlayer from HighRankHistory h
41		where h.score <= @maxScore --The score should be in stgCurrentScores if they improved, if it isn't on the list it is cheated.
42		except 
43		select Score, ScorePlayer from stgCurrentScores
44		)
45	delete h
46	from HighRankHistory h
47		inner join CheatedScores c on 1=1 -- c.Score = h.Score  --Removed so remove all scores for that player
48			and c.ScorePlayer = h.ScorePlayer
49
50	print 'Deleted ' + cast(@@ROWCOUNT as varchar(9)) + ' cheated records from HighRankHistory.'