Stored Procedure calculating employee earnings

I have following stored procedure 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 28st / 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

-
Work Days - This is the number of days a particular employee has worked and this value is taken from Attendance table.

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

-
No of Extra Shifts - This value is taken by this formula.

[Total Shifts - total days worked]

-
Basic Salary - This is taken from employee master table

-
Budgetary Allowance - All employees are paid Rs.1,000/- as budgetary allowance

-
No Pay Days - This is calculated from the formula

[(No of days in the month-04) - days worked]

-
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]

-
Amount for the EPF - This is calculated from the formula

[Basic Salary + Budgetary Allowance - Less No Pay Amount]

-
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
-- Add the parameters for the stored procedure here

AS
BEGIN
-- Declaring a variable to hold on of days in the month.
DECLARE @No_of_days int
SELECT @No_of_days = DATEDIFF(day,@fromDate,DATEADD(day,1,(@toDate)))

-- Declaring a constant to hold no of off days allowed in a month
DECLARE @Day_offs_allowed int
SELECT @Day_offs_allowed=4

--This

Here are my thoughts on your proc.

The Good

Good job on commenting out sections that are not so obvious to figure out. I had very little difficulty understanding it, your code is much cleaner than the average SQL post on this site. I would say a stored procedure is the correct type of database object for this, since it sounds like it will be called regularly based on its nature.

Improvements

-
Even though SQL engine is set up to where you don't always need to use delimiter ; it is good practice to explicitly use them. DECLARE @No_of_days int becomes DECLARE @No_of_days int; etc. throughout.

-
To avoid errors when creating a proc, it is a good idea to DROP PROCEDURE IF EXISTS [dbo].[sp_Earnings]; for instance.

-
This may just be personal preference, but I think SET is less ambiguous to set variables, so SELECT @Day_offs_allowed=4; would become SET @Day_offs_allowed=4;. This helps to differentiate them from nested queries and such.

-
I think this is a bit odd:

SELECT @SalRef= REPLACE(STUFF(CONVERT(varchar(12),CONVERT(date,@fromDate,107),106),1,3,''),' ','').
I would be tempted to instead use
SET @SalRef = CONCAT( DATEPART(Mm, @fromDate), '/', DATEPART(Yy, @fromDate) ).
This reference will look slightly different, e.g. "04/2014" but achieve the same purpose more elegantly plus you can sort them in order more easily numerically.

-
A point on formatting. When using long nested statements it is good practice to use line break and tabs to make it easier to read. For example:

(COUNT(Attendance.shift) - COUNT(DISTINCT CONVERT(DATE, Attendance.in_time)))* rank.Shift_Rate AS Extra_Shift_Amount

Becomes:

(COUNT
    (Attendance.shift) - COUNT(        
        DISTINCT CONVERT(DATE, Attendance.in_time)    
    )        
)* rank.Shift_Rate AS Extra_Shift_Amount,

Other than that I think your code is good.

StackExchange Code Review Q#49496, answer score: 2