将行与另一个表中2个日期之间的日期匹配

尝试这个

WITH
Fees (BookingTypeId, FeeAmount, EffectiveFrom) AS (
    SELECT 1, 50, '2019-06-01'
    UNION ALL SELECT 1, 55, '2019-09-01'
),
Bookings (Date, CustomerId, BookingTypeId, FeeCharged) AS (
    SELECT '2019-07-01', 1, 1, 50
    UNION ALL SELECT '2019-07-02', 1, 1, 50
    UNION ALL SELECT '2019-10-01', 1, 1, 150
),
BookingFeeUpdate as (
    SELECT
        Bookings.BookingTypeId,
        Bookings.Date,
        MAX(EffectiveFrom) LastFeeUpdate
    FROM
        Bookings
    LEFT JOIN Fees ON
        Fees.BookingTypeId = Bookings.BookingTypeId
        AND Fees.EffectiveFrom <= Bookings.Date
    GROUP BY
        Bookings.BookingTypeId,
        Bookings.Date
)
SELECT
    Bookings.*, Fees.FeeAmount as CorrectFee, Fees.EffectiveFrom, case Fees.FeeAmount when FeeCharged then 1 else 0 end Correct
FROM
    Bookings
INNER JOIN BookingFeeUpdate ON
    BookingFeeUpdate.BookingTypeId = Bookings.BookingTypeId
    AND BookingFeeUpdate.Date = Bookings.Date
LEFT JOIN Fees ON
    Fees.BookingTypeId = Bookings.BookingTypeId
    AND Fees.EffectiveFrom = BookingFeeUpdate.LastFeeUpdate
--WHERE Fees.FeeAmount <> Bookings.FeeCharged