Mqgz1FYQ

1use master 
2go
3
4if exists (
5	select	name 
6	from	sys.databases 
7	where	name = N'_Kvashnina' )
8alter database [_Kvashnina] set single_user with rollback immediate
9go
10
11if exists (
12	select	name 
13	from	sys.databases 
14	where	name = N'_Kvashnina' )
15drop database [_Kvashnina]
16go
17
18create database [_Kvashnina]
19go
20
21use [_Kvashnina]
22go
23
24if object_id('_Kvashnina.Plans', 'U') is not null
25	drop table  _Kvashnina.Plans
26go
27
28create table Plans (
29	name			varchar(100),
30	mounthCost		float,
31	minutesCount	float,
32	overCost	money,
33	check (mounthCost >= 0 and minutesCount >= 0 and overCost >= 0)
34)
35go
36
37insert into Plans(name, mounthCost, minutesCount, overCost) values
38	(N'Без абонентской платы',	0,	10, 1),
39	(N'Абонентский', 5,	20, 1),
40	(N'Безлимитный', 10, 0,	0),
41	(N'Спецпредложение',0,	0,	2)
42go
43
44if object_id( '_Kvashnina.GetCost', 'F' ) is not null   
45    drop function _Kvashnina.GetCost
46go
47
48create function GetCost(@name varchar(100), @count float)
49returns float as
50begin
51	declare @over float; declare @plancost float; declare @minuteco float;
52    select @over = minutesCount from Plans where @name=name
53	select @plancost = mounthCost	from Plans where @name=name
54	select @minuteco = overCost from Plans where @name=name
55	if (@count < @over) return @plancost
56	return @plancost + (@count - @over) * @minuteco
57end
58go
59
60if object_id( '_Kvashnina.Medium', 'F' ) is not null   
61    drop function _Kvashnina.Medium
62go
63
64create function Medium(@name varchar(100), @a float, @b float)
65returns float as
66begin return (dbo.GetCost(@name, @a) + dbo.GetCost(@name, @b)) / 2 end
67go
68
69declare @previous float = 0;
70declare @current float = 0;
71declare interval_cursor cursor for  
72select distinct b.minutesCount + a.mounthCost / b.overCost 
73from Plans a, Plans b
74where b.overCost != 0 and b.minutesCount + a.mounthCost / b.overCost > 0
75order by b.minutesCount + a.mounthCost / b.overCost;
76
77open interval_cursor
78fetch next from interval_cursor   
79into @current
80
81while @@fetch_status = 0  
82begin
83	declare @min float = 0; declare @minName varchar(100);
84	select @min = min(dbo.Medium(name, @previous, @current)) from Plans
85	select @minName = name from Plans where @min = dbo.Medium(name, @previous, @current)
86	print(N'Интервал: ' 
87			+ convert(varchar(100), @previous, 3) 
88			+ ' -> ' 
89			+  convert(varchar(100), @current, 3)
90			+ ' - ' + @minName
91	)
92	select @previous = @current;
93	fetch next from interval_cursor   
94    into @current  
95end
96
97close interval_cursor;  
98deallocate interval_cursor;
99go