Refer the below sample test quesry for your reference and implement it as per your code logic at insert time.
SQL
Declare @SingleBedPer INT
Declare @DoubleBedPer INT
Declare @SingleTotal decimal(18,2)
Declare @DoubleTotal decimal(18,2)
DECLARE @TaxFreeAmount decimal(18,2) = 0
DECLARE @SlabOneAmount decimal(18,2) = 0
DECLARE @SlabOneTax decimal(18,2)= 0
DECLARE @SlabTwoAmount decimal(18,2)= 0
DECLARE @SlabTwoTax decimal(18,2)= 0
DECLARE @SlabThreeAmount decimal(18,2)= 0
DECLARE @SlabThreeTax decimal(18,2)= 0
SET @SingleBedPer = 12 -- get your single bed percentage based on your condition for record
SET @DoubleBedPer = 12 -- get your double bed percentage based on your condition for record
SET @SingleTotal = 100 -- get your single bed Amount based on your condition for record
SET @DoubleTotal = 200 -- get your double bed Amount based on your condition for record
DECLARE @SingleBedPercentAmount decimal(18,2)= (SELECT CONVERT(DECimal(18,2),@SingleTotal) * (CONVERT(DECimal(18,2),@SingleBedPer)/100))
DECLARE @DoubleBedPercentAmount decimal(18,2) = (SELECT CONVERT(DECimal(18,2),@DoubleTotal) * (CONVERT(DECimal(18,2),@DoubleBedPer)/100))
if(@SingleBedPer = 0 and @DoubleBedPer = 0)
BEGIN
SET @TaxFreeAmount = @SingleTotal + @DoubleTotal
END
ELSE if(@SingleBedPer = 12 and @DoubleBedPer = 12)
BEGIN
SET @SlabOneAmount = @SingleTotal + @DoubleTotal
SET @SlabOneTax = @SingleBedPercentAmount + @DoubleBedPercentAmount
END
ELSE if(@SingleBedPer = 18 and @DoubleBedPer = 18)
BEGIN
SET @SlabTwoAmount = @SingleTotal + @DoubleTotal
SET @SlabTwoTax = @SingleBedPercentAmount + @DoubleBedPercentAmount
END
ELSE if(@SingleBedPer = 28 and @DoubleBedPer = 28)
BEGIN
SET @SlabThreeAmount = @SingleTotal + @DoubleTotal
SET @SlabThreeTax = @SingleBedPercentAmount + @DoubleBedPercentAmount
END
ELSE
BEGIN
IF(@SingleBedPer = 0 )
BEGIN
SET @SlabOneAmount = @SingleTotal
SET @SlabOneTax = @SingleBedPercentAmount
END
ELSE IF(@SingleBedPer = 12 )
BEGIN
SET @SlabTwoAmount = @SingleTotal
SET @SlabTwoTax = @SingleBedPercentAmount
END
ELSE IF(@SingleBedPer = 18 )
BEGIN
SET @SlabTwoAmount = @SingleTotal
SET @SlabTwoTax = @SingleBedPercentAmount
END
ELSE IF(@SingleBedPer = 28 )
BEGIN
SET @SlabThreeAmount = @SingleTotal
SET @SlabThreeTax = @SingleBedPercentAmount
END
IF(@DoubleBedPer = 0 )
BEGIN
SET @SlabOneAmount = @DoubleTotal
SET @SlabOneTax = @DoubleBedPercentAmount
END
ELSE IF(@DoubleBedPer = 12 )
BEGIN
SET @SlabTwoAmount = @DoubleTotal
SET @SlabTwoTax = @DoubleBedPercentAmount
END
ELSE IF(@DoubleBedPer = 18 )
BEGIN
SET @SlabTwoAmount = @DoubleTotal
SET @SlabTwoTax = @DoubleBedPercentAmount
END
ELSE IF(@DoubleBedPer = 28 )
BEGIN
SET @SlabThreeAmount = @DoubleTotal
SET @SlabThreeTax = @DoubleBedPercentAmount
END
END
SELECT @TaxFreeAmount TaxFreeAmount,@SlabOneAmount SlabOneAmount,@SlabOneTax labOneTax,@SlabTwoAmount SlabTwoAmount,@SlabTwoTax SlabTwoTax,@SlabThreeAmount SlabThreeAmount,@SlabThreeTax SlabThreeTax
Screenshot
| TaxFreeAmount |
SlabOneAmount |
labOneTax |
SlabTwoAmount |
SlabTwoTax |
SlabThreeAmount |
SlabThreeTax |
| 0.00 |
300.00 |
36.00 |
0.00 |
0.00 |
0.00 |
0.00 |
