bNxxYz8R

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 TRIGGER InsertRate ON Rate
40FOR INSERT
41AS
42BEGIN
43    declare @q1 float
44	declare @q2 float
45	declare @q3 float
46	set @q1 =	(SELECT min(subscriptionFee) FROM INSERTED)
47	set @q2 =	(SELECT min(timeSubscription) FROM INSERTED)
48	set @q3 =	(SELECT min(forMore)     FROM INSERTED)
49
50	if (@q1 < 0 or @q3 < 0)
51	BEGIN
52	 RAISERROR (N'Отрицательная сумма недопустима ', 16, 1)
53	 ROLLBACK TRANSACTION
54	END
55
56	if (@q2 < 0)
57	BEGIN
58	 RAISERROR (N'Отрицательное время недопустимо ', 16, 1)
59	 ROLLBACK TRANSACTION
60	END
61END
62go
63
64
65ALTER TABLE Rate ENABLE TRIGGER InsertRate
66GO
67
68CREATE PROCEDURE DetectRate (@timeU int)
69AS
70BEGIN
71	DECLARE @minName NVARCHAR(150)
72	DECLARE @minCost float
73	SET @minCost = -1
74	DECLARE @name NVARCHAR(150)
75    DECLARE @timeIn INT
76    DECLARE @subF FLOAT
77    DECLARE @forMore FLOAT
78	DECLARE c CURSOR FOR SELECT
79                           nameT,
80                           subscriptionFee,
81                           timeSubscription,
82                           forMore
83                         FROM Rate
84    OPEN c
85    FETCH NEXT FROM c
86    INTO @name, @subF, @timeIn, @forMore
87    WHILE @@fetch_status = 0
88		BEGIN
89				DECLARE @sum FLOAT
90		DECLARE @temp INT
91		IF @timeU < @timeIn
92			SET @temp = 0
93		ELSE 
94			SET @temp = @timeU - @timeIn
95		SET @sum = @subF + @temp * @forMore
96		IF ( @minCost = -1 OR @sum < @minCost)
97			BEGIN
98				SET @minCost = @sum
99				SET @minName = @name
100			END
101		FETCH NEXT FROM c
102		INTO @name, @subF, @timeIn, @forMore
103		END
104    CLOSE c
105    DEALLOCATE c
106    PRINT N'Выгодный тариф:' + @minName + N', его стоимость в месяц:' + cast((@minCost) as varchar(12))
107  END
108GO
109
110
111
112CREATE FUNCTION	takeMax(@num1 int,  @num2 int)
113RETURNS int
114AS
115BEGIN
116	IF @num1 > @num2
117		RETURN @num1
118	RETURN @num2
119END
120GO
121
122
123
124insert into Rate values
125(N'Безлимит', 500, 0, 0)
126,(N'С абонентской платой', 100, 100, 1)
127,(N'Без абонентской платы', 0, 0, 1)
128go
129
130SELECT nameT AS N'Название тарифа', subscriptionFee AS N'Абонентская плата', 
131		timeSubscription AS N'Оплаченые абонентские минуты', forMore AS N'Стоимость минуты свыше'  FROM Rate
132GROUP BY nameT, subscriptionFee, timeSubscription, forMore
133GO
134
135DetectRate 12