计算员工收入的存储过程 [英] Stored Procedure calculating employee earnings

问题描述

我在C＃win表单应用程序中编写了以下存储过程，该应用程序根据出勤率计算员工收入，如下所示。



请注意，班次是12小时，员工会在每个班次的进出中标记出勤率。工资期也是从一个月的开始到结束（1日到28日/ 30日/ 31日）



相关的表格是：



***员工（emp_id，姓名缩写，姓氏，basic_sal，budj_allowance），

出勤率（emp_id，in_time，out_time，shift），

Rank （rank_id，shift_rate）***





**计算**



a。工作日 - 这是特定员工工作的天数，此值取自出勤表。



b。休息日 - 员工有权享受一个月最多4天的休假，如果员工休息超过4天，剩余的日子将被标记为休假日。



c。没有额外班次 - 此值由此公式计算

** [总班次 - 总工作天数]。**



e 。基本工资 - 这取自员工主表



f。预算津贴 - 所有员工的报酬为1,000卢比/ - 作为预算津贴



g。无工资天数 - 这是根据公式计算的

** [（04月份的天数） - 工作日期] **



h。减去无支付金额 - 这是根据公式计算的

** [（（基本工资+预算津贴）/（04年的天数））x无工资天数** **



i。 EPF的金额 - 这是从公式计算的

** [基本工资+预算津贴 - 减去无薪金额] **



j。加班金额 - 根据公式计算

** [EPF金额 - （额外班次x工作日）] **

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

推荐答案

首先，到处都有很多转换，这意味着 Attendance.in_time 以错误的格式存储。

如果无法修复，您可以考虑使用CTE以必要的格式选择和聚合所需的数据，然后在辅助查询。

辅助，您不应在 GROUP BY 子句中使用连接或函数。



看看这个：

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:

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.

就像所有事情一样......这取决于。我假设这是一个成熟的工作系统？



有一种思想流派认为像这样的业务逻辑最好不要使用SQL。我不知道使用它的系统，但理想情况下，这里的逻辑将在应用程序代码中处理，并且存储过程将被数据访问代码隐藏，这将把信息传递给处理的更高级别的类这个复杂的计算。 （如果没有别的，那么你可以进行有效的单元测试来覆盖这条逻辑，我不希望我的SQL在没有可重复的测试的情况下设置人们的工资）



所以这将是我的第一个评论。但我们假设由于某种原因不是一种选择。你提到优化吗？但不是要优化什么参数？我猜它的速度？如果没有看到表格结构，你真的无法说出要优化的内容。我要花很长时间才能打破这一切。我很想创建子存储过程或函数，并优化它们。也许一天休息，一天休假，一天休息，额外班次等。 （来自这里的数据也可以存储在单独的表中？）优化像这样复杂的东西是一项几乎没有结果的任务。你做了一次，你下周改变它，你的执行计划也会改变。如果你真的必须在SQL中具有这种复杂程度，那么我会将其分解为可管理的块并使用尽可能多的预处理来获得速度。



希望有所帮助。

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 .

这篇关于计算员工收入的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！