du8PiPda

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, 0.5),
39	(N'Абонентский 50-минутный', 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(@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, @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
81exec GetTariff 55
82exec GetTariff 70
83
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
92declare @_count float = 1;
93
94while @@FETCH_STATUS = 0
95begin
96	set @_count = @_count + 1;
97	fetch next from _intervalСursor   
98end
99
100declare @previous float = 0;
101declare @current float = 0;
102declare intervalСursor cursor for  
103select distinct round(b.minutesCount + a.mounthCost / b.overflowCost, 0)
104from Tariffs a, Tariffs b
105where b.overflowCost != 0 and round(b.minutesCount + a.mounthCost / b.overflowCost, 0) > 0
106order by round(b.minutesCount + a.mounthCost / b.overflowCost, 0);
107
108set @current = 0;
109open intervalСursor
110fetch next from intervalСursor   
111into @current
112
113declare @number float = 0;
114
115while @@fetch_status = 0  
116begin
117	set @number = @number + 1;
118	declare @min float = 0; declare @minName varchar(100);
119	select @min = min(dbo.Median(name, @previous, @current)) from Tariffs
120	select @minName = name from Tariffs where @min = dbo.Median(name, @previous, @current)
121	if (@previous = 4) 
122		set @current = 8;
123		fetch next from intervalСursor   
124		into @current
125
126	if (@number + 3 = @_count) set @current = 43800;
127	print(N'Интервал: ' 
128			+ convert(varchar(100), @previous) 
129			+ ' -> ' 
130			+  convert(varchar(100), @current)
131			+ ' - ' + @minName
132	)
133	select @previous = @current;
134	fetch next from intervalСursor   
135    into @current  
136end
137
138close _intervalСursor
139deallocate _intervalСursor;
140go
141
142close intervalСursor;  
143deallocate intervalСursor;
144go