Advertisement
elena1234

User Defined Functions ( T-SQL)

Mar 27th, 2022
1,749
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 1.80 KB | None | 0 0
  1. USE [AdventureWorks2019]
  2. GO
  3.  
  4. --Exercise 1
  5. --Create a user-defined function that returns the percent that one number is of another.
  6. --For example, if the first argument is 8 and the second argument is 10, the function should return the string "80.00%".
  7. --The function should solve the "integer division" problem by allowing you to divide an integer by another integer,
  8. --and yet get an accurate decimal result.
  9. CREATE FUNCTION dbo.ufnReturnPercentOfNumber(@FirstNumber INT, @SecondNumber INT)
  10. RETURNS NVARCHAR(8)
  11. AS  
  12. BEGIN
  13.     DECLARE @Result DECIMAL(8,2) = (@FirstNumber * 1.0) / (@SecondNumber * 1.0) * 100.0
  14.     DECLARE @FinalResult NVARCHAR(8) = Cast(@Result AS NVARCHAR) + '%'
  15.     RETURN @FinalResult
  16. END
  17.  
  18.  
  19. SELECT dbo.ufnReturnPercentOfNumber(8 , 10)
  20.  
  21.  
  22. --Exercise 2
  23. --Store the maximum amount of vacation time for any individual employee in a variable.
  24. --Then create a query that displays all rows and the following columns from the AdventureWorks2019.HumanResources.Employee table:
  25. --BusinessEntityID
  26. --JobTitle
  27. --VacationHours
  28. --Then add a derived field called "PercentOfMaxVacation", which returns the percent an individual employees'
  29. --vacation hours are of the maximum vacation hours for any employee.
  30. --For example, the record for the employee with the most vacation hours should have a value of 100.00%, in this column.
  31. --The derived field should make use of your user-defined function from the previous exercise, as well as your variable that stored
  32. --the maximum vacation hours for any employee.
  33.  
  34. DECLARE @MaxVacationHours INT = (SELECT MAX(VacationHours) FROM AdventureWorks2019.HumanResources.Employee)
  35.  
  36. SELECT
  37. BusinessEntityID,
  38. JobTitle,
  39. VacationHours,
  40. PercentOfMaxVacation = dbo.ufnReturnPercentOfNumber(VacationHours,  @MaxVacationHours)
  41. FROM AdventureWorks2019.HumanResources.Employee
  42.  
  43.  
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement