none
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 Rammaca Friday, October 04, 2013 4:59 AM
    Thursday, October 03, 2013 5:50 AM

Answers

  • 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
    Moderator