g9pRBzLF

1-- использую свою БД
2USE master
3GO
4
5Drop database dgaranin;
6GO
7
8Create DATABasE dgaranin;
9GO
10
11USE dgaranin
12GO
13
14-- ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
15
16CREATE TABLE Participant
17(
18  PartId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
19  FirstName nvarchar(50) NOT NULL,
20  Surname nvarchar(50) NOT NULL,
21);
22GO
23
24CREATE TABLE AddressBook
25(
26  AddressId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
27  Address nvarchar(50) NOT NULL,
28  PhoneNumber nvarchar(50) NOT NULL,
29  PartId int NOT NULL,
30  FOREIGN KEY (PartId) REFERENCES Participant(PartId)
31);
32GO
33
34CREATE TABLE Team
35(
36  TeamId int NOT NULL PRIMARY KEY,
37  TeamName nvarchar(50) NOT NULL,
38  CapitanId int NOT NULL,
39  SecondPlayerId int NOT NULL,
40  ThridPlayerId int NOT NULL,
41  FourthPlayerId int NOT NULL,
42  FOREIGN KEY (CapitanId) REFERENCES Participant(PartId),
43  FOREIGN KEY (SecondPlayerId) REFERENCES Participant(PartId),
44  FOREIGN KEY (ThridPlayerId) REFERENCES Participant(PartId),
45  FOREIGN KEY (FourthPlayerId) REFERENCES Participant(PartId)
46);
47GO
48
49CREATE TABLE Game
50(
51  GameId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
52  PlayerId int NOT NULL,
53  Score int NOT NULL,
54  FOREIGN KEY (PlayerId) REFERENCES Participant(PartId)
55);
56GO
57
58CREATE TABLE Playgrounds
59(
60  PlaceId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
61  Name nvarchar(50) NOT NULL
62);
63GO
64
65CREATE TABLE WeeklyContest1
66(
67  ContestId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
68  GameId int NOT NULL,
69  PlaceId int NOT NULL,
70  WeekNumber int NOT NUll,
71  FOREIGN KEY (GameId) REFERENCES Game(GameId),
72  FOREIGN KEY (PlaceId) REFERENCES Playgrounds(PlaceId)
73);
74GO
75
76CREATE TABLE WeeklyContest
77(
78  ContestId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
79  GameId int NOT NULL,
80  team1
81    team2
82  PlaceId int NOT NULL,
83  WeekNumber int NOT NUll,
84  FOREIGN KEY (GameId) REFERENCES Game(GameId),
85  FOREIGN KEY (PlaceId) REFERENCES Playgrounds(PlaceId)
86);
87GO
88
89
90-- DROP TRIGGER countScore_check;
91
92CREATE TRIGGER countScore_check
93ON Game
94INSTEAD OF INSERT
95AS
96BEGIN
97  INSERT INTO Game
98    SELECT
99      TeamId, PlayerId, Score
100    FROM
101      inserted
102    WHERE
103      EXISTS
104      (
105        SELECT
106          TeamId, PlayerId, Score
107        WHERE
108          Score <= 300
109      )
110END;
111GO
112
113
114
115CREATE FUNCTION dbo.GetHandicap(@eff float)
116  RETURNS INT
117AS
118  BEGIN
119    set @eff = (200 - @eff) * 0.75
120    if (@eff <= 0)
121      return 0
122    return @eff
123  END
124GO
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147CREATE TABLE TariffNames
148(
149    NameId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
150    Name nvarchar(50) NOT NULL
151);
152GO
153
154CREATE TABLE Tariff
155(
156    TariffId int IDENTITY(1,1) NOT NULL PRIMARY KEY,
157    NameId int NOT NULL,
158    MinutesCount int NOT NULL,
159    CostTariff float NOT NULL,
160    CostMinuteAfter float NOT NULL,
161    FOREIGN KEY (NameId) REFERENCES TariffNames(NameId)
162);
163GO
164
165INSERT INTO TariffNames(Name) VALUES
166  (N'По минутам')
167  , (N'Абоненский')
168  , (N'Безлимитный')
169
170INSERT INTO Tariff(NameId, CostTariff, MinutesCount, CostMinuteAfter) VALUES
171    (1, 0, 0, 1)
172    , (2, 50, 80, 8.5)
173    , (3, 100, 46080, 0) -- 32 * 24 * 60 = 46080 - минут в максимальный по дням месяц
174
175-- ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
176
177drop procedure BestTariff
178go
179
180CREATE PROCEDURE BestTariff
181  (
182    @countMin int,
183    @result nvarchar(50) output,
184    @isPrint bit
185  )
186as
187  begin
188    DECLARE tariffCursor CURSOR FOR
189      SELECT
190        TariffNames.Name, Tariff.MinutesCount, Tariff.CostTariff, Tariff.CostMinuteAfter
191      FROM
192        Tariff
193      JOIN TariffNames on Tariff.NameId = TariffNames.NameId
194
195    DECLARE @name nvarchar(50)
196    DECLARE @minutesCount int
197    DECLARE @costTariff float
198    DECLARE @costMinutesAfter float
199
200    DECLARE @minCost float
201    SET @minCost = -1
202
203    open tariffCursor
204
205    FETCH NEXT FROM
206      tariffCursor
207    INTO
208      @name, @minutesCount, @costTariff, @costMinutesAfter
209
210    WHILE @@fetch_status = 0
211      begin
212
213        DECLARE @minCostTmp int
214        SET @minCostTmp = case
215          when (@countMin - @minutesCount) > 0
216          then @countMin - @minutesCount
217          else 0
218          end
219
220        DECLARE @sum float
221        SET @sum = @costTariff + @minCostTmp * @costMinutesAfter
222
223        IF (@sum < @minCost or @minCost = -1)
224          begin
225            SET @minCost = @sum
226            SET @result = @name
227          end
228
229        FETCH NEXT FROM
230          tariffCursor
231        into
232          @name, @minutesCount, @costTariff, @costMinutesAfter
233      end
234    close tariffCursor
235    DEALLOCATE tariffCursor
236    if (@isPrint = 1)
237      print (N'Подходящий тариф: ' + @result + N'; Стоимость: ' + CONVERT(nvarchar(50), @minCost))
238  end
239GO
240
241DECLARE @tmp nvarchar(50);
242EXECUTE BestTariff 101, @tmp, 'true'
243GO
244
245-- ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
246
247drop function dbo.GetSegments
248go
249
250create function dbo.GetSegments()
251  returns @result table
252  (
253    counts float not null
254  )
255as
256  begin
257    DECLARE @tmp int
258
259    DECLARE @name_1 nvarchar(120)
260    DECLARE @minutesCount_1 int
261    DECLARE @costTariff_1 float
262    DECLARE @costMinutesAfter_1 float
263
264    DECLARE @name_2 nvarchar(120)
265    DECLARE @minutesCount_2 int
266    DECLARE @costTariff_2 float
267    DECLARE @costMinutesAfter_2 float
268
269    DECLARE TariffCursor_1 CURSOR FOR
270      SELECT
271        TariffNames.Name, Tariff.MinutesCount, Tariff.CostTariff, Tariff.CostMinuteAfter
272      FROM
273        Tariff
274      JOIN TariffNames on Tariff.NameId = TariffNames.NameId
275
276    DECLARE TariffCursor_2 CURSOR FOR
277      SELECT
278        TariffNames.Name, Tariff.MinutesCount, Tariff.CostTariff, Tariff.CostMinuteAfter
279      FROM
280        Tariff
281      JOIN TariffNames on Tariff.NameId = TariffNames.NameId
282
283    INSERT INTO @result
284      SELECT
285        Tariff.MinutesCount
286      FROM Tariff
287
288    OPEN TariffCursor_1
289    FETCH NEXT FROM TariffCursor_1
290    INTO
291      @name_1, @minutesCount_1, @costTariff_1, @costMinutesAfter_1
292
293    WHILE @@fetch_status = 0
294      begin
295        OPEN TariffCursor_2
296        FETCH NEXT FROM TariffCursor_2
297        INTO
298          @name_2, @minutesCount_2, @costTariff_2, @costMinutesAfter_2
299
300        WHILE @@fetch_status = 0
301          begin
302            IF @costMinutesAfter_1 <> 0 AND @costTariff_2 <> 0
303              begin
304                SET @tmp = (@costTariff_2 - @costTariff_1) / @costMinutesAfter_1 + @minutesCount_1
305                INSERT INTO @result values (@tmp)
306              end
307            IF @costMinutesAfter_2 - @costMinutesAfter_1 <> 0
308              begin
309                SET @tmp = (@minutesCount_1 * @costMinutesAfter_1 - @minutesCount_2 * @costMinutesAfter_2 + @costTariff_2 - @costTariff_1) /
310                         (@costMinutesAfter_2 - @costMinutesAfter_1)
311                INSERT INTO @result values (@tmp)
312              end
313            FETCH NEXT FROM TariffCursor_2
314            INTO
315              @name_2, @minutesCount_2, @costTariff_2, @costMinutesAfter_2
316          end
317
318        CLOSE TariffCursor_2
319        FETCH NEXT FROM TariffCursor_1
320        INTO
321          @name_1, @minutesCount_1, @costTariff_1, @costMinutesAfter_1
322      end
323    CLOSE TariffCursor_1
324    DEALLOCATE TariffCursor_1
325    DEALLOCATE TariffCursor_2
326
327    return
328  end
329GO
330
331SELECT DISTINCT
332  counts
333FROM
334  dbo.GetSegments()
335WHERE counts >= 0
336ORDER BY counts
337
338
339drop procedure ShowSegments
340GO
341
342CREATE PROCEDURE ShowSegments
343as
344  begin
345    DECLARE @result TABLE
346    (
347      min int NOT NULL,
348      max int NOT NULL,
349      name nvarchar(50) NOT NULL
350    )
351
352    DECLARE @min int
353    DECLARE @max int
354    DECLARE @name nvarchar(120)
355    SET @min = -1
356
357    DECLARE @minTmp INT
358    DECLARE @maxTmp INT
359    DECLARE @nameTmp nvarchar(120)
360
361    DECLARE SegmentsCursor CURSOR FOR
362      SELECT DISTINCT
363        counts
364      FROM
365        dbo.GetSegments()
366      WHERE counts >= 0
367      ORDER BY counts
368
369    OPEN SegmentsCursor
370    FETCH NEXT FROM SegmentsCursor
371    INTO @minTmp
372
373    DECLARE @fetchStatusTmp int
374    set @fetchStatusTmp = @@fetch_status
375    WHILE @fetchStatusTmp = 0
376      begin
377        FETCH NEXT FROM SegmentsCursor
378        INTO @maxTmp
379        set @fetchStatusTmp = @@fetch_status
380
381        EXECUTE BestTariff @maxTmp, @nameTmp OUTPUT, 'false'
382        IF @min = -1
383          begin
384            SET @min = @minTmp
385            SET @max = @maxTmp
386            SET @name = @nameTmp
387          end
388        ELSE
389          IF @nameTmp = @name
390            SET @max = @maxTmp
391          ELSE
392            begin
393              INSERT INTO @result VALUES (@min, @max, @name)
394              SET @min = @minTmp
395              SET @max = @maxTmp
396              SET @name = @nameTmp
397            end
398        SET @minTmp = @maxTmp
399      end
400
401    INSERT INTO @result VALUES (@min, @max, @name)
402    SELECT
403      *
404    FROM
405      @result
406    ORDER BY min
407
408    CLOSE SegmentsCursor
409    DEALLOCATE SegmentsCursor
410  end
411GO
412
413-- ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
414
415SELECT
416  TariffNames.Name, Tariff.MinutesCount, Tariff.CostTariff, Tariff.CostMinuteAfter
417FROM
418  Tariff
419JOIN TariffNames on Tariff.NameId = TariffNames.NameId
420GO
421
422ShowSegments
423GO
424
425DECLARE @t nvarchar;
426EXECUTE BestTariff 100, @t OUTPUT
427GO