0P311eY0

1USE master
2
3--if EXISTS (
4--	SELECT name
5--		FROM sys.databases
6--		WHERE name = N'Melentyeva'
7--)
8--DROP DATABASE [Melentyeva]
9--GO
10
11--CREATE DATABASE [Melentyeva]
12--GO
13
14USE [Melentyeva]
15GO
16
17CREATE TABLE Tariffs(
18	id INT PRIMARY KEY NOT NULL,
19	name VARCHAR(50) NOT NULL,
20	licenseFee FLOAT NOT NULL,
21	limit FLOAT NOT NULL,
22	feeOver FLOAT NOT NULL
23)
24GO
25INSERT Tariffs VALUES
26	(1, 'Тариф1', 0, 0, 0.5),
27	(2, 'Тариф2', 2, 6, 1),
28	(3, 'Тариф3', 7, 560, 0)
29GO
30
31IF EXISTS (
32    SELECT * FROM sysobjects WHERE id = object_id(N'InlineMax') 
33    AND xtype IN (N'FN', N'IF', N'TF')
34)
35    DROP FUNCTION InlineMax
36GO
37
38CREATE FUNCTION dbo.InlineMax(@val1 FLOAT, @val2 FLOAT)
39RETURNS FLOAT
40AS
41BEGIN
42	IF @val1 > @val2
43		RETURN @val1
44	RETURN @val2
45END
46GO
47
48IF EXISTS (
49    SELECT * FROM sysobjects WHERE id = object_id(N'IntersectionOfLines') 
50    AND xtype IN (N'FN', N'IF', N'TF')
51)
52    DROP FUNCTION IntersectionOfLines
53GO
54
55CREATE FUNCTION dbo.IntersectionOfLines(@k1 FLOAT, @b1 FLOAT, @k2 FLOAT, @b2 FLOAT)
56RETURNS INT
57AS
58BEGIN
59	DECLARE @eps FLOAT = 0.0001
60	DECLARE @res FLOAT = -1
61		IF abs(@k1-@k2) > @eps
62		BEGIN
63			SET @res = (@b2 - @b1)/(@k1-@k2)
64		END
65	RETURN @res
66END
67GO
68
69PRINT dbo.IntersectionOfLines(1, 4, 2, 7)
70
71IF EXISTS (
72    SELECT * FROM sysobjects WHERE id = object_id(N'MostAdvantageous') 
73    AND xtype IN (N'FN', N'IF', N'TF')
74)
75    DROP FUNCTION MostAdvantageous
76GO
77
78CREATE FUNCTION MostAdvantageous(@minutes INT) 
79RETURNS VARCHAR(50)
80AS
81BEGIN
82	IF @minutes <= 0
83	BEGIN
84		RETURN 0
85	END
86	DECLARE @TariffId INT = (
87			SELECT TOP 1 Tariffs.id
88			FROM Tariffs
89			ORDER BY Tariffs.licenseFee + dbo.InlineMax(0, @minutes - Tariffs.limit) * Tariffs.feeOver
90							)
91	DECLARE @TariffName VARCHAR(50) = (
92			SELECT TOP 1 Tariffs.name
93			FROM Tariffs
94			WHERE Tariffs.id = @TariffId
95									)
96	RETURN @TariffName
97END	
98GO
99
100PRINT dbo.MostAdvantageous(16)
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
134SELECT *
135INTO partion
136FROM
137(
138	SELECT [Минимум], dbo.MostAdvantageous([Минимум]-1) AS 'Меньше', dbo.MostAdvantageous([Минимум]) AS 'Ровно', dbo.MostAdvantageous([Минимум]+1) AS 'Больше'
139	FROM recurringMins
140) p
141WHERE p.[Меньше] != p.[Ровно] OR p.[Ровно] != p.[Больше]
142
143SELECT * FROM partion
144
145DECLARE @left VARCHAR(50) = ''
146DECLARE @right VARCHAR(50) = ''
147DECLARE @tar VARCHAR(50) = ''
148DECLARE @last VARCHAR(50) = ''
149DECLARE @beautifulPartion TABLE (
150	[Тариф] VARCHAR(50) NOT NULL,
151	[Левая граница] VARCHAR(50) NOT NULL,
152	[Правая граница] VARCHAR(50) NOT NULL
153);
154
155DECLARE x CURSOR FOR 
156SELECT [Минимум], dbo.MostAdvantageous([Минимум]-1), dbo.MostAdvantageous([Минимум]+1) from partion
157OPEN x
158
159FETCH NEXT FROM x INTO  @right, @tar, @last
160	
161	INSERT INTO @beautifulPartion VALUES
162	(@tar, '0.0', @right)
163	SET @left = @right
164
165WHILE @@FETCH_STATUS = 0
166BEGIN
167	FETCH NEXT FROM x INTO  @right, @tar, @last
168	
169	IF @left != @right
170		INSERT INTO @beautifulPartion VALUES
171		(@tar, @left, @right)
172	SET @left = @right	
173END
174
175INSERT INTO @beautifulPartion VALUES
176(@last, @left, '560')
177
178SELECT * 
179FROM @beautifulPartion
180
181CLOSE x
182DEALLOCATE x