Stored Procedure calculating employee earnings

Question

0.00/5 (No votes)

See more:

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

SQL

    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 is a reference to identify month and year of everyrecord. example - **"APR2014"**

    DECLARE @SalRef char(20)
    SELECT @SalRef= REPLACE(STUFF(CONVERT(varchar(12),CONVERT(date,@fromDate,107),106),1,3,''),' ','')
    
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
       
        
    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?

Posted 12-May-14 3:36am

Chathur

Updated 12-May-14 3:39am

v2

Add a Solution

2 solutions

Solution 1

As in all things ... It depends . I am assuming that this is a mature working system ?

There is a school of thought that says that business logic such as this is best kept out of SQL . I have no idea of the systems that are using this ,but ideally the logic in here would be handled in the application code , and the stored procedure would be hidden by the data access code which would pass the info to a higher level class that handled this complex calculation . ( If nothing else then you can have valid unit tests covering this piece of logic , I would not want my SQL to be setting peoples wages without repeatable tests)

So that would be my first comment . But lets assume that is not an option for some reason . You mention optimize ? But not what parameter to optimize for ? I'm guessing its speed ? You cant really say what to optimize without seeing the table structure . It would take me too long to break all that down . I would be tempted to create sub stored procs or functions , and optimise those . Maybe one for days off , one for leave days , one for work days , extras shifts etc . (The data from this could also be stored in separate tables ?) Optimizing something as complex as this is an almost fruitless task . You do it once , you change it next week and your execution plan changes . If you really must have this level of complexity in SQL then I would break it down into manageable chunks and use as much preprocessing as possible to get speed .

Hope that helps .

Posted 12-May-14 8:38am

Andrew Torrance

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jörgen Andersson · Accepted Answer · 2014-05-12T10:34:00

Firstly, there are lots of conversions happening everywhere which implies that Attendance.in_time is stored in the wrong format.
If fixing that isn't possible you can consider using a CTE that selects and aggregates the needed data in the necessary format and then use this in a secondary query.
Secondary, you shouldn't use concatenation or functions in the GROUP BY clause.

Have a look at this:

SQL

WITH CTE AS (
    SELECT  
            Employee.Emp_ID,
            Employee.Initials,
            Employee.Surname,
            COUNT(DISTINCT CONVERT(DATE, Attendance.in_time)) AS work_days,
            COUNT(Attendance.shift) AS shifts,
            Rank.Shift_Rate,
            employee.Basic_Sal,
            employee.budj_allowance,
    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,
    )
SELECT  
        Emp_ID,
        Initials + ', ' + Surname AS Name, 
        work_days,
        CASE
            WHEN (@No_of_days - (work_days) >= @Day_offs_allowed)
            THEN @Day_offs_allowed
            ELSE (@No_of_days - (work_days))
        END AS day_offs,
        CASE
            WHEN (@No_of_days - (work_days) >= @Day_offs_allowed) 
            THEN @No_of_days - (work_days) - @Day_offs_allowed 
            ELSE 0 
        END AS leave_days, 
    	shifts - work_days AS extra_shifts, 
        Shift_Rate,  
        (shifts - work_days)* Shift_Rate AS Extra_Shift_Amount, 
        Basic_Sal, 
        budj_allowance,
        (@No_of_days-@Day_offs_allowed)- work_days AS no_pay_days,
        CONVERT(DECIMAL(10,2),(((basic_sal+budj_allowance) / (@No_of_days-@Day_offs_allowed) )) * ((@No_of_days-@Day_offs_allowed)- work_days)) AS less_no_pay_amt, 
        basic_sal+budj_allowance-CONVERT(DECIMAL(10,2),((basic_sal+budj_allowance) / (@No_of_days-@Day_offs_allowed) ) * ((@No_of_days-@Day_offs_allowed)- work_days))AS amt_for_epf, 
        CONVERT(DECIMAL(10,2),((Shift_Rate*(work_days))-((((basic_sal+budj_allowance)-(((basic_sal+budj_allowance) / (@No_of_days-@Day_offs_allowed)) * (@No_of_days-@Day_offs_allowed- work_days))))))) AS over_time_amt, 
        @salRef AS Reference 
FROM    CTE

But just as have been mentioned before, the second part actually belongs in the business layer.