# Slab Based amount Calculation

### Question

• Hi,

My scenario is, i have to calculate payroll process amount based on the Slabs....

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

• Edited by Friday, October 04, 2013 4:59 AM
Thursday, October 03, 2013 5:50 AM

• Hi Rammaca,

You can refer to the following codes:

```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,'')

declare @employees int;
--set @employees = 150
--set @employees = 250
set @employees = 350
;with CTE as
(
select *,ROW_NUMBER() over(order by minrange) as RowNum from Tbl_Slab
)
,cte2 as
(
select c1.MinRange as MinRange,c1.MaxRange as MaxRange, c1.Baserate as Baserate, c1.perrecord as perrecord
,c2.MinRange as MinRange2,c2.MaxRange as MaxRange2, c2.Baserate as Baserate2, c2.perrecord as perrecord2 from cte c1
inner join cte c2
on c1.RowNum = c2.RowNum+1
)
select
case when Baserate = 0 then Baserate2+perrecord2*MaxRange2+(@employees-MaxRange2)*perrecord else Baserate+@employees*perrecord end as [Count]
from cte2
where @employees>MinRange
and @employees<MaxRange;```

Allen Li
TechNet Community Support

Tuesday, October 08, 2013 3:17 AM