WNMnceXR

1USE master
2go
3 
4if EXISTS(
5    select name
6    from sys.databases
7    where name = N'Sergey.Ubogov'
8)
9    ALTER DATABasE [Sergey.Ubogov] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
10go
11 
12if EXISTS(
13    select name
14    from sys.databases
15    where name = N'Sergey.Ubogov'
16)
17    DROP DATABasE [Sergey.Ubogov]
18go
19 
20CREATE DATABasE [Sergey.Ubogov]
21go
22 
23USE [Sergey.Ubogov]
24go
25 
26if OBJECT_ID('tarifs', 'U') IS NOT NULL
27    DROP TABLE tarifs
28go
29 
30CREATE TABLE tarifs
31(
32    Name nvarchar(100) NOT NULL,
33    LicenseFee money NOT NULL,
34    MinutesCount int NOT NULL,
35    OverCapacityMinuteCost money NOT NULL,
36    check (LicenseFee >= 0 and MinutesCount >= 0 and MinutesCount <= 44640 and OverCapacityMinuteCost >= 0)
37)
38 
39if object_id( 'GetCost', 'F' ) is not null  
40    drop function GetCost
41go
42 
43create function GetCost(@name varchar(100), @count float)
44returns float as
45begin
46    declare @overflow float; declare @plancost float; declare @minuteco float;
47    select @overflow = MinutesCount from tarifs where @name=name
48    select @plancost = LicenseFee   from tarifs where @name=name
49    select @minuteco = OverCapacityMinuteCost from tarifs where @name=name
50    if (@count < @overflow) return @plancost
51    return @plancost + (@count - @overflow) * @minuteco
52end
53go
54 
55if OBJECT_ID(N'getBestTarif', 'F') IS NOT NULL
56    drop function getBestTarif
57go
58 
59/*Поиск лучшего тарифа*/
60create function getBestTarif (@MinutesCount float)
61returns table
62as
63return (
64    select top 1 Name as Тариф, min(dbo.GetCost(name, @MinutesCount)) as Стоимость
65    from tarifs
66    group by Name
67    order by Стоимость asc
68)
69go
70 
71if object_id( 'getAverage', 'F' ) is not null  
72    drop function getAverage
73go
74 
75create function getAverage(@name varchar(100), @a float, @b float)
76returns float as
77begin return (dbo.GetCost(@name, @a) + dbo.GetCost(@name, @b)) / 2 end
78go
79 
80if object_id( 'getOtr', 'F' ) is not null  
81    drop function getOtr
82go
83
84create function BestTariff(@RealMinutesCount float)
85returns nvarchar(100)
86as
87	begin
88	if @RealMinutesCount > 44640 or 0 > @RealMinutesCount
89		return null
90	declare @BestCost float
91	set @BestCost = (select max(LicenseFee) from tarifs) * (select max(OverCapacityMinuteCost) from tarifs) * 44640
92	declare @NameBestTariff nvarchar(50)
93	declare @Name nvarchar(50)
94	declare @MinutesCount float
95	declare @LicenseFee float
96	declare @OverCapacityMinuteCost float
97	declare @cursor cursor
98	set @cursor = cursor scroll for select Name, MinutesCount, LicenseFee, OverCapacityMinuteCost from tarifs
99	open @cursor
100	while 1 = 1
101	begin
102		fetch next from @cursor into @Name, @MinutesCount, @LicenseFee, @OverCapacityMinuteCost
103		if @RealMinutesCount > @MinutesCount
104			begin
105				if @BestCost > (@RealMinutesCount - @MinutesCount)*@OverCapacityMinuteCost + @LicenseFee
106					begin
107						set @BestCost = (@RealMinutesCount - @MinutesCount)*@OverCapacityMinuteCost + @LicenseFee
108						set @NameBestTariff = @Name
109					end
110			end
111		else
112			begin
113				if @BestCost > @LicenseFee
114					begin
115						set @BestCost = @LicenseFee
116						set @NameBestTariff = @Name
117					end
118			end
119		if @@FETCH_STATUS != 0
120			break
121	end
122	close @cursor
123	deallocate @cursor
124	return @NameBestTariff
125	end
126go
127
128create procedure SplitSegment
129as
130	create table Points(Point float)
131	insert into Points select  distinct * from (select MinutesCount from tarifs union select LicenseFee from tarifs) as t
132	create table Segments(Start float, Finish float,Tariff nvarchar(50))
133	declare @segment_start float
134	declare @segment_end float
135	set @segment_start = (select min(Point) from Points)
136	declare @max_point float
137	set @max_point = (select max(Point) from Points)
138	while @segment_start != @max_point
139		begin
140			set @segment_end = (select min(Point) from Points where Point > @segment_start)
141			if dbo.BestTariff(@segment_start + 0.1) = dbo.BestTariff(@segment_end - 0.1)
142				begin
143					insert into Segments select @segment_start, @segment_end, dbo.BestTariff(@segment_start+0.01)
144					set @segment_start = @segment_end
145				end
146			else
147				begin
148					insert into Points select @segment_start + (@segment_end - @segment_start)/2
149				end
150		end
151	declare @some float
152	declare @tariff nvarchar(50)
153	while 1 = 1
154		begin
155		if exists(select main.Start from Segments main, Segments sub where main.Start = sub.Finish and main.Tariff = sub.Tariff)
156			begin
157				set @segment_start = (select min(main.Start) from Segments main, Segments sub where main.Finish = sub.Start and main.Tariff = sub.Tariff)
158				set @some = (select Finish from Segments where Start = @segment_start)
159				set @segment_end = (select Finish from Segments where Start = @some)
160				set @tariff = (select Tariff from Segments where Start = @segment_start)
161				delete Segments where Start = @segment_start or Finish = @segment_end
162				insert into Segments select @segment_start, @segment_end, @tariff
163			end
164		else
165			break
166		end
167	
168	drop table Points
169	select '[' +(cast(cast(Start as int) as varchar) +', '+ cast(cast(Finish as int) as varchar))+']' as Промежуток, Tariff as Тариф from Segments
170	drop table Segments
171go
172
173
174INSERT INTO tarifs
175(Name, LicenseFee, MinutesCount, OverCapacityMinuteCost)
176VALUES
177    (N'Без абонентской платы', 0, 0, 1),
178    (N'50 рублей в месяц', 50, 80, 2),
179    (N'Безлимитный', 100, 44640, 0)
180GO
181 
182select * from getBestTarif(84)
183
184exec SplitSegment