December 31, 2002 at 3:21 am
I will be working on a Payroll Project very soon, and the requirement is such that we will have a control table with codes like
BA - Basic Pay
etc
and we will derive formulas from it like (BA/10) etc to actually calculate the Salary. Does anyone know of a sample code that I could use for this Formula Calculation.
Best Regards,
Trevor Benedict R
Microsoft Certified Solution Developer
Best Regards,
Trevor Benedict R
Microsoft Certified Solution Developer
December 31, 2002 at 3:59 am
Can you give a bit more detail on what you will see and what it should return based on the ex. BA/10 are you meaning Basic Pay with 10 payments or what are you needing?
January 1, 2003 at 7:40 pm
The Pay is calculated based on some formula like Basic Pay, then we add the Rent Allowance, after this we will deduct the loan etc and then the Tax etc and finally the Net Pay is calculated.
The Steps taken for a Pay Calculation can be different for individuals or for a Group. So I am looking for a way to configure a row stating that
BA - Basic Pay - Will be fixed for each emp
HA - Housing Allowance - Formula = BA*.4
VL - Vehicle Loan - Can be a Fixed amount for each employee
IT - Income Tax - ((BA+HA)-VL)*.7
This is part of the common Configuration set. This combination will be used for a Group and employees can be assigned to a Group or it will be overridden at the employee level. This will give a finer control over how the salary is calculated and will require fewer changes in the long run.
So a Group of Directors can have a Set of formula like
BA = 25000
HA = BA*.04
VL = 1500
IT = (BA+HA-VL)*.07
NET = BA+HA-VL-IT
The formulas can also be nested. For performance sake I need the shortest possible way to work out this. Any ideas.
Way back in '96 I remember using a Visual Basic class provided by Microsoft. This sample code was packaged in the MSPJ CD. If I could have that code then I can still mkae use of the same logic from SQL & VB
Best Regards,
Trevor Benedict R
Microsoft Certified Professional (VB)
Best Regards,
Trevor Benedict R
Microsoft Certified Solution Developer
January 2, 2003 at 5:27 am
I think I would consider using calculated columns for Housing Allowance and Income Tax then a View to calc Net Pay as the way calculated columns work Net pay would be a huge formula (could be done but view seems easier).
Not sure if needed but Icome Tax is calculated to round up instead or standard rounding by multiplying by the correct amount to get the right decimal position that needs to be rounded then using the ceiling function to round up and finally divied by the same number to return the value to the correct decimal value.
If nothing else the code should help see your calcs like you want. ANd if you prefer to have something reusable you can create functions in SQL 2000 but they cannot be used in calculated columns.
CREATE TABLE [TblPayroll] (
[FName] [varchar] (50) NOT NULL ,
[LName] [varchar] (50) NOT NULL ,
[SSN] [char] (9) NOT NULL ,
[BA] [numeric](19, 2) NOT NULL ,
[HA] AS (convert(numeric(19,2),([BA] * 0.04))) ,
[VL] [numeric](19, 2) NOT NULL CONSTRAINT [DF_TblPayroll_VL] DEFAULT (0.00),
[IT] AS (convert(numeric(19,2),(ceiling((([BA] + [BA] * 0.04 - [VL]) * 0.07 * 100)) / 100))) ,
CONSTRAINT [CK_Table8_SSN] CHECK ([SSN] like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
) ON [PRIMARY]
GO
CREATE VIEW vw_TblPayrollWNet
AS
SELECT FName, LName, SSN, BA, HA, VL, IT, (BA+HA-VL-IT) AS NET FROM TblPayroll
GO
INSERT TblPayroll (FName, LName, SSN, BA, VL) VALUES ('Mark', 'Jones', '123456789', 3614.52, 500.00)
GO
SELECT * FROM TblPayroll
GO
SELECT * FROM vw_TblPayrollWNet
GO
January 2, 2003 at 7:12 pm
Thanks.
Best Regards,
Trevor Benedict R
Microsoft Certified Professional (VB)
Best Regards,
Trevor Benedict R
Microsoft Certified Solution Developer
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply