U7nze9x9

1USE Master
2GO
3IF  EXISTS (
4	SELECT name 
5		FROM sys.databases 
6		WHERE name = N'Lab4'
7)
8ALTER DATABASE [Lab4] set single_user with rollback immediate
9GO
10IF  EXISTS (
11	SELECT name
12		FROM sys.databases 
13		WHERE name = N'Lab4'
14)
15DROP DATABASE [Lab4]
16GO
17CREATE DATABASE [Lab4]
18Go
19USE [Lab4]
20GO
21
22CREATE TABLE Rate_info (
23	rate_id int NOT NULL identity primary key,
24	rate_name char(50),
25	cost float,
26	minutes_number int CHECK (minutes_number<=43200),
27	additional_minute float
28)
29GO
30
31INSERT INTO Rate_info values
32	('Без абонентской платы', 0, 0, 1.5), 
33	('Безлимитный', 1200, 43200, 0), 
34	('300 Минут', 258, 300, 3.5), 
35	('500 Минут', 550, 500, 2.5)
36GO
37
38CREATE TABLE Rate_segment (
39	segment_id int NOT NULL identity primary key,
40	rate_name char(50),
41	segment_start float NOT NULL,
42	segment_end float NOT NULL 
43)
44GO
45
46create function cost(@min float, @rate char(50))
47  returns float
48as
49  begin
50    declare @cost float;
51    declare @minutes_number float;
52    declare @additional_minute float;
53	select @cost = cost, @minutes_number = minutes_number, @additional_minute = additional_minute
54    from Rate_info where rate_name like @rate;
55	if @min >= @minutes_number set @min = @min - @minutes_number;
56    else set @min = 0;
57
58    return @cost + @min * @additional_minute;
59  end
60go
61
62create function best_rate(@minutes float)
63  returns char(50)
64as
65  begin
66    declare @min float;
67    select @min = min(dbo.cost(@minutes, rate_name))
68		from Rate_info;
69	declare @rate varchar(25);
70    select @rate = rate_name
71    from Rate_info where dbo.cost(@minutes, rate_name) = @min;
72
73    return @rate;
74  end
75go
76
77CREATE PROCEDURE needed_rate
78as
79	BEGIN
80		declare @point table(x float);
81		insert into @point values (0);
82		insert into @point values (43200);
83
84		insert into @point
85			select (r2.cost - r1.cost) / r1.additional_minute + r1.minutes_number
86				from Rate_info as r1 join Rate_info as r2 on r1.cost < r2.cost
87					where r1.additional_minute != 0;
88		insert into @point
89			select (r1.additional_minute * r1.minutes_number - r2.additional_minute * r2.minutes_number + r2.cost - r1.cost) /
90						 (r1.additional_minute - r2.additional_minute)
91				from Rate_info as r1 join Rate_info as r2 on r1.cost < r2.cost
92					where (r1.additional_minute - r2.additional_minute) != 0;
93		declare @segment table (x float, number int);
94		insert into @segment
95			select *, row_number() over (order by x) as number
96				from @point where x >= 0;
97
98		declare c cursor for
99			select x1.x, x2.x, dbo.best_rate((x2.x + x1.x) / 2) as rate
100				from @segment as x1 join @segment as x2 on x1.number + 1 = x2.number;
101		declare @x11 float;
102		declare @x12 float;
103		declare @x21 float;
104		declare @x22 float;
105		declare @rate1 char(50); declare @rate2 char(50);
106
107		open c;
108		fetch next from c into @x11, @x21, @rate1;
109		while @@fetch_status = 0
110			begin
111				fetch next from c into @x12, @x22, @rate2;
112				if @rate2 not like @rate1
113					begin
114						insert into Rate_segment values (@rate1, @x11, @x21);
115						set @x11 = @x12;
116						set @rate1 = @rate2;
117					end
118				set @x21 = @x22;
119			end;
120		insert into Rate_segment values (@rate1, @x11, @x22);
121		close c;
122		deallocate c;
123	END
124go
125EXECUTE needed_rate;
126go
127
128create procedure get_rate(@minutes int)
129as
130  begin
131    declare @rate char(50) = (select rate_name from Rate_segment 
132								where @minutes>=segment_start and @minutes<=segment_end)
133    select (@rate) as Rate;
134  end
135go
136
137EXECUTE get_rate 380;
138
139select * from Rate_segment