u6kyPUTK

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