4NYvmLzz

1USE [Petrova]
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)
17GO
18
19IF EXISTS (
20    SELECT * FROM sysobjects WHERE id = object_id(N'InlineMax') 
21    AND xtype IN (N'FN', N'IF', N'TF')
22)
23    DROP FUNCTION InlineMax
24GO
25
26CREATE FUNCTION dbo.InlineMax(@val1 FLOAT, @val2 FLOAT)
27RETURNS FLOAT
28AS
29BEGIN
30	IF @val1 > @val2
31		RETURN @val1
32	RETURN @val2
33END
34GO
35
36IF EXISTS (
37    SELECT * FROM sysobjects WHERE id = object_id(N'IntersectionOfLines') 
38    AND xtype IN (N'FN', N'IF', N'TF')
39)
40    DROP FUNCTION IntersectionOfLines
41GO
42
43CREATE FUNCTION dbo.IntersectionOfLines(@k1 FLOAT, @b1 FLOAT, @k2 FLOAT, @b2 FLOAT)
44RETURNS INT
45AS
46BEGIN
47	DECLARE @eps FLOAT = 0.0001
48	DECLARE @res FLOAT = -1
49		IF abs(@k1-@k2) > @eps
50		BEGIN
51			SET @res = (@b2 - @b1)/(@k1-@k2)
52		END
53	RETURN @res
54END
55GO
56
57PRINT dbo.IntersectionOfLines(2, -14, 2, -0.25)
58
59IF EXISTS (
60    SELECT * FROM sysobjects WHERE id = object_id(N'MostAdvantageous') 
61    AND xtype IN (N'FN', N'IF', N'TF')
62)
63    DROP FUNCTION MostAdvantageous
64GO
65
66CREATE FUNCTION MostAdvantageous(@minutes INT) 
67RETURNS VARCHAR(50)
68AS
69BEGIN
70	IF @minutes <= 0
71	BEGIN
72		RETURN 0
73	END
74	DECLARE @TariffId INT = (
75			SELECT TOP 1 Tariffs.id
76			FROM Tariffs
77			ORDER BY Tariffs.licenseFee + dbo.InlineMax(0, @minutes - Tariffs.limit) * Tariffs.feeOver
78							)
79	DECLARE @TariffName VARCHAR(50) = (
80			SELECT TOP 1 Tariffs.name
81			FROM Tariffs
82			WHERE Tariffs.id = @TariffId
83									)
84	RETURN @TariffName
85END	
86GO
87
88PRINT dbo.MostAdvantageous(650)
89
90IF object_id('AllX', 'U') IS NOT NULL
91	DROP TABLE AllX;
92IF object_id('recurringMins', 'U') IS NOT NULL
93	DROP TABLE recurringMins;
94IF object_id('partion', 'U') IS NOT NULL
95	DROP TABLE partion;
96GO
97
98SELECT 
99	dbo.IntersectionOfLines(
100		0, tariff1.licenseFee, tariff2.feeOver,
101		-(tariff2.limit - tariff2.licenseFee/dbo.InlineMax(tariff2.feeOver, 0.0001))*tariff2.feeOver) as intersection1,
102	dbo.IntersectionOfLines(
103		0, tariff2.licenseFee, tariff1.feeOver, 
104		-(tariff1.limit - tariff1.licenseFee/dbo.InlineMax(tariff1.feeOver, 0.0001))*tariff1.feeOver) as intersection2,
105	dbo.IntersectionOfLines(
106		tariff1.feeOver, -(tariff1.limit - tariff1.licenseFee/dbo.InlineMax(tariff1.feeOver, 0.0001))*tariff1.feeOver,
107		tariff2.feeOver, -(tariff2.limit - tariff2.licenseFee/dbo.InlineMax(tariff2.feeOver, 0.0001))*tariff2.feeOver) as intersection3
108INTO AllX
109FROM Tariffs AS tariff1
110CROSS JOIN Tariffs AS tariff2
111
112
113SELECT intersection1 AS 'Минимум' INTO recurringMins FROM AllX
114WHERE intersection1 > 0
115UNION
116SELECT intersection2 FROM AllX
117WHERE intersection2 > 0
118UNION
119SELECT intersection3 FROM AllX
120WHERE intersection3 > 0
121
122SELECT *
123INTO partion
124FROM
125(
126	SELECT [Минимум], dbo.MostAdvantageous([Минимум]-1) AS 'Меньше', dbo.MostAdvantageous([Минимум]) AS 'Ровно', dbo.MostAdvantageous([Минимум]+1) AS 'Больше'
127	FROM recurringMins
128) p
129WHERE p.[Меньше] != p.[Ровно] OR p.[Ровно] != p.[Больше]
130
131SELECT * FROM partion
132
133DECLARE @left VARCHAR(50) = ''
134DECLARE @right VARCHAR(50) = ''
135DECLARE @tar VARCHAR(50) = ''
136DECLARE @last VARCHAR(50) = ''
137DECLARE @beautifulPartion TABLE (
138	[Тариф] VARCHAR(50) NOT NULL,
139	[Левая граница] VARCHAR(50) NOT NULL,
140	[Правая граница] VARCHAR(50) NOT NULL
141);
142
143DECLARE x CURSOR FOR 
144SELECT [Минимум], dbo.MostAdvantageous([Минимум]-1), dbo.MostAdvantageous([Минимум]+1) from partion
145OPEN x
146
147FETCH NEXT FROM x INTO  @right, @tar, @last
148	
149	INSERT INTO @beautifulPartion VALUES
150	(@tar, '0.0', @right)
151	SET @left = @right
152
153WHILE @@FETCH_STATUS = 0
154BEGIN
155	FETCH NEXT FROM x INTO  @right, @tar, @last
156	
157	IF @left != @right
158		INSERT INTO @beautifulPartion VALUES
159		(@tar, @left, @right)
160	SET @left = @right	
161END
162
163INSERT INTO @beautifulPartion VALUES
164(@last, @left, '10000')
165
166SELECT * 
167FROM @beautifulPartion
168
169CLOSE x
170DEALLOCATE x