Hi,
My scenario is, i have to calculate payroll process amount based on the Slabs....
CREATE TABLE #slab
(lbound int
,ubound int
,fixed_amount decimal(10,0)
,variable_amount decimal(10,0)
)
INSERT INTO #slab VALUES (1,100,5000,0)
INSERT INTO #slab VALUES (101,200,0,40)
INSERT INTO #slab VALUES (201,300,0,30)
INSERT INTO #slab VALUES (301,400,0,20)
Declare @employees int
Set @employees=300
SELECT SUM(fixed_amount + (employees * variable_amount))
FROM (
SELECT CASE WHEN @employees > ubound THEN ubound ELSE @employees END + 1 - lbound AS employees
, fixed_amount
, variable_amount
FROM #slab
WHERE @employees >= lbound
) AS employees_per_slab
drop table #slab
Above mention query is properly working fine. But flowing insert query I included 'exclude' keyword . So how to change procedure..
The amount should get calculated with respect to slab defined for it....
CREATE TABLE [dbo].[Tbl_Slab](
[MinRange] [bigint] NULL,
[MaxRange] [bigint] NULL,
[Baserate] [decimal](10,2)NULL,
[perrecord] [decimal] (10,2)NULL,
[Condition] [nvarchar](10) NOT NULL)
insert into Tbl_Slab values(1,100,1500,0,'')
insert into Tbl_Slab values(101,200,0,50,'')
insert into Tbl_Slab values(201,300,5000,30,'exclude')
insert into Tbl_Slab values (301,400,0,20,'')
Calculation part:
1. To calculate payroll for 150 employees when the codition is ''. The result is
1500->for 100 employee basic amount
50*50->another 50 employee and 50Rs per employee.
Total Amount:4000
2. To calculate payroll for 250 employees when the codition is 'exclude'. If the condition is exclude then it should ignore the previous slabs and the result is
5000-> the baserate
250*30->7500 = 5000+7500
Total Amount=12500
3. To calculate payroll for 350 employees when the codition is ''.The result is
5000-> the baserate
300*30->9000 = 5000+9000
50*20->1000=5000+9000+1000=15000
Total Amount=15000
How can i Achieve this using sql server....Kindly help...
Thanks and Regards,
Ramesh
Veena SardaPosted Oct 7, 2013, 12:25 AM