SHARE
TWEET

4task

Arzybek Dec 24th, 2019 248 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. USE KN_301_Kazikulov
  2. go
  3. DROP SYNONYM Currencies
  4. go
  5. DROP PROCEDURE ShowCourses;
  6. GO
  7. DROP TABLE KN_301_Kazikulov.Kazikulov.Abonents
  8. GO
  9. DROP SYNONYM Abonents
  10. DROP TABLE KN_301_Kazikulov.Kazikulov.Books
  11. DROP SYNONYM Books
  12. DROP TABLE KN_301_Kazikulov.Kazikulov.Biblio;
  13. DROP SYNONYM Biblio
  14. DROP TABLE  KN_301_Kazikulov.Kazikulov.Records
  15. DROP SYNONYM Records
  16. DROP TRIGGER Kazikulov.Records_Insert;
  17. go
  18. DROP TABLE  KN_301_Kazikulov.Kazikulov.Orders
  19. DROP SYNONYM Orders
  20. DROP FUNCtion GetAllPersonsBooks
  21. go
  22. DROP FUNCtion GetAllPersonsBooks
  23. go
  24. DROP Procedure ReturnBook
  25. go
  26. DROP Procedure GetMostReadPerson
  27. go
  28. DROP Procedure GetMostBook
  29. go
  30. DROP Procedure GetMostOrderedBook
  31. go
  32. DROP Procedure GetMostReadedBook
  33. go
  34.  
  35. DROP TABLE [KN_301_Kazikulov].Kazikulov.Tariff
  36. GO
  37. CREATE TABLE [KN_301_Kazikulov].Kazikulov.Tariff
  38. (
  39.     Id tinyint identity NOT NULL,
  40.     Name nvarchar(100) NOT NULL,
  41.     Payment money NOT NULL,
  42.     Minutes_Amount float NOT NULL,
  43.     Minute_Cost money NOT NULL
  44.     CONSTRAINT PK_Tariff PRIMARY KEY (ID)
  45. )
  46. GO
  47.  
  48. CREATE SYNONYM Tariff FOR [KN_301_Kazikulov].Kazikulov.Tariff;
  49.  
  50. DELETE FROM Tariff;
  51. INSERT INTO [KN_301_Kazikulov].Kazikulov.Tariff VALUES
  52. (N'Поминутный', 0, 0, 0.5),
  53. (N'Безлимитный', 12500, 44640, 0),
  54. (N'Смешанный', 2999, 12000, 1)
  55. GO
  56. --INSERT INTO [KN_301_Kazikulov].Kazikulov.Tariff VALUES
  57. --(N'Поминутный', 0, 0, 0.5),
  58. --(N'Смешанный', 2, 6, 1),
  59. --(N'Безлимитный', 5, 44640, 0)
  60. --GO
  61.  
  62.  
  63. DROP FUNCTION dbo.Best_Tariff
  64. GO
  65. CREATE FUNCTION Best_Tariff(@minutes_IN float)
  66. RETURNS nvarchar(100)
  67. AS
  68. BEGIN
  69.     IF @minutes_IN < 0
  70.     BEGIN
  71.         return 'Minutes should be positive number';
  72.     END
  73.     IF @minutes_IN > 44640
  74.     BEGIN
  75.         return 'Minutes are bigger than minutes in one month';
  76.     END
  77.  
  78.     DECLARE @name nvarchar(100), @pay money, @minutes float, @cost money
  79.     DECLARE @result nvarchar(100), @current float, @last float
  80.     SET @last = 0
  81.     SET @current = 0
  82.  
  83.     DECLARE @cursor CURSOR
  84.     SET @cursor =  CURSOR FOR SELECT Name, Payment, Minutes_Amount, Minute_Cost FROM [KN_301_Kazikulov].Kazikulov.Tariff
  85.  
  86.     OPEN @cursor
  87.     FETCH NEXT FROM @cursor INTO @name, @pay, @minutes, @cost
  88.     WHILE @@FETCH_STATUS = 0
  89.     BEGIN
  90.             IF @minutes_IN <= @minutes
  91.             BEGIN
  92.                 SET @current = @pay    
  93.             END
  94.             ELSE
  95.             BEGIN
  96.                 SET @current = @pay + (@minutes_IN - @minutes) * @cost
  97.             END
  98.  
  99.             IF @last = 0 OR @current < @last
  100.             BEGIN
  101.                 SET @last = @current
  102.                 SET @result = @name
  103.             END
  104.             FETCH NEXT FROM @cursor INTO @name, @pay, @minutes, @cost
  105.     END
  106.     CLOSE @cursor
  107.     DEALLOCATE @cursor
  108.     RETURN @result
  109. END
  110. GO
  111.  
  112. print(dbo.Best_Tariff(1000));   -- поминутный
  113.  
  114. print(dbo.Best_Tariff(6000));
  115. print(dbo.Best_Tariff(12000));
  116. print(dbo.Best_Tariff(18000));  -- смешанный
  117.  
  118. print(dbo.Best_Tariff(18002));
  119. print(dbo.Best_Tariff(24999));  -- поминутный
  120.  
  121. print(dbo.Best_Tariff(25001));   -- безлимитный
  122. print(dbo.Best_Tariff(40000));
  123.  
  124. print(dbo.Best_Tariff(46650));   -- ошибки
  125. print(dbo.Best_Tariff(-1));
  126.  
  127. DROP PROCEDURE BestTariffForSegments
  128. GO
  129. CREATE PROCEDURE BestTariffForSegments
  130. AS
  131. BEGIN
  132.     DECLARE @max int
  133.     CREATE TABLE #table
  134.     (
  135.         Point float
  136.     )
  137.     INSERT INTO #table VALUES
  138.      (0), (44640)
  139.  
  140.     INSERT INTO #table SELECT MAX(Minutes_Amount) FROM [KN_301_Kazikulov].Kazikulov.Tariff;
  141.     SET @max = 44640
  142.  
  143.     ----Пересечение безлимитного A с поминутным B
  144.     --INSERT INTO #table
  145.     --SELECT
  146.     ----A.Name, B.Name
  147.     --(A.Payment-B.Payment)/B.Minute_Cost
  148.     --FROM [KN_301_Kazikulov].Kazikulov.Tariff as A, [KN_301_Kazikulov].Kazikulov.Tariff as B
  149.     --WHERE A.Id<>B.Id AND B.Minute_Cost <> 0 AND (A.Payment- B.Payment)/B.Minute_Cost > 0
  150.  
  151.     --Пересечение безлимитного A с поминутным B, смешанного A с поминутным B, Безлимитного A со смешанным B
  152.     INSERT INTO #table
  153.     SELECT
  154.     --A.Name, B.Name,
  155.     ((A.Payment-B.Payment)/B.Minute_Cost + B.Minutes_Amount)
  156.     --,B.Minutes_Amount
  157.     FROM [KN_301_Kazikulov].Kazikulov.Tariff as A, [KN_301_Kazikulov].Kazikulov.Tariff as B
  158.     WHERE A.Id<>B.Id AND B.Minute_Cost <> 0 AND (A.Payment - B.Payment)/B.Minute_Cost > 0
  159.  
  160.     --пересечение поминутного A с смешанным B
  161.     INSERT INTO #table
  162.     SELECT
  163.     --A.Name, B.Name,
  164.     (B.Payment - A.Payment - B.Minutes_Amount*B.Minute_Cost + A.Minutes_Amount*A.Minute_Cost)/(A.Minute_Cost - B.Minute_Cost)
  165.     --, B.Payment, B.Minutes_Amount, B.Minute_Cost
  166.     FROM [KN_301_Kazikulov].Kazikulov.Tariff as A, [KN_301_Kazikulov].Kazikulov.Tariff as B
  167.     WHERE A.Id<>B.Id AND (B.Minutes_Amount - A.Minutes_Amount) > 0 AND (A.Minute_Cost - B.Minute_Cost) <> 0 AND
  168.     (B.Payment - A.Payment - B.Minutes_Amount*B.Minute_Cost + A.Minutes_Amount*A.Minute_Cost)/(A.Minute_Cost - B.Minute_Cost) > 0
  169.    
  170.     UPDATE #table SET Point=ROUND(Point,0)
  171.     DELETE FROM #table WHERE
  172.                     Point != 0
  173.                     AND Point != @max
  174.                     AND  dbo.best_tariff(Point-1) = dbo.best_tariff(Point+1)
  175.                     AND  dbo.best_tariff(Point) = dbo.best_tariff(Point+1)
  176.  
  177.     CREATE TABLE #result (
  178.         left_point int,
  179.         right_point int,
  180.         tariff nvarchar(40)
  181.     )
  182.  
  183.     DECLARE @cursor CURSOR, @left int, @right int
  184.     SET @cursor = CURSOR FOR SELECT DISTINCT * FROM #table ORDER BY Point ASC
  185.     OPEN @cursor
  186.     FETCH NEXT FROM @cursor INTO @left
  187.     FETCH NEXT FROM @cursor INTO @right
  188.     WHILE @@FETCH_STATUS = 0
  189.     BEGIN
  190.         INSERT INTO #result VALUES (@left, @right, dbo.best_tariff((@left+@right)/2))
  191.         SET @left=@right
  192.         FETCH NEXT FROM @cursor INTO @right
  193.     END
  194.     SELECT CONCAT('[', left_point, ';', right_point, ']') as Отрезок,           
  195.            tariff as [Тариф]
  196.            FROM #result
  197. END
  198. GO
  199.  
  200. EXEC BestTariffForSegments
  201. go
  202.  
  203. --DROP PROCEDURE TimeLimits
  204. --GO
  205. --CREATE PROC Timelimits AS
  206. --begin
  207. --  DECLARE
  208. --      @counter int = 0,
  209.  
  210. --      @id1 int,
  211. --      @f_subscription int,
  212. --      @f_minutes_limit int,
  213. --      @f_pay_over_limit float,
  214.  
  215. --      @id2 int,
  216. --      @s_subscription int,
  217. --      @s_minutes_limit int,
  218. --      @s_pay_over_limit float,
  219.  
  220. --      @f_crossing_point float,
  221. --      @s_crossing_point float,
  222.  
  223. --      @f_coord float,
  224. --      @s_coord float
  225.    
  226. --      CREATE TABLE #Crossing_points(
  227. --          p_id int,
  228. --          coords float
  229. --      )
  230.        
  231. --      CREATE TABLE #Limits(
  232. --          fromtime float,
  233. --          totime float,
  234. --          tarif nvarchar(100)
  235. --      )
  236.  
  237. --      INSERT INTO #Crossing_points
  238. --      VALUES (
  239. --          @counter, 0
  240. --      )
  241. --      SET @counter +=1
  242.  
  243. --      DECLARE cur1 CURSOR FOR SELECT Id, Payment, Minutes_Amount, Minute_Cost from Kazikulov.Tariff ORDER By Payment
  244.        
  245. --      OPEN cur1
  246. --      FETCH NEXT FROM cur1 INTO @id1, @f_subscription, @f_minutes_limit, @f_pay_over_limit
  247. --      WHILE @@FETCH_STATUS = 0
  248. --          BEGIN
  249. --              DECLARE cur2 CURSOR FOR
  250. --              SELECT id, Payment, Minutes_Amount, Minute_Cost FROM Kazikulov.Tariff
  251. --              WHERE Payment> @f_subscription
  252. --              ORDER BY Payment
  253. --              OPEN cur2
  254. --              FETCH NEXT FROM cur2 INTO @id2, @s_subscription, @s_minutes_limit, @s_pay_over_limit
  255. --              WHILE @@FETCH_STATUS = 0
  256. --                  BEGIN
  257. --                      SET @f_crossing_point = (@s_subscription - @f_subscription) / @f_pay_over_limit + @f_minutes_limit
  258. --                      SET @s_crossing_point = (@f_subscription - @s_subscription + @s_minutes_limit * @s_pay_over_limit - @f_minutes_limit * @f_pay_over_limit) / (@s_pay_over_limit - @f_pay_over_limit)
  259. --                      IF(@s_crossing_point > @s_minutes_limit)
  260. --                          BEGIN
  261. --                              INSERT INTO #Crossing_points
  262. --                              VALUES ( @counter, CEILING(@s_crossing_point))
  263. --                              SET @counter +=1
  264. --                          END
  265. --                      IF(@f_crossing_point < @s_minutes_limit)
  266. --                          BEGIN
  267. --                              INSERT INTO #Crossing_points
  268. --                              VALUES ( @counter, CEILING(@f_crossing_point) )
  269. --                              SET @counter +=1
  270. --                          END
  271. --                      FETCH NEXT FROM cur2 INTO @id2, @s_subscription, @s_minutes_limit, @s_pay_over_limit
  272. --                  END
  273. --                  CLOSE cur2
  274. --                  DEALLOCATE cur2
  275. --          FETCH NEXT FROM cur1 INTO @id1, @f_subscription, @f_minutes_limit, @f_pay_over_limit
  276. --          END
  277. --      CLOSE cur1
  278. --      DEALLOCATE cur1
  279.  
  280. ----        select * from #Crossing_points
  281. --      DECLARE cur3 CURSOR FOR
  282. --      SELECT coords FROM #Crossing_points ORDER BY coords
  283. --      OPEN cur3
  284. --      FETCH NEXT FROM cur3 INTO @f_coord
  285. --      WHILE(@@FETCH_STATUS = 0)
  286. --      BEGIN
  287. --          FETCH NEXT FROM cur3 INTO @s_coord
  288.  
  289. --          IF (dbo.Best_Tariff(@f_coord) != dbo.Best_Tariff(@s_coord))
  290. --              BEGIN
  291. --                  INSERT INTO #Limits
  292. --                  VALUES ( CEILING(@f_coord), CEILING(@s_coord - 1),(SELECT Name FROM Kazikulov.Tariff WHERE dbo.Best_Tariff(@f_coord) = Name))
  293. --                  SET @f_coord =  CEILING(@s_coord)
  294.  
  295. --              END
  296. --      END
  297. --      INSERT INTO #Limits
  298. --      VALUES ( CEILING(@f_coord),44640,(SELECT Name FROM Kazikulov.Tariff WHERE dbo.best_tariff(@f_coord) = Name))
  299.  
  300. --      CLOSE cur3
  301. --      DEALLOCATE cur3
  302.  
  303. --      SELECT * FROM #Limits
  304. --end
  305. --GO
  306.  
  307. --EXEC Timelimits
  308. --GO
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top