· 9 years ago · Dec 21, 2016, 07:10 AM
1USE master
2GO
3
4Drop database dgaranin;
5GO
6
7Create DATABasE dgaranin;
8GO
9
10USE dgaranin
11GO
12
13-- ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
14
15CREATE TABLE Player
16(
17 PlayerId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
18 FirstName nvarchar(50) NOT NULL,
19 Surname nvarchar(50) NOT NULL,
20 Addr nvarchar(50) NOT NULL,
21 PhoneNumber nvarchar(50) NOT NULL
22);
23GO
24
25CREATE TABLE Team
26(
27 TeamId int NOT NULL PRIMARY KEY,
28 TeamName nvarchar(50) NOT NULL,
29 CapitanId int NOT NULL,
30 SecondPlayerId int NOT NULL,
31 ThridPlayerId int NOT NULL,
32 FourthPlayerId int NOT NULL,
33 FOREIGN KEY (CapitanId) REFERENCES Player(PlayerId),
34 FOREIGN KEY (SecondPlayerId) REFERENCES Player(PlayerId),
35 FOREIGN KEY (ThridPlayerId) REFERENCES Player(PlayerId),
36 FOREIGN KEY (FourthPlayerId) REFERENCES Player(PlayerId)
37);
38GO
39
40CREATE TABLE Game
41(
42 GameId int NOT NULL PRIMARY KEY,
43 PlayerId int NOT NULL,
44 Score int NOT NULL,
45 FOREIGN KEY (PlayerId) REFERENCES Player(PlayerId)
46);
47GO
48
49CREATE TABLE Playgrounds
50(
51 PlaceId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
52 Name nvarchar(50) NOT NULL
53);
54GO
55
56CREATE TABLE Contest
57(
58 ContestId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
59 CapitanGame int,
60 SecondPlayerGame int,
61 ThridPlayerGame int,
62 FourthPlayerGame int,
63 IsAbsence bit NOT NULL,
64 GeneralScore int NOT NULL,
65 FOREIGN KEY (CapitanGame) REFERENCES Game(GameId),
66 FOREIGN KEY (SecondPlayerGame) REFERENCES Game(GameId),
67 FOREIGN KEY (ThridPlayerGame) REFERENCES Game(GameId),
68 FOREIGN KEY (FourthPlayerGame) REFERENCES Game(GameId)
69);
70GO
71
72CREATE TABLE WeeklyContest
73(
74 WeContestId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
75 ContestId1 int NOT NULL,
76 ContestId2 int NOT NULL,
77 PlaceId int NOT NULL,
78 DateContest DATE NOT NUll,
79 FOREIGN KEY (ContestId1) REFERENCES Contest(ContestId),
80 FOREIGN KEY (ContestId2) REFERENCES Contest(ContestId),
81 FOREIGN KEY (PlaceId) REFERENCES Playgrounds(PlaceId)
82);
83GO
84
85
86
87
88
89CREATE TRIGGER countScore_check
90ON Game
91INSTEAD OF INSERT
92AS
93BEGIN
94 INSERT INTO Game
95 SELECT
96 GameId, PlayerId, Score
97 FROM
98 inserted
99 WHERE
100 EXISTS
101 (
102 SELECT
103 GameId, PlayerId, Score
104 WHERE
105 Score <= 300
106 )
107END;
108GO
109
110
111
112
113CREATE FUNCTION dbo.GetHandicap(@eff float)
114 RETURNS INT
115AS
116 BEGIN
117 set @eff = (200 - @eff) * 0.75
118 if (@eff <= 0)
119 return 0
120 return @eff
121 END
122GO
123
124
125CREATE FUNCTION dbo.GetSum(@id int, @b int)
126 RETURNS INT
127AS
128 BEGIN
129 DECLARE @tmp int
130 select @tmp = Score FROM Game where GameId = @id
131 if @tmp is NULL
132 return @b
133
134 Set @tmp = @tmp + dbo.GetHandicap(@tmp)
135 return @tmp + @b
136 END
137GO
138
139
140
141CREATE TRIGGER addRecordContest_check
142ON Contest
143INSTEAD OF INSERT
144AS
145BEGIN
146 Declare @CapitanGame int
147 Declare @SecondPlayerGame int
148 Declare @ThridPlayerGame int
149 Declare @FourthPlayerGame int
150 Declare @IsAbsence bit
151 Declare @GeneralScore int
152
153 DECLARE cc CURSOR FOR
154 SELECT
155 CapitanGame, SecondPlayerGame, ThridPlayerGame, FourthPlayerGame
156 FROM
157 inserted
158
159 OPEN cc
160 FETCH NEXT FROM cc INTO
161 @CapitanGame, @SecondPlayerGame, @ThridPlayerGame, @FourthPlayerGame
162
163 WHILE @@fetch_status = 0
164 begin
165 SET @IsAbsence = 0
166 SET @GeneralScore = 0
167
168 if ((@CapitanGame IS NULL and @SecondPlayerGame IS NULL) OR
169 (@CapitanGame IS NULL and @ThridPlayerGame IS NULL) OR
170 (@CapitanGame IS NULL and @FourthPlayerGame IS NULL) OR
171 (@SecondPlayerGame IS NULL and @ThridPlayerGame IS NULL) OR
172 (@SecondPlayerGame IS NULL and @FourthPlayerGame IS NULL) OR
173 (@ThridPlayerGame IS NULL and @FourthPlayerGame IS NULL) )
174 SET @IsAbsence = 1
175
176 if (@IsAbsence = 0)
177 BEGIN
178 SET @GeneralScore = dbo.GetSum(@CapitanGame, @GeneralScore)
179 SET @GeneralScore = dbo.GetSum(@SecondPlayerGame, @GeneralScore)
180 SET @GeneralScore = dbo.GetSum(@ThridPlayerGame, @GeneralScore)
181 SET @GeneralScore = dbo.GetSum(@FourthPlayerGame, @GeneralScore)
182 END
183
184 INSERT INTO Contest (CapitanGame, SecondPlayerGame, ThridPlayerGame, FourthPlayerGame, IsAbsence, GeneralScore)
185 values (@CapitanGame, @SecondPlayerGame, @ThridPlayerGame, @FourthPlayerGame, @IsAbsence, @GeneralScore)
186
187 FETCH NEXT FROM cc INTO
188 @CapitanGame, @SecondPlayerGame, @ThridPlayerGame, @FourthPlayerGame
189 END
190 close cc
191 DEALLOCATE cc
192END;
193GO
194
195
196
197INSERT INTO Player(FirstName, Surname, Addr, PhoneNumber) VALUES
198 (N'Григорий', N'Смирнов', N'Бажова', N'25-05-51')
199 , (N'Ðртем', N'ВаÑильев', N'БелинÑкого', N'73-65-70')
200 , (N'БориÑ', N'Соколов', N'БелинÑкого', N'38-39-40')
201 , (N'Валерий', N'Петров', N'БороваÑ', N'44-67-97')
202 , (N'Виктор', N'Федоров', N'БратÑкаÑ', N'44-97-97')
203 , (N'ОÑип', N'Морозов', N'Бажова', N'73-41-73')
204 , (N'Петр', N'Волков', N'Ватутина', N'44-19-94')
205 , (N'Ðдуард', N'Лебедев', N'Бажова', N'60-30-39')
206 , (N'Яков', N'Егоров', N'Вайнера', N'64-58-40')
207 , (N'Степан', N'Степанов', N'Викулова', N'36-43-31')
208 , (N'МакÑим', N'Орлов', N'Вилонова', N'71-41-30')
209 , (N'ÐÑ€Ñений', N'Виноградов', N'Калинина', N'78-47-35')
210
211
212INSERT INTO Team(TeamId, TeamName, CapitanId, SecondPlayerId, ThridPlayerId, FourthPlayerId) VALUES
213 (1, N'Викинги', 1, 2, 3, 4)
214 , (2, N'Ðмбробене', 5, 6, 7 , 8)
215 , (3, N'Байкал', 9, 10, 11, 12)
216
217
218INSERT INTO Playgrounds(Name) VALUES
219 (N'Ðшан')
220 , (N'КрепоÑÑ‚ÑŒ')
221 , (N'Паркет')
222 , (N'ПлоÑкоÑÑ‚ÑŒ')
223 , (N'У петрова')
224 , (N'Под козырьком')
225 , (N'Ð’ Ñливках')
226 , (N'БильÑрд')
227
228
229INSERT INTO Game(GameId, PlayerId, Score) VALUES
230 (1, 1, 250)
231 , (2, 2, 180)
232 , (3, 3, 234)
233 , (4, 4, 122)
234 , (5, 5, 236)
235 , (6, 6, 265)
236 , (7, 7, 213)
237 , (8, 8, 276)
238 , (9, 9, 298)
239 , (10, 10, 254)
240 , (11, 11, 198)
241 , (12, 12, 287)
242 , (13, 2, 284)
243 , (14, 3, 239)
244 , (15, 4, 185)
245 , (16, 5, 212)
246 , (17, 6, 269)
247 , (18, 7, 236)
248 , (19, 8, 279)
249 , (20, 9, 285)
250 , (21, 10, 294)
251 , (22, 11, 148)
252 , (23, 12, 297)
253 , (24, 1, 267)
254 , (25, 1, 295)
255 , (26, 2, 274)
256 , (27, 3, 269)
257 , (28, 4, 187)
258 , (29, 5, 264)
259 , (30, 6, 274)
260 , (31, 7, 239)
261 , (32, 8, 285)
262 , (33, 9, 267)
263 , (34, 10, 238)
264 , (35, 11, 176)
265 , (36, 12, 281)
266
267
268INSERT INTO Contest(CapitanGame, SecondPlayerGame, ThridPlayerGame, FourthPlayerGame) VALUES
269 (1, 2, 3, 4)
270 , (5, 6, 7 , 8)
271 , (9, 10, 11, 12)
272
273 , (24, 13, 14, 15)
274 , (16, 17, 18, 19)
275 , (20, 21, 22, 23)
276
277 , (29, 30, 31, NULL)
278 , (33, 34, NULL, NULL)
279
280
281INSERT INTO WeeklyContest(ContestId1, ContestId2, PlaceId, DateContest) VALUES
282 (1, 2, 1, '2016-12-19')
283 , (3, 4, 2, '2016-12-20')
284 , (5, 6, 3, '2016-12-21')
285 , (7, 8, 3, '2016-12-22')
286
287
288-- ÑпиÑок команд в указанный интервал времени
289-- ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
290drop PROCEDURE ShowListTeam
291go
292
293CREATE PROCEDURE ShowListTeam
294(
295 @start DATE,
296 @end DATE
297)
298as
299 begin
300 SELECT DISTINCT
301 Team.TeamName as N'Ðазвание команды',
302 p1.Surname as N'Капитан',
303 p2.Surname as N'Второй',
304 p4.Surname as N'Третий',
305 p3.Surname as N'Четвертый'
306 from
307 WeeklyContest
308 JOIN Contest on Contest.ContestId = WeeklyContest.ContestId1 or Contest.ContestId = WeeklyContest.ContestId2
309 JOIN Game on Contest.CapitanGame = Game.GameId
310 JOIN Team on Team.CapitanId = Game.PlayerId
311 JOIN Player p1 on Team.CapitanId = p1.PlayerId
312 JOIN Player p2 on Team.SecondPlayerId = p2.PlayerId
313 JOIN Player p3 on Team.ThridPlayerId = p3.PlayerId
314 JOIN Player p4 on Team.FourthPlayerId = p4.PlayerId
315 Where DateContest >= @start and DateContest <= @end
316 end
317GO
318
319
320EXECUTE ShowListTeam '2016-12-19', '2016-12-24'
321GO
322
323
324
325-- ÑпиÑок площадок
326-- ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
327
328CREATE PROCEDURE ShowListPlaygrounds
329as
330 begin
331 SELECT
332 Playgrounds.Name as N'Ðазвание площадки',
333 count(*) as N'КоличеÑтво'
334 from
335 WeeklyContest
336 Join Playgrounds on WeeklyContest.PlaceId = Playgrounds.PlaceId
337 group by Playgrounds.Name
338 end
339GO
340
341EXECUTE ShowListPlaygrounds
342GO
343
344
345
346-- ÑпиÑок игроков в порÑдке рейтинга
347-- ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
348
349drop PROCEDURE ShowListPlayersRating
350go
351
352
353CREATE PROCEDURE ShowListPlayersRating
354(
355 @month int,
356 @isMonth bit,
357 @isHandcap bit
358)
359as
360 begin
361 DECLARE @dateStart Date = DATEFROMPARTS(2016, @month, 1)
362 DECLARE @dateFinish Date = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dateStart)+1,0))
363
364 DECLARE @score TABLE
365 (
366 PlayerId int NOT NULL,
367 Score int NOT NULL,
368 DateContest Date NOT NULL
369 )
370
371 INSERT INTO @score (PlayerId, Score, DateContest)
372 SELECT
373 PlayerId, Score, DateContest
374 from
375 WeeklyContest
376 JOIN Contest on Contest.ContestId = WeeklyContest.ContestId1 or Contest.ContestId = WeeklyContest.ContestId2
377 JOIN Game on Contest.CapitanGame = Game.GameId
378 or Contest.SecondPlayerGame = Game.PlayerId
379 or Contest.ThridPlayerGame = Game.PlayerId
380 or Contest.FourthPlayerGame = Game.PlayerId
381 Where DateContest >= @dateStart and DateContest <= @dateFinish
382
383 DECLARE @scoreTrue TABLE
384 (
385 PlayerId int NOT NULL,
386 Score int NOT NULL,
387 handicap int NOT NULL,
388 curWeek int NOT NULL
389 )
390
391 declare @curWeek int = 1
392 while @curWeek <= DATEPART(WEEK, @dateFinish) - DATEPART(WEEK, DATEADD(MM, DATEDIFF(MM,0,@dateFinish), 0))+ 1
393 begin
394
395 INSERT INTO @scoreTrue (PlayerId, Score, handicap, curWeek)
396 SELECT
397 PlayerId,
398 avg(Score) as avg_score,
399 dbo.GetHandicap(avg(Score)) as handicap,
400 @curWeek
401 from
402 @score
403 where @curWeek = DATEPART(WEEK, DateContest) - DATEPART(WEEK, DATEADD(MM, DATEDIFF(MM,0,DateContest), 0))+1
404 group by PlayerId
405
406 set @curWeek = @curWeek + 1
407 end
408
409 if (@isMonth = 1)
410 BEGIN
411 SELECT
412 Player.Surname,
413 case
414 when @isHandcap = 1
415 then sum(Score + handicap)
416 else sum(Score)
417 end as avg_sum
418 FROM
419 @scoreTrue as t
420 join Player on Player.PlayerId = t.PlayerId
421 group by Player.Surname
422 order by avg_sum DESC
423 END
424 else
425 begin
426 SELECT
427 Player.Surname,
428 case
429 when @isHandcap = 1
430 then sum(Score + handicap)
431 else sum(Score)
432 end as avg_sum,
433 curWeek
434 FROM
435 @scoreTrue as t
436 join Player on Player.PlayerId = t.PlayerId
437 group by curWeek, Player.Surname
438 order by avg_sum DESC
439 end
440 end
441GO
442
443
444-- @month int, @isMonth bit, @isHandcap bit
445EXECUTE ShowListPlayersRating 12, 1, 0
446GO
447
448
449
450
451
452-- рейтинг команд в указанный меÑÑц
453-- ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
454
455CREATE PROCEDURE RatingMonth
456(
457 @month int
458)
459as
460 begin
461 DECLARE @dateStart Date = DATEFROMPARTS(2016, @month, 1)
462 DECLARE @dateFinish Date = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dateStart)+1,0))
463
464 declare @curWeek int = 4
465
466 DECLARE @score TABLE
467 (
468 TeamId1 int NOT NULL,
469 TeamId2 int NOT NULL,
470 Win1 int NOT NULL,
471 Win2 int NOT NULL,
472 Score1 int NOT NULL,
473 Score2 int NOT NULL,
474 WeekNumber int NOT NULL
475 )
476
477 while @curWeek <= DATEPART(WEEK, @dateFinish) - DATEPART(WEEK, DATEADD(MM, DATEDIFF(MM,0,@dateFinish), 0))+ 1
478 begin
479 INSERT INTO @score (TeamId1, TeamId2, Win1, Win2, Score1, Score2, WeekNumber)
480 SELECT
481 g1.PlayerId as TeamId1,
482 g2.PlayerId as TeamId2,
483 case when con1.GeneralScore >= con2.GeneralScore and con1.IsAbsence <> 1
484 then 1 else 0 end as win1,
485 case when con2.GeneralScore >= con1.GeneralScore and con2.IsAbsence <> 1
486 then 1 else 0 end as win2,
487 con1.GeneralScore,
488 con2.GeneralScore,
489 @curWeek as WeekNumber
490 from
491 WeeklyContest
492 JOIN Contest con1 on WeeklyContest.ContestId1 = con1.ContestId
493 JOIN Contest con2 on WeeklyContest.ContestId2 = con2.ContestId
494 JOIN Game g1 on con1.CapitanGame = g1.GameId
495 JOIN Game g2 on con2.CapitanGame = g2.GameId
496 where @curWeek = DATEPART(WEEK, DateContest) - DATEPART(WEEK, DATEADD(MM, DATEDIFF(MM,0,DateContest), 0))+1
497
498 set @curWeek = @curWeek + 1
499 END
500
501
502 DECLARE @result TABLE
503 (
504 TeamId1 int NOT NULL,
505 TeamId2 int NOT NULL,
506 CountWin1 int NOT NULL,
507 CountWin2 int NOT NULL,
508 WeekWin1 bit NOT NULL,
509 WeekWin2 bit NOT NULL,
510 Score1 int NOT NULL,
511 Score2 int NOT NULL,
512 WeekNumber int NOT NULL
513 )
514
515 DECLARE cc CURSOR FOR
516 SELECT DISTINCT
517 TeamId1, TeamId2, WeekNumber
518 FROM
519 @score
520
521 DECLARE @TeamId1 int
522 DECLARE @TeamId2 int
523 DECLARE @WeekNumber int
524
525 open cc
526 FETCH NEXT FROM cc INTO
527 @TeamId1, @TeamId2, @WeekNumber
528
529 WHILE @@fetch_status = 0
530 begin
531
532 INSERT INTO @result (TeamId1, TeamId2, CountWin1, CountWin2, WeekWin1, WeekWin2, Score1, Score2, WeekNumber)
533 SELECT
534 @TeamId1,
535 @TeamId2,
536 sum(Win1),
537 sum(Win2),
538 case when sum(Score1) >= sum(Score2) then 1 else 0 end,
539 case when sum(Score2) >= sum(Score1) then 1 else 0 end,
540 sum(Score1),
541 sum(Score2),
542 @WeekNumber
543 from
544 @score
545 where TeamId1 = @TeamId1 and TeamId2 = @TeamId2 and WeekNumber = @WeekNumber
546
547 FETCH NEXT FROM cc INTO
548 @TeamId1, @TeamId2, @WeekNumber
549 end
550 close cc
551 DEALLOCATE cc
552
553 SELECT
554 Team.TeamName as TeamName,
555 sum(wins + weekWin) as winsAll,
556 sum(score) as scoreAll
557 FROM
558 (
559 SELECT
560 TeamId1 as id,
561 CountWin1 as wins,
562 WeekWin1 as weekWin,
563 Score1 as score
564 FROM @result
565
566 UNION
567
568 SELECT
569 TeamId2 as id,
570 CountWin2 as wins,
571 WeekWin2 as weekWin,
572 Score2 as score
573 FROM @result
574 ) t1
575 JOIN Player on Player.PlayerId = id
576 JOIN Team on Player.PlayerId = Team.CapitanId
577 group by Team.TeamName
578 order by winsAll desc
579 end
580GO
581
582
583EXEC RatingMonth 12
584GO
585
586
587
588-- ÑпиÑок команд Ñ Ð½ÐµÑвкой
589-- ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
590
591CREATE PROCEDURE ShowListTeamAbsence
592(
593 @month int
594)
595as
596 begin
597 DECLARE @dateStart Date = DATEFROMPARTS(2016, @month, 1)
598 DECLARE @dateFinish Date = DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,@dateStart)+1,0))
599
600 SELECT
601 Team.TeamName,
602 Contest.CapitanGame,
603 Contest.SecondPlayerGame,
604 Contest.ThridPlayerGame,
605 Contest.FourthPlayerGame
606 from WeeklyContest
607 JOIN Contest on WeeklyContest.ContestId1 = Contest.ContestId
608 or WeeklyContest.ContestId2 = Contest.ContestId
609 JOIN Game on Contest.CapitanGame = Game.GameId
610 JOIN Team on Team.CapitanId = Game.PlayerId
611 WHERE Contest.IsAbsence = 1 and DateContest >= @dateStart and DateContest <= @dateFinish
612 end
613GO
614
615exec ShowListTeamAbsence 12
616Go