· 9 years ago · Dec 25, 2016, 07:22 PM
1USE master
2GO
3
4IF EXISTS (
5 SELECT name
6 FROM sys.databases
7 WHERE name = N'Uliana_Kvashnina_2'
8)
9ALTER DATABASE [Uliana_Kvashnina_2] set single_user with rollback immediate
10GO
11
12IF EXISTS (
13 SELECT name
14 FROM sys.databases
15 WHERE name = N'Uliana_Kvashnina_2'
16)
17DROP DATABASE Uliana_Kvashnina_2
18GO
19
20CREATE DATABASE Uliana_Kvashnina_2
21GO
22
23USE Uliana_Kvashnina_2
24GO
25
26CREATE TABLE rates
27(
28 id int,
29 paid_minutes int NOT NULL,
30 monthly_payment float NOT NULL,
31 over_included float NOT NULL,
32 CONSTRAINT PK_rate_id PRIMARY KEY (id),
33 CONSTRAINT rate_check CHECK (((paid_minutes = 0 AND monthly_payment = 0) OR
34 (paid_minutes > 0 AND monthly_payment > 0)) AND over_included >= 0)
35)
36GO
37
38CREATE FUNCTION GetBestrate(@minutes int)
39RETURNS int
40BEGIN
41 DECLARE @min_payment float = 1000000000
42 DECLARE @id_min_interval int
43 DECLARE @id int
44 DECLARE @paid_min int
45 DECLARE @monthly_pay float
46 DECLARE @over_inc float
47 DECLARE @cursor CURSOR
48 SET @cursor = CURSOR SCROLL FOR
49 SELECT id, paid_minutes, monthly_payment, over_included FROM rates
50 OPEN @cursor
51 FETCH NEXT FROM @cursor INTO @id, @paid_min, @monthly_pay, @over_inc
52 WHILE @@FETCH_STATUS = 0
53 BEGIN
54 DECLARE @result float
55 SET @result = @monthly_pay
56 IF @paid_min < @minutes
57 SET @result = @result + (@minutes - @paid_min) * @over_inc
58
59 IF @result < @min_payment
60 BEGIN
61 SET @min_payment = @result
62 SET @id_min_interval = @id
63 END
64
65 FETCH NEXT FROM @cursor INTO @id, @paid_min, @monthly_pay, @over_inc
66 END
67 CLOSE @cursor
68 RETURN @id_min_interval
69END
70GO
71
72INSERT rates(id, paid_minutes, monthly_payment, over_included) VALUES
73 (1, 100, 50, 3),
74 (2, 0, 0, 10),
75 (3, 200, 150, 1),
76 (4, 1000000, 10, 0),
77 (5, 10, 25, 5)
78GO
79
80DECLARE @intervals TABLE
81(
82 _begin float,
83 _end float,
84 id_rate int
85)
86
87DECLARE @currentInterval int
88IF EXISTS (SELECT * FROM rates WHERE monthly_payment = 0)
89 SET @currentInterval = (SELECT TOP 1 id FROM rates WHERE monthly_payment = 0 ORDER BY over_included ASC)
90ELSE
91 SET @currentInterval = (SELECT TOP 1 id FROM rates ORDER BY monthly_payment ASC)
92
93DECLARE @currentIntervalStart float = 0
94WHILE 1 = 1
95BEGIN
96 DECLARE @over_time float = (SELECT over_included FROM rates WHERE id = @currentInterval)
97 DECLARE @payment int = (SELECT monthly_payment FROM rates WHERE id = @currentInterval)
98 DECLARE @paid int = (SELECT paid_minutes FROM rates WHERE id = @currentInterval)
99 DECLARE @nextInterval int
100 DECLARE @nextIntervalStart float = 1000000
101 DECLARE @id int
102 DECLARE @paid_min int
103 DECLARE @monthly_pay float
104 DECLARE @over_inc float
105 DECLARE @cursor CURSOR
106 SET @cursor = CURSOR SCROLL FOR
107 SELECT id, paid_minutes, monthly_payment, over_included FROM rates WHERE id != @currentInterval
108 OPEN @cursor
109 FETCH NEXT FROM @cursor INTO @id, @paid_min, @monthly_pay, @over_inc
110 WHILE @@FETCH_STATUS = 0
111 BEGIN
112 IF @over_time != 0
113 BEGIN
114 DECLARE @intervalStart float = (-@payment + @over_time * @paid+ @monthly_pay) / @over_time
115 IF @intervalStart < @nextIntervalStart AND
116 @intervalStart > @currentIntervalStart AND
117 @intervalStart > @paid
118 BEGIN
119 SET @nextIntervalStart = @intervalStart
120 SET @nextInterval = @id
121 END
122 END
123 IF @over_time - @over_inc != 0
124 BEGIN
125 SET @intervalStart = (-@over_inc * @paid_min + @monthly_pay + @over_time * @paid- @payment) / (@over_time - @over_inc)
126 IF @intervalStart < @nextIntervalStart AND
127 @intervalStart > @currentIntervalStart AND
128 @intervalStart > @paid
129 BEGIN
130 SET @nextIntervalStart = @intervalStart
131 SET @nextInterval = @id
132 END
133 END
134 FETCH NEXT FROM @cursor INTO @id, @paid_min, @monthly_pay, @over_inc
135 END
136 CLOSE @cursor
137 INSERT @intervals(_begin, _end, id_rate) VALUES (@currentIntervalStart, @nextIntervalStart, @currentInterval)
138 IF isnull(@nextInterval, @currentInterval) = @currentInterval
139 BREAK
140 SET @currentIntervalStart = @nextIntervalStart
141 SET @currentInterval = @nextInterval
142END
143
144SELECT * FROM rates
145SELECT * FROM @intervals
146PRINT(dbo.GetBestrate(5))
147GO