qK68srTy

1use master 
2go
3
4if exists (
5	select	name 
6	from	sys.databases 
7	where	name = N'Nagaev' )
8alter database [Nagaev] set single_user with rollback immediate
9go
10
11if exists (
12	select	name 
13	from	sys.databases 
14	where	name = N'Nagaev' )
15drop database [Nagaev]
16go
17
18create database [Nagaev]
19go
20
21use [Nagaev]
22go
23
24if object_id('Nagaev.Tariff', 'U') is not null
25	drop table  Nagaev.Tariff
26go
27
28create table Tariff (
29	name			varchar(100),
30	month_cost		float,
31	minutes_count	float,
32	overflow_cost	float,
33	check (month_cost >= 0 and minutes_count >= 0 and overflow_cost >= 0)
34)
35go
36
37insert into Tariff(name, month_cost, minutes_count, overflow_cost) values
38	(N'Без абонентской платы', 0, 0, 0.5),
39	(N'Абонентский – 6 минут', 2, 6, 1),
40	(N'Безлимитный', 5, 0, 0)
41go
42
43if object_id( 'Nagaev.GetCost', 'F' ) is not null   
44    drop function Nagaev.GetCost
45go
46
47if object_id( 'Nagaev.GetTariff', 'F' ) is not null   
48    drop procedure Nagaev.GetCost
49go
50
51create procedure GetTariff(@minutes float)
52as
53begin
54declare @tariffName varchar(100)
55declare @min float;
56select @min = min(dbo.Median(name, @minutes, @minutes)) from Tariff
57select @tariffName = name from Tariff where dbo.Median(name, @minutes, @minutes) = @min
58print('Самый выгодный тариф для ' + convert(varchar(20), @minutes) + ' минут: ' + @tariffName)
59end
60go
61
62create function GetCost(@name varchar(100), @count float)
63returns float as
64begin
65	declare @overflow float; declare @plancost float; declare @minuteco float;
66    select @overflow = minutes_count from Tariff where @name=name
67	select @plancost = month_cost	from Tariff where @name=name
68	select @minuteco = overflow_cost from Tariff where @name=name
69	if (@count < @overflow) return @plancost
70	return @plancost + (@count - @overflow) * @minuteco
71end
72go
73
74if object_id( 'Nagaev.Median', 'F' ) is not null   
75    drop function Nagaev.Median
76go
77
78create function Median(@name varchar(100), @a float, @b float)
79returns float as
80begin return (dbo.GetCost(@name, @a) + dbo.GetCost(@name, @b)) / 2 end
81go
82
83declare @previous float = 0;
84declare @current float = 0;
85declare interval_cursor cursor for  
86select distinct b.minutes_count + a.month_cost / b.overflow_cost 
87from Tariff a, Tariff b
88where b.overflow_cost != 0 and b.minutes_count + a.month_cost / b.overflow_cost > 0
89union (select 60 * 24 * 30)
90order by b.minutes_count + a.month_cost / b.overflow_cost;
91
92open interval_cursor
93fetch next from interval_cursor   
94into @current
95
96declare @last_min_name varchar(100) = '';
97declare @last int = 0;
98while @@fetch_status = 0
99begin
100	declare @min float = 0; declare @min_name varchar(100);
101	select @min = min(dbo.Median(name, @previous, @current)) from Tariff
102	select @min_name = name from Tariff where @min = dbo.Median(name, @previous, @current)
103	if (@previous != 0 and @last_min_name != @min_name)
104	print(convert(varchar(100), @last, 3) 
105			+ ' -> ' + convert(varchar(100), @previous, 3)
106			+ ' - '  + @last_min_name
107	)
108	if (@last_min_name != @min_name)
109		select @last = @previous;
110	select @previous = @current;
111	select @last_min_name = @min_name;
112	fetch next from interval_cursor   
113    into @current  
114end
115print(convert(varchar(100), @last, 3) 
116		+ ' -> ' +  convert(varchar(100), @current, 3)
117		+ ' - ' + @last_min_name
118)
119
120
121close interval_cursor;  
122deallocate interval_cursor;
123go
124
125exec GetTariff 5
126go