5sHq0kaP

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	monthCost		float,
31	minutesCount	float,
32	overflowCost	money,
33	check (monthCost >= 0 and minutesCount >= 0 and overflowCost >= 0)
34)
35go
36
37insert into Tariffs(name, monthCost, minutesCount, overflowCost) values
38	(N'Без абонентской платы',0,0, 0.5),
39	(N'Абонентский', 2 ,6, 1),
40	(N'Безлимитный',5,0,0)
41go
42
43if object_id( 'GusarovVadim.GetCost', 'F' ) is not null   
44    drop function GusarovVadim.GetCost
45go
46
47create function GetCost(@name varchar(100), @count float)
48returns float as
49begin
50	declare @overflow float; declare @plancost float; declare @minuteco float;
51    select @overflow = minutesCount from Tariffs where @name=name
52	select @plancost = monthCost from Tariffs where @name=name
53	select @minuteco = overflowCost from Tariffs where @name=name
54	if (@count < @overflow) return @plancost
55	return @plancost + (@count - @overflow) * @minuteco
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), @a float, @b float)
64returns float as
65begin return (dbo.GetCost(@name, @a) + dbo.GetCost(@name, @b)) / 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, @minutes, @minutes)) from Tariffs
76	select @nameOfTariff = name from Tariffs where @min = dbo.Median(name, @minutes, @minutes)
77	print(@nameOfTariff)
78go
79
80exec GetTariff 9
81
82declare @previous float = 0;
83declare @current float = 0;
84declare interval_cursor cursor for  
85select distinct b.minutesCount + a.monthCost / b.overflowCost 
86from Tariffs a, Tariffs b
87where b.overflowCost != 0 and b.minutesCount + a.monthCost / b.overflowCost > 0
88union (select 60 * 24 * 30)
89order by b.minutesCount + a.monthCost / b.overflowCost;
90
91open interval_cursor
92fetch next from interval_cursor   
93into @current
94
95declare @last_min_name varchar(100) = '';
96declare @last int = 0;
97while @@fetch_status = 0
98begin
99	declare @min float = 0; declare @min_name varchar(100);
100	select @min = min(dbo.Median(name, @previous, @current)) from Tariffs
101	select @min_name = name from Tariffs where @min = dbo.Median(name, @previous, @current)
102	if (@previous != 0 and @last_min_name != @min_name)
103	print(N'Интервал: ' 
104			+ 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(N'Интервал: '
116		+ convert(varchar(100), @last, 3) 
117		+ ' -> ' 
118		+  convert(varchar(100), @current, 3)
119		+ ' - ' + @last_min_name
120)
121
122
123close interval_cursor;  
124deallocate interval_cursor;
125go