BUGv73gz

1use master 
2go
3
4if exists (
5	select	name 
6	from	sys.databases 
7	where	name = N'Mamonov' )
8alter database [Mamonov] set single_user with rollback immediate
9go
10
11if exists (
12	select	name 
13	from	sys.databases 
14	where	name = N'Mamonov' )
15drop database [Mamonov]
16go
17
18create database [Mamonov]
19go
20
21use [Mamonov]
22go
23
24
25if object_id('Mamonov.Tariffs', 'U') is not null
26	drop table  Mamonov.Tariffs
27go
28
29create table Tariffs(
30	NameTariff nvarchar(50) not null,
31	QuantityMinutes float not null,
32	SubscriptionFee float not null,
33	PriceAdditionalMinute float not null,
34	constraint CorrectQuantityMinutes check(QuantityMinutes between 0 and 44640))
35go
36
37
38insert into Tariffs(NameTariff, QuantityMinutes, SubscriptionFee, PriceAdditionalMinute) values
39	('Без абонентской платы', 0, 0, 1),
40	('Абонентский 300 – минут', 300, 100, 2),
41	('Безлимитный', 44640, 1500, 0)
42go
43
44
45if object_id('Mamonov.BestTariff', 'F') is not null
46    drop function Mamonov.BestTariff
47go
48
49create function BestTariff(@RealQuantityMinutes float)
50-- без учёта дробных чисел (стоимость за минуту умножается на нецелое число)
51-- для проверки границы НУЖНО использовать с дробными
52-- для нахождения стоимости аргумент округлять в большую сторону
53returns nvarchar(50)
54as
55	begin
56	--set @RealQuantityMinutes = CEILING(@RealQuantityMinutes)
57	if @RealQuantityMinutes > 44640 or 0 > @RealQuantityMinutes
58		return null
59	declare @BestCost float
60	set @BestCost = (select max(SubscriptionFee) from Tariffs) + (select max(PriceAdditionalMinute) from Tariffs) * 44640
61	declare @NameBestTariff nvarchar(50)
62	declare @NameTariff nvarchar(50)
63	declare @QuantityMinutes float
64	declare @SubscriptionFee float
65	declare @PriceAdditionalMinute float
66	declare @cursor cursor
67	set @cursor = cursor scroll for select NameTariff, QuantityMinutes, SubscriptionFee, PriceAdditionalMinute from Tariffs
68	open @cursor
69	while 1 = 1
70	begin
71		fetch next from @cursor into @NameTariff, @QuantityMinutes, @SubscriptionFee, @PriceAdditionalMinute
72		if @RealQuantityMinutes > @QuantityMinutes
73			begin
74				if @BestCost > (@RealQuantityMinutes - @QuantityMinutes)*@PriceAdditionalMinute + @SubscriptionFee
75					begin
76						set @BestCost = (@RealQuantityMinutes - @QuantityMinutes)*@PriceAdditionalMinute + @SubscriptionFee
77						set @NameBestTariff = @NameTariff
78					end
79			end
80		else
81			begin
82				if @BestCost > @SubscriptionFee
83					begin
84						set @BestCost = @SubscriptionFee
85						set @NameBestTariff = @NameTariff
86					end
87			end
88		if @@FETCH_STATUS != 0
89			break
90	end
91	close @cursor
92	deallocate @cursor
93	return @NameBestTariff
94	end
95go
96
97
98IF OBJECT_ID('Mamonov.SplitSegment', 'P') is not null
99	DROP PROCEDURE Mamonov.SplitSegment
100GO
101
102create procedure SplitSegment
103as
104	create table Points(Point float)
105	insert into Points select  distinct * from (select QuantityMinutes from Tariffs union select SubscriptionFee from Tariffs) as tz
106	create table Segments(Start float, Finish float,Tariff nvarchar(50))
107	declare @segment_start float
108	declare @segment_end float
109	set @segment_start = 0
110	declare @max_point float
111	set @max_point = 44640
112	while @segment_start != @max_point
113		begin
114			set @segment_end = (select min(Point) from Points where Point > @segment_start)
115			if dbo.BestTariff(@segment_start + 0.01) = dbo.BestTariff(@segment_end - 0.01)
116				begin
117					insert into Segments select @segment_start, @segment_end, dbo.BestTariff(@segment_start+0.01)
118					set @segment_start = @segment_end
119				end
120			else
121				begin
122					insert into Points select FLOOR((@segment_end + @segment_start)/2)
123				end
124		end
125	declare @some float
126	declare @tariff nvarchar(50)
127	while 1 = 1
128		begin
129		if exists(select main.Start from Segments main, Segments sub where main.Start = sub.Finish and main.Tariff = sub.Tariff)
130			begin
131				set @segment_start = (select min(main.Start) from Segments main, Segments sub where main.Finish = sub.Start and main.Tariff = sub.Tariff)
132				set @some = (select Finish from Segments where Start = @segment_start)
133				set @segment_end = (select Finish from Segments where Start = @some)
134				set @tariff = (select Tariff from Segments where Start = @segment_start)
135				delete Segments where Start = @segment_start or Finish = @segment_end
136				insert into Segments select @segment_start, @segment_end, @tariff
137			end
138		else
139			break
140		end
141	
142	drop table Points
143	select Tariff, Start, Finish from Segments
144	order by start
145	drop table Segments
146go
147
148
149if object_id('Mamonov.FindCost', 'F') is not null
150    drop function Mamonov.FindCost
151go
152
153create function FindCost(@RealQuantityMinutes float, @NameNeedTariff nvarchar(50))
154returns float
155as
156	begin
157	if @RealQuantityMinutes > 44640 or 0 > @RealQuantityMinutes
158		return null
159	declare @NameTariff nvarchar(50)
160	declare @QuantityMinutes float
161	declare @SubscriptionFee float
162	declare @PriceAdditionalMinute float
163	declare @cursor cursor
164	set @cursor = cursor scroll for select NameTariff, QuantityMinutes, SubscriptionFee, PriceAdditionalMinute from Tariffs
165	open @cursor
166	while 1 = 1
167	begin
168		fetch next from @cursor into @NameTariff, @QuantityMinutes, @SubscriptionFee, @PriceAdditionalMinute
169		if @NameNeedTariff = @NameTariff
170			begin
171				if @RealQuantityMinutes > @QuantityMinutes
172					begin
173						return (@RealQuantityMinutes - @QuantityMinutes)*@PriceAdditionalMinute + @SubscriptionFee
174					end
175				else
176					begin
177						return @SubscriptionFee
178					end
179			end
180		if @@FETCH_STATUS != 0
181			break
182	end
183	close @cursor
184	deallocate @cursor
185	return 0
186	end
187go
188
189declare @interesing_count_minutes float = 501
190select @interesing_count_minutes as 'Интересующее количество минут',
191	   dbo.BestTariff(@interesing_count_minutes) as 'Самый выгодный тариф',
192	   dbo.FindCost(@interesing_count_minutes, dbo.BestTariff(@interesing_count_minutes)) as 'Стоимость'
193
194exec SplitSegment