Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
siva_kodai
Starting Member
1 Post |
 Posted - 2013-06-10 : 08:49:07
|
| Dear All, We are having SQL Server .NET based product. The employee Salary calculation process which is written in stored procedure based called through Scheduler /SQL Job Call. I have shared the current table structure and we are planning to recode.I need all your suggession and views to design robust calculation engine. So I need your expertise view on the same. The following points i am facing day to day activity how to overcome the same. 1.The salary calculation which is happening in the backend level now. So locks (update lock) happening. How to avoid and over come the same what are all the things I have to concentrate on the design level. 2.The Calculation logic now stored procedure based has to be converted to .NET based code? The data is employee salary details so is it advisable to do the calculation in front ends level. Even we do the calculation we have to write back the values to Database table. 3.In my product there the salary Calculation is based on user driven formula. For Example: CASE WHEN GRADE=”G1” THEN 1000 ELSE 750 END. The expression evaluation and execution now at SQL Server stored procedure based. How optimal I can restructure the same. 4.Is CLR Based stored procedure is helpful? The following I have attached the transaction table design. CREATE TABLE PROCESSED_DATA( [N_EMPLOYEE_ID] [numeric](18, 0) NOT NULL, [D_PAY_DATE] [datetime] NOT NULL, [N_COMPONENT_ID] [numeric](10, 0) NOT NULL, [S_COMPONENT_CODE] [varchar](10) NULL, [S_COMPONENT_TYPE] [varchar](1) NULL, [S_CALCULATION_TYPE] [varchar](1) NULL, [S_DATA_TYPE] [varchar](1) NULL, [N_ACTUAL_AMOUNT] [numeric](14, 3) NULL, [N_EARNED_AMOUNT] [numeric](14, 3) NULL, [N_LOP_AMOUNT] [numeric](14, 3) NULL, [N_ARREAR_AMOUNT] [numeric](14, 3) NULL, [N_ADJUSTED_AMOUNT] [numeric](14, 3) NULL, [N_PAY_GROUP_ID] [numeric](10, 0) NULL, [S_CURRENCY_CODE] [varchar](20) NULL, [S_CURRENCY_CODE_BASE] [varchar](20) NULL, CONSTRAINT [PK_PROCESSEDDATA] PRIMARY KEY CLUSTERED ( [N_EMPLOYEE_ID] ASC, [D_PAY_DATE] ASC, [N_COMPONENT_ID] ASC ) ) G Sivaramakrishnan |
|
|
|
|
|
|
|
Design of Payroll System