0GaysFyt

1use master
2GO
3 
4IF EXISTS (
5SELECT name
6FROM sys.databases
7WHERE name = N'[Valeria_Karandina]'
8)
9ALTER DATABASE Valeria_Karandina set single_user with rollback immediate
10GO
11 
12IF EXISTS (
13SELECT name
14FROM sys.databases
15WHERE name = N'Valeria_Karandina'
16)
17DROP DATABASE Valeria_Karandina
18GO
19 
20IF OBJECT_ID('Valeria_Karandina') IS NOT NULL
21DROP DATABASE Valeria_Karandina
22GO
23 
24CREATE DATABASE Valeria_Karandina
25GO
26 
27USE Valeria_Karandina
28GO
29
30CREATE TABLE Plans (
31plan_name nvarchar(50) NOT NULL,
32mounthCost float,
33minutesCount float,
34overflowCost money,
35CHECK (mounthCost >= 0 AND minutesCount >= 0 AND overflowCost >= 0)
36)
37GO
38
39INSERT INTO Plans(name, mounthCost, minutesCount, overflowCost) values
40	(N'10 бесплатных минут',	0,	10, 1),
41	(N'Все', 5,	20, 1),
42	(N'Безлит',			10,	0,	0),
43	(N'Поминутный',		0,	0,	2)
44GO
45
46
47CREATE PROCEDURE GetCost(@name varchar(100), @count float)
48AS
49BEGIN
50	DECLARE @overflow float;
51	DECLARE @plancost float; 
52	DECLARE @minuteco float;
53    SELECT @overflow = minutesCount FROM Plans WHERE @name=name
54	SELECT @plancost = mounthCost	FROM Plans WHERE @name=name
55	SELECT @minuteco = overflowCost FROM Plans WHERE @name=name
56	IF (@count < @overflow) RETURN @plancost
57	RETURN @plancost + (@count - @overflow) * @minuteco
58END
59GO
60
61CREATE PROCEDURE Median(@name varchar(100), @a float, @b float)
62AS
63BEGIN
64RETURN (dbo.GetCost(@name, @a) + dbo.GetCost(@name, @b)) / 2
65END
66GO
67
68CREATE PROCEDURE Advanteges()
69AS
70BEGIN
71	DECLARE @previous float = 0;
72	DECLARE @current float = 0;
73	DECLARE interval_cursor cursor for  
74	SELECT DISTINCT	b.minutesCount + a.mounthCost / b.overflowCost 
75	FROM Plans a, Plans b
76	WHERE b.overflowCost != 0 AND b.minutesCount + a.mounthCost / b.overflowCost > 0
77	ORDER BY b.minutesCount + a.mounthCost / b.overflowCost;
78	OPEN interval_cursor
79	FETCH NEXT FROM interval_cursor   
80	INTO @current
81	WHILE @@fetch_status = 0  
82	BEGIN
83		DECLARE @min float = 0; 
84		DECLARE @minName varchar(100);
85		SELECT @min = min(dbo.Median(name, @previous, @current)) FROM Plans
86		SELECT @minName = name FROM Plans WHERE @min = dbo.Median(name, @previous, @current)
87		print(CONVERT(varchar(100), @previous, 3) 
88				+ ' -> ' 
89				+  CONVERT(varchar(100), @current, 3)
90				+ ' - ' + @minName
91		)
92		SELECT @previous = @current;
93		FETCH NEXT FROM interval_cursor   
94		INTO @current  
95	END
96	CLOSE interval_cursor;  
97	DEALLOCATE interval_cursor;
98END
99GO