Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Creating User-defined Function

I’m working with the following table:

Employees_2 (
    EmpID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Department VARCHAR(50),
    Salary DECIMAL(8,2),
    DateOfJoining DATE
);

I need to create a user-defined function to determine if an employee is a ‘Senior’ (more than 3 years with the company) or ‘Junior’. Then, list all employees with this designation.

So far I’ve come up with the following code:

DELIMITER $$
CREATE FUNCTION DESIGNATION(DateOfJoining DATE)
RETURNS VARCHAR(30)
DETERMINISTIC
BEGIN
RETURN DESIGNATION = FLOOR(DATEDIFF(CURDATE(), DateOfJoining) / 365.25);
END $$
DELIMITER ;

How can I assign ‘Senior’ & ‘Junior’?

Tags:

2

Answers


  1. 0

    So to determine whether an Employee is ‘Senior’ or ‘Junior’ from a user-defined function based on DateOfJoining passed, use the below function definition snippet:

    DELIMITER $$
CREATE FUNCTION DESIGNATION(DateOfJoining DATE)
RETURNS VARCHAR(30)
DETERMINISTIC
BEGIN
RETURN (CASE
            WHEN FLOOR(DATEDIFF(CURDATE(), DateOfJoining) / 365.25) >= 3 THEN 'Senior'
            ELSE 'Junior'
        END);
END $$
DELIMITER ;

    Then write your select query to select employees with particular designations. For selecting all ‘Senior’ designation employees you will do something like this:

    SELECT e2.*
FROM Employees_2 e2
WHERE DESIGNATION(e2.DateOfJoining) = 'Senior';

    So to conclude, you won’t be able to select the second outcome from a user-defined function. You must write a select query as shown above or define a stored procedure to include both the requirements.

    Hope this helps!

    Login or Signup to reply.
  2. 0

    Why create a function? Just do with SELECT TIMESTAMPDIFF(year,date1,date2)

    SELECT *, IF (TIMESTAMPDIFF(YEAR, DateOfJoining, NOW()) >= 3,'senior','junior') label FROM Employees_2
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search