how to pickup rate between current dates

[adnancanada]

Hello everyone,
I am stuck here you guys can help me out. I have historical rates I need to apply only one rates based on pickup date.
i want to write sql query that will only pick up RatePerKg between effective and expiry date.

in this example pickup date 30-jun-2023 which is under this bucket.

32030

0003001155G20

30-Jun-23

18-Jun-23

30-Dec-23

$10.18

[madpiet]

You need a non-equijoin to return one record

SELECT ...
FROM MyTable mt
INNER JOIN RatesTable rt ON mt.MyDate >= rt.StartDate AND mt.MyDate <= rt.EndDate

Wait, that table doesn't make sense. Shouldn't a rates table be something like
(RateID INT IDENTITY, StartDate DATE NOT NULL, EndDate DATE NOT NULL, Rate DECIMAL(6,2) ) or similar?

Then you just do a non-equijoin, assuming the rate periods don't overlap.

[CJ_London]

that table does not work

for 30-Jun-23 you have two possible rates: 16-Apr-23 to 30-Dec-23 ($13.72) and 18-Jun-23 to 30-Dec-23 ($10.18)

On what basis do you decide it should be the second one?

[madpiet]

Hello everyone,
I am stuck here you guys can help me out. I have historical rates I need to apply only one rates based on pickup date.
i want to write sql query that will only pick up RatePerKg between effective and expiry date.

in this example pickup date 30-jun-2023 which is under this bucket.

32030

0003001155G20

30-Jun-23

18-Jun-23

30-Dec-23

$10.18

This rates table doesn't super make sense. Rate periods shouldn't overlap. Can you post some data? Create table script, insert script, and for the love of all things good and holy, as TEXT and not a picture.