开发者社区> 问答> 正文

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

我正在写一个查询,该查询计算是否为所有预订收取了正确的预订费。表的结构如下。请注意,由于我所处的环境,我无法创建任何存储过程或函数来帮助我。

预订

Date date,
CustomerId int,
BookingTypeId int,
FeeCharged money
费用

BookingTypeId int,
FeeAmount money,
EffectiveFrom date
给定以下示例数据集
,```  
我如何查询数据以确保基于生效日期的每次预订收取的费用与费用表中的正确费用相符:

展开
收起
祖安文状元 2020-01-05 14:48:10 455 0
1 条回答
写回答
取消 提交回答
  • 尝试这个

    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
    
    2020-01-05 14:48:28
    赞同 展开评论 打赏
问答分类:
问答地址:
问答排行榜
最热
最新

相关电子书

更多
低代码开发师(初级)实战教程 立即下载
冬季实战营第三期:MySQL数据库进阶实战 立即下载
阿里巴巴DevOps 最佳实践手册 立即下载