我正在写一个查询,该查询计算是否为所有预订收取了正确的预订费。表的结构如下。请注意,由于我所处的环境,我无法创建任何存储过程或函数来帮助我。
预订
Date date,
CustomerId int,
BookingTypeId int,
FeeCharged money
费用
BookingTypeId int,
FeeAmount money,
EffectiveFrom date
给定以下示例数据集
,```
我如何查询数据以确保基于生效日期的每次预订收取的费用与费用表中的正确费用相符:
尝试这个
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
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。