4tm7Lqfg

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 PROCEDURE DetectRate (@timeU int)
40AS
41BEGIN
42	DECLARE @minName NVARCHAR(150)
43	DECLARE @minCost float
44	SET @minCost = -1
45	DECLARE @name NVARCHAR(150)
46    DECLARE @timeIn INT
47    DECLARE @subF FLOAT
48    DECLARE @forMore FLOAT
49	DECLARE c CURSOR FOR SELECT
50                           nameT,
51                           subscriptionFee,
52                           timeSubscription,
53                           forMore
54                         FROM Rate
55    OPEN c
56    FETCH NEXT FROM c
57    INTO @name, @subF, @timeIn, @forMore
58	PRINT N'ee'
59    WHILE @@fetch_status = 0
60		PRINT N'eee ' +cast( @name as nvarchar(900)) +cast(  @subF as nvarchar(900)) + cast( @timeIn as nvarchar(900))
61		BEGIN
62		DECLARE @sum FLOAT
63		DECLARE @temp INT
64		IF @timeU < @timeIn
65			SET @temp = 0
66		ELSE 
67			SET @temp = @timeU - @timeIn
68		SET @sum = @subF + @temp * @forMore
69		IF ( @minCost = -1 OR @sum < @minCost)
70			BEGIN
71				SET @minCost = @sum
72				SET @minName = @name
73			END
74		FETCH NEXT FROM c
75		INTO @name, @subF, @timeIn, @forMore
76		END
77    CLOSE c
78    DEALLOCATE c
79	PRINT N'eeee'
80    PRINT N'Выгодный тариф:' + @minName + N', его стоимость в месяц:' + cast((@minCost) as varchar(12))
81  END
82GO
83
84
85
86CREATE FUNCTION	takeMax(@num1 int,  @num2 int)
87RETURNS int
88AS
89BEGIN
90	IF @num1 > @num2
91		RETURN @num1
92	RETURN @num2
93END
94GO
95
96
97
98insert into Rate values
99(N'Безлимит', 500, 0, 0)
100,(N'С абонентской платы', 100, 100, 1)
101,(N'Без абонентской платы', 0, 0, 12)
102go
103
104SELECT nameT AS N'Название тарифа', subscriptionFee AS N'Абонентская плата', 
105		timeSubscription AS N'Оплаченые абонентские минуты', forMore AS N'Стоимость минуты свыше'  FROM Rate
106GROUP BY nameT, subscriptionFee, timeSubscription, forMore
107GO
108
109DetectRate 12