"Description", "LeaveType", "MonthlyMaxLeave", "YearlyMaxLeave" are the column names in the table EmpTab.
I have written a basic logic to calculate the PayLeave and NonPayLeave, and to display its final values. Assume that the fields "Description" and "LeaveType" always contains the values "Sick" and "Yearly" respectively. Please do not worry about the data in the table. The field values in the table may be customized as per the requirements.
Just correct the syntax in the code so as to meet the SQL standards, as well as to display the PayLeave and NonPayLeave values based on the calculation logic shown below.
Expand|Select|Wrap|Line Numbers
- USE [Emp1]
- GO
- CREATE FUNCTION [dbo].[Leave]
- (
- @EmployeeID INT
- )
- RETURNS INT
- AS
- BEGIN
- DECLARE @PayLeave INT,
- @NonPayLeave INT;
- SET @PayLeave = 0;
- SET @NonPayLeave = 0;
- RETURN
- (
- SELECT
- IF((ImDescription == "Sick") AND (ImLeaveType == "Yearly"))
- {
- IF((ImMonthlyMaxLeave > 1) OR (ImYearlyMaxLeave > 10 ))
- {
- @NonPayLeave = @NonPayLeave + 1;
- PRINT @NonPayLeave;
- }
- ELSE
- {
- @PayLeave = @PayLeave + 1;
- PRINT @PayLeave;
- }
- }
- )
- END
- --PRINT dbo.Leave('123')