Advertisement
Guest User

Untitled

a guest
May 29th, 2017
61
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 3.36 KB | None | 0 0
  1. ------3 zmniejsz place pracownikow pracujacych w danym dziale o okreslony procent w danym hotelu, wyswietl odpowiedni komunikat
  2. --IF EXISTS(SELECT * FROM sys.objects WHERE TYPE='P' AND name='rozmyte')
  3. --DROP PROCEDURE rozmyte
  4. --GO
  5.  
  6. --CREATE PROCEDURE rozmyte @id_pracownika INT=NULL,  @low FLOAT=NULL OUTPUT, @middle FLOAT=NULL OUTPUT, @high FLOAT=NULL OUTPUT
  7. --AS
  8. --BEGIN
  9. --  DECLARE @a INT, @b INT, @c INT, @d INT, @earning INT
  10. --  SET @earning = (SELECT e.earnings FROM hotel..employee e WHERE e.id_employee = @id_pracownika)
  11. --  SET @a = 1500
  12. --  SET @b = 3000
  13. --  SET @c = 4500
  14. --  SET @d = 6000
  15. --  BEGIN
  16. --      if (@earning <= @a)
  17. --          begin
  18. --              SET @low = 1.0
  19. --              set @middle = 0.0
  20. --              set @high = 0.0
  21. --          end
  22. --      if (@earning >= @b and @earning <= @c)
  23. --          begin
  24. --              SET @low = 0.0
  25. --              set @middle = 1.0
  26. --              set @high = 0.0
  27. --          end
  28. --      if (@earning >= @d)
  29. --          begin
  30. --              set @low = 0.0
  31. --              set @middle = 0.0
  32. --              set @high = 1.0
  33. --          end
  34.            
  35. --      if (@earning > @a and @earning < @b)
  36. --          begin
  37. --              declare @first float, @second float
  38. --              set @first = 1.0 / (@a - @b)
  39. --              set @second = -@first * @b
  40. --              set @low = @first * @earning + @second
  41. --              set @middle = 1.0 - @low
  42. --              set @high = 0.0
  43. --          end
  44. --      if (@earning > @c and @earning < @d)
  45. --          begin
  46. --              set @first = 1.0 / (@c - @d)
  47. --              set @second = -@first * @d
  48. --              set @low = 0.0
  49. --              set @middle = @first * @earning + @second
  50. --              set @high = 1.0 - @middle
  51. --          end
  52. --  END
  53. --END
  54. --GO
  55.  
  56.  
  57. --BEGIN
  58. --  DECLARE @low float, @middle float, @high float
  59. --  EXEC rozmyte 45, @low output, @middle output, @high output
  60. --  PRINT 'k'
  61. --  print cast(@low as varchar(10)) + ' ' + cast(@middle as varchar(10)) + ' ' + cast(@high as varchar(10))
  62. --END
  63. --GO
  64.  
  65. IF EXISTS(SELECT * FROM sys.objects WHERE type='FN' AND name='fn_rozmyte')
  66. DROP FUNCTION fn_rozmyte
  67. GO
  68.  
  69. CREATE FUNCTION fn_rozmyte(@earning INT, @a INT, @b INT, @c INT, @d INT, @wsp INT) RETURNS FLOAT(4)
  70. BEGIN
  71.     DECLARE @low FLOAT(4), @middle FLOAT(4), @high FLOAT(4), @return_value FLOAT(4)
  72.     BEGIN
  73.         if (@earning <= @a)
  74.             begin
  75.                 SET @low = 1.0
  76.                 set @middle = 0.0
  77.                 set @high = 0.0
  78.             end
  79.         if (@earning >= @b and @earning <= @c)
  80.             begin
  81.                 SET @low = 0.0
  82.                 set @middle = 1.0
  83.                 set @high = 0.0
  84.             end
  85.         if (@earning >= @d)
  86.             begin
  87.                 set @low = 0.0
  88.                 set @middle = 0.0
  89.                 set @high = 1.0
  90.             end
  91.            
  92.         if (@earning > @a and @earning < @b)
  93.             begin
  94.                 declare @first float, @second float
  95.                 set @first = 1.0 / (@a - @b)
  96.                 set @second = -@first * @b
  97.                 set @low = @first * @earning + @second
  98.                 set @middle = 1.0 - @low
  99.                 set @high = 0.0
  100.             end
  101.         if (@earning > @c and @earning < @d)
  102.             begin
  103.                 set @first = 1.0 / (@c - @d)
  104.                 set @second = -@first * @d
  105.                 set @low = 0.0
  106.                 set @middle = @first * @earning + @second
  107.                 set @high = 1.0 - @middle
  108.             end
  109.     END
  110.     if (@wsp = 1)
  111.         set @return_value = @low
  112.     if (@wsp = 2)
  113.         set @return_value = @middle
  114.     if (@wsp = 3)
  115.         set @return_value = @high
  116.  
  117.     RETURN @return_value
  118. END
  119. GO
  120.  
  121. DECLARE @a INT, @b INT, @c INT, @d INT
  122. SET @a = 1500
  123. SET @b = 3000
  124. SET @c = 4500
  125. SET @d = 6000
  126.  
  127. SELECT e.id_employee, e.name, e.surname, e.earnings, dbo.fn_rozmyte(e.earnings, @a, @b, @c, @d, 1) AS low, dbo.fn_rozmyte(e.earnings, @a, @b, @c, @d, 2) AS middle, dbo.fn_rozmyte(e.earnings, @a, @b, @c, @d, 3) AS high
  128. FROM hotel..employee e
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement