F5hAhnBn

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', 'FN') IS NOT NULL
56	drop function getBestTarif
57go
58
59/*Поиск лучшего тарифа*/
60create function getBestTarif (@MinutesCount int)
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( 'Median', 'F' ) is not null   
72    drop function Median
73go
74
75create function Median(@name varchar(100), @a float, @b float)
76returns float as
77begin return (dbo.GetCost(@name, @a) + dbo.GetCost(@name, @b)) / 2 end
78go
79
80INSERT INTO tarifs
81(Name, LicenseFee, MinutesCount, OverCapacityMinuteCost)
82VALUES
83	(N'Без абонентской платы', 0, 0, 1), 
84	(N'50 рублей в месяц', 50, 80, 2), 
85	(N'Безлимитный', 100, 44640, 0)
86GO
87
88select * from getBestTarif(84)
89
90if object_id( 'getOtr', 'F' ) is not null   
91    drop function getOtr
92go
93
94create function getOtr()
95returns @table table
96	(
97		Name nvarchar(100) NOT NULL,
98		IntervalStart float NOT NULL,
99		IntervalEnd float NOT NULL
100	)
101as
102begin
103	declare @previous float = 0;
104	declare @current float = 0;
105	declare interval_cursor cursor for  
106	select distinct b.MinutesCount + a.LicenseFee / b.OverCapacityMinuteCost 
107	from tarifs a, tarifs b
108	where b.OverCapacityMinuteCost != 0 and b.MinutesCount + a.LicenseFee / b.OverCapacityMinuteCost > 0
109	order by b.MinutesCount + a.LicenseFee / b.OverCapacityMinuteCost;
110
111	open interval_cursor
112	fetch next from interval_cursor   
113	into @current
114
115	while @@fetch_status = 0  
116	begin
117		declare @min float = 0;
118		declare @minName varchar(100);
119		select @min = min(dbo.Median(name, @previous, @current)) from tarifs
120		select @minName = name from tarifs where @min = dbo.Median(name, @previous, @current)
121		insert @table values 
122		(@minName, @previous, @current)
123		select @previous = @current;
124		fetch next from interval_cursor   
125		into @current  
126	end
127
128	close interval_cursor;  
129	deallocate interval_cursor
130return
131end
132go
133
134select (cast(IntervalStart as varchar)+ '-' + cast(IntervalEnd as varchar)) as Промежуток, Name as Тариф from getOtr()