Calculate payroll process amount based on the Slabs

Question

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