Getting a value based off MIN/MAX columns on same row

Transact-SQL
1

My database is populated as follows:

|BASE_PER_GALLON_MIN | BASE_PER_GALLON_MAX | SURCHARGE|
2.751 | 2.800 | 0.330
2.801 | 2.850 | 0.340
2.851 | 2.900 | 0.350
2.901 | 2.950 | 0.360
2.951 | 3.000 | 0.370
3.001 | 3.050 | 0.380

I am attempting to get the SURCHARGE amount if, as an example, the price falls between 2.851 and 2.900 which would be 0.350. I am at a lose how to write the SQL to look at the between numbers and give the end result.

My goal is to input an amount (example: 2.870) and the SQL sees it falls between BASE_PER_GALLON_MIN (2.851) and BASE_PER_GALLON_MAX (2.900) which should return the SURCHARGE amount of 0.350.

Help please?

2
script

create table #Temp (BASE_PER_GALLON_MIN float, BASE_PER_GALLON_MAX float, SURCHARGE float)

insert into #Temp select 2.751, 2.800, 0.330
insert into #Temp select 2.801, 2.850, 0.340
insert into #Temp select 2.851, 2.900, 0.350
insert into #Temp select 2.901, 2.950, 0.360
insert into #Temp select 2.951, 3.000, 0.370
insert into #Temp select 3.001, 3.050, 0.380

select * from #Temp

declare @input float = 2.870

select SURCHARGE from #Temp where @input between BASE_PER_GALLON_MIN and BASE_PER_GALLON_MAX

drop table #Temp

image
image784×579 11.8 KB

1 Like
3

Perfect! Thank you much...

I knew the Between Statement was required/needed but was unsure how to do it.

4

one issue may come with end points ..depends on your requirement

min 5 max 10 surcharge 3

input 5 or input 10 then does between pick up surcharge on not -=

+++++++++++++++++++++++++
for performance sake

better to use greater than less than

input >= 5 and input <= 10
rather than
input between 5 and 10

5

So noted .. I have a add on to the above question:

This seems to hang in MSSMS and unsure why... I want to take the result 0f the query and write it to another database. The query just seems to hang.

DECLARE @input float = '5.224'
DECLARE @updsc decimal (3, 3)
SELECT @updsc = (SELECT SURCHARGE
FROM Surcharge_Table
WHERE @input between BASE_PER_GALLON_MIN And BASE_PER_GALLON_MAX)

               While @updsc Is Not NULL
               BEGIN TRANSACTION
              UPDATE QUOTE_TABLE 
                 SET PER_MILE_FUEL_SURCHARGE = @updsc
              COMMIT
6

Well... Should slap meself hard...

Deleted the "while" statement - problem solved... it was in an infinite loop since the variable assigned never actually goes null... DUH!