· 9 years ago · Dec 11, 2016, 07:18 PM
1use master
2GO
3
4IF EXISTS (
5SELECT name
6FROM sys.databases
7WHERE name = N'[ AMitrofanova]'
8)
9ALTER DATABASE AMitrofanova set single_user with rollback immediate
10GO
11
12IF EXISTS (
13SELECT name
14FROM sys.databases
15WHERE name = N'AMitrofanova'
16)
17DROP DATABASE AMitrofanova
18GO
19
20IF OBJECT_ID('AMitrofanova') IS NOT NULL
21DROP DATABASE AMitrofanova
22GO
23
24CREATE DATABASE AMitrofanova
25GO
26
27USE AMitrofanova
28GO
29
30CREATE TABLE Rate
31(
32nameT nvarchar(150) not null,
33subscriptionFee float not null,
34timeSubscription int not null,
35forMore float not null
36)
37go
38
39CREATE TABLE Itog
40(
41 start INT NOT NULL,
42 fin INT NOT NULL,
43 name NVARCHAR(120) NOT NULL
44)
45go
46
47CREATE TRIGGER InsertRate ON Rate
48FOR INSERT
49AS
50BEGIN
51 declare @q1 float
52 declare @q2 float
53 declare @q3 float
54 set @q1 = (SELECT min(subscriptionFee) FROM INSERTED)
55 set @q2 = (SELECT min(timeSubscription) FROM INSERTED)
56 set @q3 = (SELECT min(forMore) FROM INSERTED)
57
58 if (@q1 < 0 or @q3 < 0)
59 BEGIN
60 RAISERROR (N'ÐžÑ‚Ñ€Ð¸Ñ†Ð°Ñ‚ÐµÐ»ÑŒÐ½Ð°Ñ Ñумма недопуÑтима ', 16, 1)
61 ROLLBACK TRANSACTION
62 END
63
64 if (@q2 < 0)
65 BEGIN
66 RAISERROR (N'Отрицательное Ð²Ñ€ÐµÐ¼Ñ Ð½ÐµÐ´Ð¾Ð¿ÑƒÑтимо ', 16, 1)
67 ROLLBACK TRANSACTION
68 END
69END
70go
71
72
73ALTER TABLE Rate ENABLE TRIGGER InsertRate
74GO
75
76CREATE PROCEDURE DetectRate (@timeU int, @flag int, @minName NVARCHAR(120) OUTPUT)
77AS
78BEGIN
79 DECLARE @minCost float
80 SET @minCost = -1
81 DECLARE @name NVARCHAR(150)
82 DECLARE @timeIn INT
83 DECLARE @subF FLOAT
84 DECLARE @forMore FLOAT
85 DECLARE c CURSOR FOR SELECT
86 nameT,
87 subscriptionFee,
88 timeSubscription,
89 forMore
90 FROM Rate
91 OPEN c
92 FETCH NEXT FROM c
93 INTO @name, @subF, @timeIn, @forMore
94 WHILE @@fetch_status = 0
95 BEGIN
96 DECLARE @sum FLOAT
97 DECLARE @temp INT
98 IF @timeU < @timeIn
99 SET @temp = 0
100 ELSE
101 SET @temp = @timeU - @timeIn
102 SET @sum = @subF + @temp * @forMore
103 IF ( @minCost = -1 OR @sum < @minCost)
104 BEGIN
105 SET @minCost = @sum
106 SET @minName = @name
107 END
108 FETCH NEXT FROM c
109 INTO @name, @subF, @timeIn, @forMore
110 END
111 CLOSE c
112 DEALLOCATE c
113 if @flag = 1
114 PRINT N'Выгодный тариф: ' + @minName + N', его ÑтоимоÑÑ‚ÑŒ в меÑÑц: ' + cast((@minCost) as varchar(12))
115 END
116GO
117
118CREATE PROCEDURE FindPoints
119AS
120BEGIN
121 DECLARE @POINTS TABLE
122 (
123 X varchar(32) not null
124 )
125 DECLARE @name1 NVARCHAR(150)
126 DECLARE @timeIn1 INT
127 DECLARE @subF1 FLOAT
128 DECLARE @forMore1 FLOAT
129 DECLARE c1 CURSOR FOR SELECT
130 nameT,
131 subscriptionFee,
132 timeSubscription,
133 forMore
134 FROM Rate
135 OPEN c1
136 FETCH NEXT FROM c1
137 INTO @name1, @subF1, @timeIn1, @forMore1
138 WHILE @@fetch_status = 0
139 BEGIN
140 DECLARE @x FLOAT
141 DECLARE @name2 NVARCHAR(150)
142 DECLARE @timeIn2 INT
143 DECLARE @subF2 FLOAT
144 DECLARE @forMore2 FLOAT
145 DECLARE c2 CURSOR FOR SELECT
146 nameT,
147 subscriptionFee,
148 timeSubscription,
149 forMore
150 FROM Rate
151 OPEN c2
152 FETCH NEXT FROM c2
153 INTO @name2, @subF2, @timeIn2, @forMore2
154 WHILE @@fetch_status = 0
155 BEGIN
156 IF @name1 != @name2
157 BEGIN
158 IF @forMore1 <> 0 AND @subF2 <> 0
159 begin
160 SET @x = (@subF2 - @subF1) / @forMore1 + @timeIn1
161 IF @x > 0 AND @x < 43200
162 INSERT INTO @POINTS SELECT @x WHERE NOT exists(SELECT * FROM @POINTS WHERE X = @x)
163 end
164 IF @forMore2 - @forMore1 <> 0
165 begin
166 SET @x = (@forMore1 * @timeIn1 - @forMore2 * @timeIn2 - @subF1 + @subF2)/(@forMore1 - @forMore2)
167 IF @x > 0 AND @x < 43200
168 begin
169 INSERT INTO @POINTS SELECT @x WHERE NOT exists(SELECT * FROM @POINTS WHERE X = @x)
170 end
171 end
172 END
173
174 FETCH NEXT FROM c2
175 INTO @name2, @subF2, @timeIn2, @forMore2
176 END
177 CLOSE c2
178 DEALLOCATE c2
179
180
181 FETCH NEXT FROM c1
182 INTO @name1, @subF1, @timeIn1, @forMore1
183 END
184 CLOSE c1
185 DEALLOCATE c1
186 INSERT INTO @POINTS values (0), (43200)
187
188 DECLARE @start INT
189 DECLARE @fin INT
190 DECLARE @name NVARCHAR(120)
191 SET @name = N''
192 DECLARE @st int
193 DECLARE @curStart INT
194 DECLARE @curFin INT
195 DECLARE @curName NVARCHAR(120)
196
197 DECLARE cc CURSOR FOR SELECT X
198 FROM @points
199 ORDER BY X
200 OPEN cc
201 FETCH NEXT FROM cc
202 INTO @curStart
203 set @st = @@fetch_status
204 WHILE @st = 0
205 BEGIN
206 FETCH NEXT FROM cc
207 INTO @curFin
208 set @st = @@fetch_status
209 DECLARE @mid INT
210 SET @mid = cast((@curFin + @curStart) / 2 AS INT)
211 EXECUTE DetectRate @mid, 0, @curName OUTPUT
212 IF @name != N''
213 IF @curName = @name
214 BEGIN
215 SET @fin = @curFin
216 END
217 ELSE
218 BEGIN
219 INSERT INTO Itog VALUES (@start, @fin, @name)
220 SET @start = @curStart
221 SET @fin = @curFin
222 SET @name = @curName
223 END
224 ELSE
225 BEGIN
226 SET @start = @curStart
227 print @start
228 SET @fin = @curFin
229 SET @name = @curName
230 END
231
232 SET @curStart = @curFin
233 END
234 INSERT INTO Itog VALUES (@start, @fin, @name)
235 CLOSE cc
236 DEALLOCATE cc
237
238END
239GO
240
241
242CREATE FUNCTION takeMax(@num1 int, @num2 int)
243RETURNS int
244AS
245BEGIN
246 IF @num1 > @num2
247 RETURN @num1
248 RETURN @num2
249END
250GO
251
252
253/*
254insert into Rate values
255(N'Безлимит', 65, 0, 0)
256,(N'С абонентÑкой платой', 50, 16, 5)
257,(N'Без абонентÑкой платы', 0, 0, 2.5)
258go*/
259
260/*
261insert into Rate values
262(N'Безлимит', 65, 0, 0)
263,(N'С абонентÑкой платой', 70, 16, 5)
264,(N'Без абонентÑкой платы', 0, 0, 5)
265go
266*/
267
268insert into Rate values
269(N'Безлимит', 65, 0, 0)
270,(N'С абонентÑкой платой', 50, 16, 5)
271,(N'Без абонентÑкой платы', 0, 0, 5)
272go
273
274
275SELECT nameT AS N'Ðазвание тарифа', subscriptionFee AS N'ÐбонентÑÐºÐ°Ñ Ð¿Ð»Ð°Ñ‚Ð°',
276 timeSubscription AS N'Оплаченые абонентÑкие минуты', forMore AS N'СтоимоÑÑ‚ÑŒ минуты Ñвыше' FROM Rate
277GROUP BY nameT, subscriptionFee, timeSubscription, forMore
278GO
279
280FindPoints
281go
282
283
284SELECT start as N'С', fin as N'До', name as N'Тариф' FROM Itog ORDER BY start
285
286
287DECLARE @t NVARCHAR;
288EXECUTE DetectRate 100, 1, @t OUTPUT
289GO