I can't find a solution for Create a function with the name EmployeeSalaryIncrement with hireda-CodePudding

Logic for creating the function

Consider the EMP table fields hiredate and salary.

If the employee has completed 1 year from the date which he is hired, salary has to be incremented by 1000 rupees.
Like that for every year completion, salary has to increment by 1000 rupees.

Calculate the Current Salary for each employees by using this newly created function

Expected Output should be like below

EMPNO   ENAME   JOB     MGR     HIREDATE    SAL     COMM    DEPTNO  CurrentSalary
7369    SMITH   CLERK   7902    17-Dec-80   800     20      20800

CODE:

SAMPLE DATA

INSERT INTO EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO)
VALUES 
    (7369,'SMITH','CLERK',7902,'12/17/1980',800,NULL,20),
    (7499,'ALLEN','SALESMAN',7698,'02/20/81',1600,300,30),
    (7521,'WARD','SALESMAN',7698,'02/22/81',1250,500,30),
    (7566,'JONES','MANAGER',7839,'02/04/81' ,2975,NULL,20),
    (7654,'MARTIN','SALESMAN',7698,'09/28/81',1250,1400,30),
    (7698,'BLAKE','MANAGER',7839,'05/01/81',2850,NULL,30),
    (7782,'CLARK','MANAGER',7839,'06/09/81' ,2450,NULL,10),
    (7788,'SCOTT','ANALYST',7566,'12/09/82' ,3000,NULL,20),
    (7839,'KING','PRESIDENT',NULL,'11/17/81',5000,NULL,10),
    (7844,'TURNER','SALESMAN',7698,'09/08/81' ,1500,0,30),
    (7876,'ADAMS','CLERK',7788,'01/12/83' ,1100,NULL,20),
    (7900,'JAMES','CLERK',7698,'12/03/81' ,950,NULL,30),
    (7902,'FORD','ANALYST',7566,'12/03/81' ,3000,NULL,20),
    (7934,'MILLER','CLERK',7782,'01/23/82',1300,NULL,10);

CodePudding user response：

If I understand you correct you want a function that will calculate the new salary of employees based on the hiredate and the current date

A function like that can look like this

create function EmployeeSalaryIncrement(@hiredate date, @sal int) returns int as
begin
     declare @years int = datediff(year, @hiredate, getdate())
     return @sal   (@years * 1000)
end

Please find a complete example here, check it out if this is what you need

CodePudding user response：

Considering that the calculation is a direct calculation on data from a single table an Inline Table Valued Function (iTVF) might yield better performance. Normally this is the case.

As such the function can be:

CREATE FUNCTION dbo.tvf_EmployeeSalaryIncrement( @EmplCode int )
RETURNS TABLE
AS
RETURN (  
  SELECT
    e.EMPNO
  , Datediff( year, e.HIREDATE, Sysdatetime() ) * 1000
      e.SAL AS CurrentSalary
  FROM
    EMP AS e
  WHERE
      e.EMPNO = @EmplCode
);

This can be used like:

SELECT
  ...
FROM EMP AS e
  INNER JOIN tvf_EmployeeSalaryIncrement( e.EMPNO )
;

In all honesty using a function just because there is a calculation seems overkill to me. A much simpler way is a simple view that takes the columns from table EMP and adds the calculation (as single line) to the output. This works unless the date to use as comparison date shall be flexible. In this case this comparison date can be added to the function.

Additional background for Table Valued Functions:

https://www.sqlservercentral.com/articles/the-basics-of-itvfs