I have following stored procedure written in a C# win forms application which calculates employee earnings based on attendance as follows.
Note that a shift is 12 hours and employees mark attendance for in and out of each shifts. Also salary period is from beginning to end of a month (1st to 28th / 30th / 31st)
Related tables are :
***Employee (emp_id, initials, surname, basic_sal, budj_allowance),
Attendance (emp_id, in_time, out_time,shift),
Rank (rank_id, shift_rate)***
**Calculations**
a. Work Days- This is the number of days a particular employee has worked and this value is taken from Attendance table.
b. Day Offs- An employee is entitled for maximum of 4 day offs for a month and if more than four days have been taken by an employee, remaining days will be marked as “Leave days”.
c. No of Extra Shifts- This value is taken by this formula
**[Total Shifts- total days worked].**
e. Basic Salary – This is taken from employee master table
f. Budgetary Allowance –All employees are paid Rs.1,000/- as budgetary allowance
g. No Pay Days – This is calculated from the formula
**[(No of days in the month-04)-days worked]**
h. Less No Pay Amount- This is calculated from the formula
**[((Basic Salary + Budgetary Allowance) / (No of Days in the month-04)) x No Pay Days]**
i. Amount for the EPF- This is calculated from the formula
**[Basic Salary + Budgetary Allowance - Less No Pay Amount]**
j. Overtime Amount- This is calculated from the formula
**[Amount for the EPF - (Extra Shift Rate x Work Days)]**
CREATE PROCEDURE [dbo].[sp_Earnings] @fromDate datetime, @toDate datetime
AS
BEGIN
DECLARE @No_of_days int
SELECT @No_of_days = DATEDIFF(day,@fromDate,DATEADD(day,1,(@toDate)))
DECLARE @Day_offs_allowed int
SELECT @Day_offs_allowed=4
DECLARE @SalRef char(20)
SELECT @SalRef= REPLACE(STUFF(CONVERT(varchar(12),CONVERT(date,@fromDate,107),106),1,3,''),' ','')
SET NOCOUNT ON;
SELECT Employee.Emp_ID, Employee.Initials + ', ' + Employee.Surname AS Name,
COUNT(DISTINCT CONVERT(DATE, Attendance.in_time)) AS work_days,
CASE WHEN (@No_of_days - (COUNT(DISTINCT CONVERT(DATE, Attendance.in_time))) >= @Day_offs_allowed)
THEN @Day_offs_allowed
ELSE (@No_of_days - (COUNT(DISTINCT CONVERT(DATE, Attendance.in_time))))
END AS day_offs,
CASE WHEN (@No_of_days - (COUNT(DISTINCT CONVERT(DATE, Attendance.in_time))) >= @Day_offs_allowed)
THEN @No_of_days - (COUNT(DISTINCT CONVERT(DATE, Attendance.in_time))) - @Day_offs_allowed
ELSE 0
END AS leave_days,
COUNT(Attendance.shift) - COUNT(DISTINCT CONVERT(DATE, Attendance.in_time)) AS extra_shifts,
Rank.Shift_Rate,
(COUNT(Attendance.shift) - COUNT(DISTINCT CONVERT(DATE, Attendance.in_time)))* rank.Shift_Rate AS Extra_Shift_Amount,
employee.Basic_Sal,
employee.budj_allowance,
(@No_of_days-@Day_offs_allowed)- COUNT(DISTINCT CONVERT(DATE, Attendance.in_time)) AS no_pay_days,
CONVERT(DECIMAL(10,2),(((employee.basic_sal+employee.budj_allowance) / (@No_of_days-@Day_offs_allowed) )) * ((@No_of_days-@Day_offs_allowed)- COUNT(DISTINCT CONVERT(DATE, Attendance.in_time)))) AS less_no_pay_amt,
employee.basic_sal+employee.budj_allowance-CONVERT(DECIMAL(10,2),((employee.basic_sal+employee.budj_allowance) / (@No_of_days-@Day_offs_allowed) ) * ((@No_of_days-@Day_offs_allowed)- COUNT(DISTINCT CONVERT(DATE, Attendance.in_time))))AS amt_for_epf,
CONVERT(DECIMAL(10,2),((Rank.Shift_Rate*(COUNT(DISTINCT CONVERT(DATE, Attendance.in_time))))-((((employee.basic_sal+employee.budj_allowance)-(((employee.basic_sal+employee.budj_allowance) / (@No_of_days-@Day_offs_allowed)) * (@No_of_days-@Day_offs_allowed- COUNT(DISTINCT CONVERT(DATE, Attendance.in_time))))))))) AS over_time_amt,
@salRef AS Reference
FROM Employee INNER JOIN
Attendance ON Employee.Emp_ID = Attendance.EID INNER JOIN
Point ON Attendance.PID = Point.PID INNER JOIN
Rank ON Employee.Rank = Rank.Rank_ID
WHERE Attendance.in_time BETWEEN CONVERT(DATETIME, @fromDate, 102) AND CONVERT(DATETIME, @toDate, 102)
GROUP BY Employee.Emp_ID, Employee.Initials + ', ' + Employee.Surname, Rank.Shift_Rate, Employee.Basic_Sal, Employee.budj_allowance
ORDER BY Employee.Emp_ID
END
I want to know whether this could be further optimized ? Any notable flows ? And also is a stored procedure suitable for this requirement?