CODEWITHSUNDEEP
sql-server
VPP
VPP

Reputation: 779

How to do SQL Range based calculations

I have two columns 'TotalAmount' and 'DefaultFees'

But there is a more granular table that says for the 'TotalAmount' ( which is 837.681561) between '0.010000' and '500.000000' fees should be '2.500000'

and for the rest of the total amount between '500.010000' and '800.000000' which is 300 fees should be '4.876000'

For the rest which is 37.681561, it should be default fees. There can be more rows to the Min/Max table

How can I achieve this in SQL

enter image description here

Expected Output

enter image description here

Upvotes: 0

Views: 60

Answers (1)

Eray Balkanli
Eray Balkanli

Reputation: 7990

Although Isaac's answer is more clear, as an alternative solution, you can use a case-when statement with a subqueries as below.

Select TotalAmount,
       case when TotalAmount >= (select max(MaximumAmount) from TableB) then DefaultFees 
            else (Select Fees 
                  From TableB b 
                  where a.TotalAmount<b.MaximumAmount and a.TotalAmount>=b.MinimumAmount
                 )
       end as FinalFee
From TableA a

EDIT:

Based on your updated question, this is the query you are looking for:

CREATE TABLE #TableA (myVal INT, DefaultFee float)
INSERT INTO #TableA VALUES (837,3.66)

CREATE TABLE #TableB (minamount INT, maxamount INT, fees float)
INSERT INTO #TableB VALUES (0,500,2.5),(500,800,4.876)

;WITH cte AS (
    SELECT ISNULL(b.minamount, (SELECT MAX(maxamount) FROM #tableB)) as [max]
    from #TableA a
    left join #TableB b on a.myVal between b.minamount and maxamount
)
SELECT maxamount, fees
FROM #TableB
WHERE maxamount <= (SELECT [max] FROM cte)

UNION

SELECT (a.myval - (SELECT [max] FROM cte)), a.DefaultFee
FROM #TableA a
ORDER BY 1 desc

Upvotes: 2

Related Questions