MBPQrCGp

1use [Stroeva]
2
3if object_id('Tariffs', 'U') IS NOT NULL
4	drop table Tariffs;
5GO
6create table Tariffs(
7	id int primary key not null,
8	name varchar(50) not null,
9	licenseFee float not null,
10	limit float not null,
11	feeOver float not null
12);
13insert Tariffs values
14	(1, 'Тариф1', 0, 0, 0.5),
15	(2, 'Тариф2', 2, 6, 1),
16	(3, 'Тариф3', 6, 10000, 0)
17	--(4, '', 10, 5, 2)
18GO
19
20
21--------------------------------------------------------
22
23IF EXISTS (
24    SELECT * FROM sysobjects WHERE id = object_id(N'InlineMax') 
25    AND xtype IN (N'FN', N'IF', N'TF')
26)
27    DROP FUNCTION InlineMax
28GO
29
30create function dbo.InlineMax(@val1 float, @val2 float)
31returns float
32as
33begin
34	if @val1 > @val2
35		return @val1
36	return @val2
37end
38GO
39
40---
41
42IF EXISTS (
43    SELECT * FROM sysobjects WHERE id = object_id(N'IntersectionOfLines') 
44    AND xtype IN (N'FN', N'IF', N'TF')
45)
46    DROP FUNCTION IntersectionOfLines
47GO
48
49create function dbo.IntersectionOfLines(@k1 float, @b1 float, @k2 float, @b2 float)
50returns int
51as
52begin
53	declare @eps float = 0.0001
54	declare @res float = -1
55
56		if abs(@k1-@k2) > @eps
57		begin
58			set @res = (@b2 - @b1)/(@k1-@k2)
59		end
60	return @res
61end
62GO
63
64print dbo.IntersectionOfLines(2, -14, 2, -0.25)
65
66---
67
68IF EXISTS (
69    SELECT * FROM sysobjects WHERE id = object_id(N'MostAdvantageous') 
70    AND xtype IN (N'FN', N'IF', N'TF')
71)
72    DROP FUNCTION MostAdvantageous
73GO
74
75create function MostAdvantageous(@minutes int) 
76returns varchar(50)
77as
78begin
79	if @minutes <= 0
80	begin
81		return 0
82	end
83	declare @TariffId int = (
84								select top 1 Tariffs.id
85								from Tariffs
86								order by Tariffs.licenseFee + dbo.InlineMax(0, @minutes - Tariffs.limit) * Tariffs.feeOver
87							)
88	declare @TariffName varchar(50) = (
89										select top 1 Tariffs.name
90										from Tariffs
91										where Tariffs.id = @TariffId
92									)
93	return @TariffName
94end	
95GO
96
97print dbo.MostAdvantageous(650)
98
99----------------------------------------------
100
101
102if object_id('AllX', 'U') IS NOT NULL
103	drop table AllX;
104if object_id('recurringMins', 'U') IS NOT NULL
105	drop table recurringMins;
106if object_id('partion', 'U') IS NOT NULL
107	drop table partion;
108GO
109
110select 
111	dbo.IntersectionOfLines(
112		0, tariff1.licenseFee, tariff2.feeOver,
113		-(tariff2.limit - tariff2.licenseFee/dbo.InlineMax(tariff2.feeOver, 0.0001))*tariff2.feeOver) as intersection1,
114	dbo.IntersectionOfLines(
115		0, tariff2.licenseFee, tariff1.feeOver, 
116		-(tariff1.limit - tariff1.licenseFee/dbo.InlineMax(tariff1.feeOver, 0.0001))*tariff1.feeOver) as intersection2,
117	dbo.IntersectionOfLines(
118		tariff1.feeOver, -(tariff1.limit - tariff1.licenseFee/dbo.InlineMax(tariff1.feeOver, 0.0001))*tariff1.feeOver,
119		tariff2.feeOver, -(tariff2.limit - tariff2.licenseFee/dbo.InlineMax(tariff2.feeOver, 0.0001))*tariff2.feeOver) as intersection3
120into AllX
121from Tariffs as tariff1
122cross join Tariffs as tariff2
123
124
125select intersection1 as 'Минимум' into recurringMins from AllX
126where intersection1 > 0
127union
128select intersection2 from AllX
129where intersection2 > 0
130union
131select intersection3 from AllX
132where intersection3 > 0
133
134
135select *
136into partion
137from
138(
139	select [Минимум], dbo.MostAdvantageous([Минимум]-1) as 'Меньше', dbo.MostAdvantageous([Минимум]) as 'Ровно', dbo.MostAdvantageous([Минимум]+1) as 'Больше'
140	from recurringMins
141) p
142where p.[Меньше] != p.[Ровно] or p.[Ровно] != p.[Больше]
143
144select * from partion
145
146declare @left varchar(50) = ''
147declare @right varchar(50) = ''
148declare @tar varchar(50) = ''
149declare @last varchar(50) = ''
150declare @beautifulPartion table (
151	[Тариф] varchar(50) not null,
152	[Левая граница] varchar(50) not null,
153	[Правая граница] varchar(50) not null
154);
155
156declare x cursor for 
157select [Минимум], dbo.MostAdvantageous([Минимум]-1), dbo.MostAdvantageous([Минимум]+1) from partion
158open x
159
160fetch next from x into  @right, @tar, @last
161	
162	insert into @beautifulPartion values
163	(@tar, '0.0', @right)
164	set @left = @right
165
166while @@FETCH_STATUS = 0
167begin
168	fetch next from x into  @right, @tar, @last
169	
170	if @left != @right
171		insert into @beautifulPartion values
172		(@tar, @left, @right)
173	set @left = @right
174	
175end
176
177insert into @beautifulPartion values
178(@last, @left, '10000')
179
180select * 
181from @beautifulPartion
182
183close x
184deallocate x