0
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')

How to convert the following SQL-C# logic to syntactically-correct SQL code (SQL Server 2005)?

"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.

2
Contributors
1
Reply
9
Views
4 Years
Discussion Span
Last Post by adam_k
0

Assuming a lot, this could work:

select sum(non_pay) as non_pay_leave, sum(pay) as pay_leave
from (
select 1 as non_pay, 0 as pay 
from table
where employee= @employeeid
and description = "Sick" 
and leavetype = "Yearly" 
and (monthlymaxleave > 1 
or yearlymaxleave > 10 ) 
union all 
select 0, 1 
from table
where employee = @employeeid
and description = "Sick" 
and leavetype = "Yearly" 
and (monthlymaxleave <= 1 
or yearlymaxleave <= 10) 
) a 
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.