sql - SQL：查询过滤MemberId，如果住宿不连续（同一家酒店，入住和退房发生在同一天）
问题描述
以下是包含条件以及我要修改的代码的说明。我需要将此条件放在下面的代码中，但我无法这样做，需要一些帮助。
So you have property A and property B.
如果在 8 月 1 日至 8 月 2 日在酒店 A 预订，而在 8 月 2 日至 8 月 3 日在酒店 A 进行第二次预订，则会员不应获得多个积分。这是在同一家酒店内，退房和下次入住之间没有 24 小时。
如果在 8 月 1 日至 8 月 2 日在酒店 A 进行预订，并在 8 月 2 日至 8 月 3 日在酒店 B 进行第二次预订，则会员应获得多个积分。这是在不同的属性内，所以时间并不重要。
视觉示例：
Property A – check in 8/1 to 8/2 – qualified stay
Property A – check in 8/2 to 8/3 – non qualified
Property A – check in 8/4 to 8/5 - qualified
Property B – check in 8/5 to 8/6 – qualified
Property C – check in 8/6 to 8/29 - qualified
Property C – check in 8/30 to 9/15 – non qualified
这是我的代码：
SELECT
LP.LoyaltyMemberID, LP.MemberEmail, H.pcode, cs.TotalRevenue,
cdc.MarketSubSegment, CS.RoomNights, cs.RateType,
CS.ReservationNumber, CS.StayStatus, H.HotelStatus, cs.departuredate,
CAST(CS.ArrivalDate AS DATE) AS ArrivalDate
FROM
ODS.C_DCustomerStay AS CS
LEFT OUTER JOIN
[ODS].[MemberTransactions] AS CDC ON CDC.SourceReferenceNumber = CS.ReservationNumber
LEFT JOIN
[ODS].[Memberships] AS LP ON LP.profileID = CDC.profileID
LEFT OUTER JOIN
dbo.[Hotels] AS H ON CS.CPropertyID = H.cidcode
WHERE
CAST(ArrivalDate AS DATE) = DATEADD(day, -1, CONVERT(DATE, GETDATE())) --extracting records for yesterday
AND LoyaltyMemberID <> '' AND LoyaltyMemberID IS NOT NULL
AND RoomNights >= 1 -- Min room nights > 1
AND CAST(totalrevenue AS FLOAT) >= 1 -- Min Revenue >= 1
AND DATEDIFF(DAY, arrivaldate, departuredate) >= 1
数据如下所示，每个符合上述规则的 LoyaltyMemberid 仅 2 个唯一的预订号码将获得积分。并非所有符合条件的预订都会获得积分。
LoyaltyMemberID MemberEmail propertycode TotalRevenue MarketSubSegment RoomNights RateType ReservationNumber StayStatus rlhc_hotelstatusname arrivaldate departuredate
102282482 ljbirr@aol.com WAFEDW 118.8 PR 1 EXT1 88676 R Active 7/30/2018 7/31/2018
102282482 ljbirr@aol.com ORPEND 285.6 BR 3 WEB 119223 R Active 7/30/2018 8/2/2018
102296283 tj711@aol.com WAPOUL 246 PR 3 FDR 975372 R Active 7/30/2018 8/2/2018
102898784 JW@gmail.com WAANGE 900.9 BR 4 RNR 33401155 R Active 7/30/2018 8/3/2018
102898784 JW@gmail.com WAANGE 900.9 BR 4 RNR 33401156 R Active 7/30/2018 8/3/2018
102898784 JW@gmail.com WAANGE 937.4 BR 4 RNR 33401170 R Active 7/30/2018 8/3/2018
103723804 hmayfield@co.net IDCANY 85.48 PR 1 EX1HR 168702 R Active 7/30/2018 7/31/2018
103723804 hmayfield@co.net WAKENT 499.75 IN 4 EX1 100803 R Active 7/30/2018 8/3/2018
104157546 dfa@pn.com CAPERR 89.38 BR 1 EX1 71220 R Active 7/30/2018 7/31/2018
104337973 ralphog@eoni.com ORPEND 160 BR 2 WEB 119221 R Active 7/30/2018 8/1/2018
104408813 dennisvaughn@msn.comPAHARR 218 IN 2 GRPNP 164701 R Active 7/30/2018 8/1/2018
104420433 mahlerkelsey@me.com WAFEDW 245.1 C0 2 RNR 87476 R Active 7/30/2018 8/1/2018
104420433 mahlerkelsey@me.com WAFEDW 118.8 C0 1 EXT1 88676 R Active 7/30/2018 7/31/2018
解决方案
如果我对您的理解正确，您所说的是，如果自上次结帐以来已经过去了 24 小时窗口，则会员有资格获得某种信用。
我要指出的第一件事是您提供的示例数据不一致。
Property A – check in 8/4 to 8/5 - qualified
这是有资格获得信用的，因为从 08/03 的上一次“退房”过去了一整天。
但是你说：
Property C – check in 8/30 to 9/15 – non qualified
当财产 A 在完全相同的情况下符合条件时，这怎么可能呢？
无论如何，我相信这可能是出于疏忽。这是一个示例，我认为它将使您朝着正确的方向前进以完成所需的工作。
您可以在 SSMS 中运行以下示例：
DECLARE @data TABLE ( [member] VARCHAR(10), [hotel] VARCHAR(10), [check_in] DATETIME, [check_out] DATETIME );
INSERT INTO @data (
[member], [hotel], [check_in], [check_out]
)
VALUES
( '60135522', 'PropA', '08/01/2018', '08/02/2018' )
, ( '60135522', 'PropA', '08/02/2018', '08/03/2018' )
, ( '60135522', 'PropA', '08/04/2018', '08/05/2018' )
, ( '60135522', 'PropB', '08/05/2018', '08/06/2018' )
, ( '60135522', 'PropC', '08/06/2018', '08/29/2018' )
, ( '60135522', 'PropC', '08/30/2018', '09/15/2018' );
SELECT
MemberStays.member
, MemberStays.hotel
, MemberStays.PrevCheckOut
, MemberStays.CheckIn
, MemberStays.CheckOut
, DATEDIFF( DD, [PrevCheckOut], [CheckIn] ) PrevCheckoutDays
, CASE DATEDIFF( DD, [PrevCheckOut], [CheckIn] )
WHEN 0 THEN 'non qualified'
ELSE 'qualified'
END AS [CreditStatus]
FROM (
SELECT
data1.member
, data1.hotel
, CONVERT(
VARCHAR(10)
, LAG( [check_out], 1, NULL ) OVER ( PARTITION BY [member], [hotel] ORDER BY [member], [hotel], [check_in] )
, 101
) AS PrevCheckOut
, CONVERT( VARCHAR(10), data1.check_in, 101 ) AS CheckIn
, CONVERT( VARCHAR(10), data1.check_out, 101 ) AS CheckOut
FROM @data AS data1
) AS MemberStays
ORDER BY
[hotel], [CheckIn];
退货
+----------+-------+--------------+------------+------------+------------------+---------------+
| member | hotel | PrevCheckOut | CheckIn | CheckOut | PrevCheckoutDays | CreditStatus |
+----------+-------+--------------+------------+------------+------------------+---------------+
| 60135522 | PropA | NULL | 08/01/2018 | 08/02/2018 | NULL | qualified |
| 60135522 | PropA | 08/02/2018 | 08/02/2018 | 08/03/2018 | 0 | non qualified |
| 60135522 | PropA | 08/03/2018 | 08/04/2018 | 08/05/2018 | 1 | qualified |
| 60135522 | PropB | NULL | 08/05/2018 | 08/06/2018 | NULL | qualified |
| 60135522 | PropC | NULL | 08/06/2018 | 08/29/2018 | NULL | qualified |
| 60135522 | PropC | 08/29/2018 | 08/30/2018 | 09/15/2018 | 1 | qualified |
+----------+-------+--------------+------------+------------+------------------+---------------+
我将主要逻辑分解为一个表子查询，以使其易于查看。这里的关键是使用 SQL Server 的 LAG 函数（连同它的分区/排序）来查看成员在给定酒店住宿的先前结帐。一旦你有了它，你就可以将它与当前行的 CheckIn 进行比较，以确定两者之间经过了多少时间。从那里开始变得简单。如果经过的天数为 0（零），则它没有资格获得连续学分，否则，它有资格。
这是物业 C 的第二条记录让我失望的地方。如果我要应用您所说的逻辑，那么两个属性 C 记录都符合条件。
更新以反映指定的新结果集
我已经更新了我的示例以使用您的结果集中的更新数据。您可以从 SSMS 中运行此代码以进行审核。
-- replicate resultset definition --
DECLARE @resultset TABLE (
LoyaltyMemberID VARCHAR(10)
, MemberEmail VARCHAR(100)
, PropertyCode VARCHAR(10)
, TotalRevenue DECIMAL(18, 2)
, MarketSubSegment VARCHAR(2)
, RoomNights INT
, RateType VARCHAR(10)
, ReservationNumber VARCHAR(10)
, StayStatus VARCHAR(10)
, rlhc_HotelStatusName VARCHAR(10)
, ArrivalDate SMALLDATETIME
, DepartureDate SMALLDATETIME
);
-- insert sample data into @resultset --
INSERT INTO @resultset (
LoyaltyMemberID, MemberEmail, PropertyCode, TotalRevenue, MarketSubSegment, RoomNights, RateType, ReservationNumber, StayStatus, rlhc_HotelStatusName, ArrivalDate, DepartureDate
) VALUES
( '102282482', 'ljbirr@aol.com', 'WAFEDW', 118.8, 'PR', 1, 'EXT1', '88676', 'R', 'Active', '7/30/2018', '7/31/2018' )
, ( '102282482', 'ljbirr@aol.com', 'ORPEND', 285.6, 'BR', 3, 'WEB', '119223', 'R', 'Active', '7/30/2018', '8/2/2018' )
, ( '102296283', 'tj711@aol.com', 'WAPOUL', 246, 'PR', 3, 'FDR', '975372', 'R', 'Active', '7/30/2018', '8/2/2018' )
, ( '102898784', 'JW@gmail.com', 'WAANGE', 900.9, 'BR', 4, 'RNR', '33401155', 'R', 'Active', '7/30/2018', '8/3/2018' )
, ( '102898784', 'JW@gmail.com', 'WAANGE', 937.4, 'BR', 4, 'RNR', '33401170', 'R', 'Active', '7/30/2018', '8/3/2018' )
, ( '103723804', 'hmayfield@co.net', 'IDCANY', 85.48, 'PR', 1, 'EX1HR', '168702', 'R', 'Active', '7/30/2018', '7/31/2018' )
, ( '103723804', 'hmayfield@co.net', 'WAKENT', 499.75, 'IN', 4, 'EX1', '100803', 'R', 'Active', '7/30/2018', '8/3/2018' )
, ( '104157546', 'dfa@pn.com', 'CAPERR', 89.38, 'BR', 1, 'EX1', '71220', 'R', 'Active', '7/30/2018', '7/31/2018' )
, ( '104337973', 'ralphog@eoni.com', 'ORPEND', 160, 'BR', 2, 'WEB', '119221', 'R', 'Active', '7/30/2018', '8/1/2018' )
, ( '104408813', 'dennisvaughn@msn.com', 'PAHARR', 218, 'IN', 2, 'GRPNP', '164701', 'R', 'Active', '7/30/2018', '8/1/2018' )
, ( '104420433', 'mahlerkelsey@me.com', 'WAFEDW', 245.1, 'C0', 2, 'RNR', '87476', 'R', 'Active', '7/30/2018', '8/1/2018' )
, ( '104420433', 'mahlerkelsey@me.com', 'WAFEDW', 118.8, 'C0', 1, 'EXT1', '88676', 'R', 'Active', '7/30/2018', '7/31/2018' );
然后...
/*
SELECT data from @resultset with the following rules:
- Any stay less than 24 hours does *not* qualify for loyalty credits.
- Only 2 unique reservation numbers per LoyaltyMemberid satisfying the above rule will be given credits.
- Note: Not all reservations that qualify will get credits.
*/
SELECT
LoyaltyMemberID
, MemberEmail
, PropertyCode
, TotalRevenue
, MarketSubSegment
, RoomNights
, RateType
, ReservationNumber
, StayStatus
, rlhc_HotelStatusName
, ArrivalDate
, DepartureDate
, PrevDeparture
, DepartureSeq
-- apply business rules --
, CASE
WHEN ( DATEDIFF( DD, ArrivalDate, PrevDeparture ) = 0 ) THEN 'Not Eligible'
WHEN ( DepartureSeq > 1 ) THEN 'Not Eligible'
ELSE 'Eligible'
END AS CreditEligible
FROM (
-- perform some intital work on the base resultsel --
SELECT
MemberStays.LoyaltyMemberID
, MemberStays.MemberEmail
, MemberStays.PropertyCode
, MemberStays.TotalRevenue
, MemberStays.MarketSubSegment
, MemberStays.RoomNights
, MemberStays.RateType
, MemberStays. ReservationNumber
, MemberStays.StayStatus
, MemberStays.rlhc_HotelStatusName
, CONVERT( VARCHAR(10), MemberStays.ArrivalDate, 101) AS ArrivalDate
, CONVERT( VARCHAR(10), MemberStays.DepartureDate, 101) AS DepartureDate
, CONVERT(
VARCHAR(10),
LAG( MemberStays.DepartureDate, 1, NULL ) OVER (
PARTITION BY MemberStays.LoyaltyMemberID, MemberStays.PropertyCode, MemberStays.ReservationNumber
ORDER BY MemberStays.LoyaltyMemberID, MemberStays.PropertyCode, MemberStays.ReservationNumber, MemberStays.ArrivalDate
)
, 101
) AS PrevDeparture
, ROW_NUMBER() OVER (
PARTITION BY MemberStays.LoyaltyMemberID, MemberStays.PropertyCode, MemberStays.ReservationNumber
ORDER BY MemberStays.LoyaltyMemberID, MemberStays.PropertyCode, MemberStays.ReservationNumber, MemberStays.ArrivalDate
) AS DepartureSeq
FROM @resultset AS MemberStays
) AS LoyaltyData
ORDER BY
LoyaltyData.LoyaltyMemberID, LoyaltyData.PropertyCode, LoyaltyData.ReservationNumber, LoyaltyData.ArrivalDate;
退货
+-----------------+----------------------+--------------+--------------+------------------+------------+----------+-------------------+------------+----------------------+-------------+---------------+---------------+--------------+----------------+
| LoyaltyMemberID | MemberEmail | PropertyCode | TotalRevenue | MarketSubSegment | RoomNights | RateType | ReservationNumber | StayStatus | rlhc_HotelStatusName | ArrivalDate | DepartureDate | PrevDeparture | DepartureSeq | CreditEligible |
+-----------------+----------------------+--------------+--------------+------------------+------------+----------+-------------------+------------+----------------------+-------------+---------------+---------------+--------------+----------------+
| 102282482 | ljbirr@aol.com | ORPEND | 285.60 | BR | 3 | WEB | 119223 | R | Active | 07/30/2018 | 08/02/2018 | NULL | 1 | Eligible |
| 102282482 | ljbirr@aol.com | WAFEDW | 118.80 | PR | 1 | EXT1 | 88676 | R | Active | 07/30/2018 | 07/31/2018 | NULL | 1 | Eligible |
| 102296283 | tj711@aol.com | WAPOUL | 246.00 | PR | 3 | FDR | 975372 | R | Active | 07/30/2018 | 08/02/2018 | NULL | 1 | Eligible |
| 102898784 | JW@gmail.com | WAANGE | 900.90 | BR | 4 | RNR | 33401155 | R | Active | 07/30/2018 | 08/03/2018 | NULL | 1 | Eligible |
| 102898784 | JW@gmail.com | WAANGE | 937.40 | BR | 4 | RNR | 33401170 | R | Active | 07/30/2018 | 08/03/2018 | NULL | 1 | Eligible |
| 103723804 | hmayfield@co.net | IDCANY | 85.48 | PR | 1 | EX1HR | 168702 | R | Active | 07/30/2018 | 07/31/2018 | NULL | 1 | Eligible |
| 103723804 | hmayfield@co.net | WAKENT | 499.75 | IN | 4 | EX1 | 100803 | R | Active | 07/30/2018 | 08/03/2018 | NULL | 1 | Eligible |
| 104157546 | dfa@pn.com | CAPERR | 89.38 | BR | 1 | EX1 | 71220 | R | Active | 07/30/2018 | 07/31/2018 | NULL | 1 | Eligible |
| 104337973 | ralphog@eoni.com | ORPEND | 160.00 | BR | 2 | WEB | 119221 | R | Active | 07/30/2018 | 08/01/2018 | NULL | 1 | Eligible |
| 104408813 | dennisvaughn@msn.com | PAHARR | 218.00 | IN | 2 | GRPNP | 164701 | R | Active | 07/30/2018 | 08/01/2018 | NULL | 1 | Eligible |
| 104420433 | mahlerkelsey@me.com | WAFEDW | 245.10 | C0 | 2 | RNR | 87476 | R | Active | 07/30/2018 | 08/01/2018 | NULL | 1 | Eligible |
| 104420433 | mahlerkelsey@me.com | WAFEDW | 118.80 | C0 | 1 | EXT1 | 88676 | R | Active | 07/30/2018 | 07/31/2018 | NULL | 1 | Eligible |
+-----------------+----------------------+--------------+--------------+------------------+------------+----------+-------------------+------------+----------------------+-------------+---------------+---------------+--------------+----------------+
请注意，我无法针对您的数据库对此进行测试，但我的想法是，如果您将上述 SQL 修改为：
SELECT
* -- I didn't feel like typing out all the column names again, however you should as it is a best practice
, CASE
WHEN ( DATEDIFF( DD, ArrivalDate, PrevDeparture ) = 0 ) THEN 'Not Eligible'
WHEN ( DepartureSeq > 1 ) THEN 'Not Eligible'
ELSE 'Eligible'
END AS CreditEligible
FROM (
SELECT
LP.LoyaltyMemberID
, LP.MemberEmail
, H.pcode AS PropertyCode
, CS.TotalRevenue
, CDC.MarketSubSegment
, CS.RoomNights
, CS.RateType
, CS.ReservationNumber
, CS.StayStatus
, H.HotelStatus
, CAST( CS.departuredate AS SMALLDATETIME ) AS DepartureDate
, CAST( CS.ArrivalDate AS SMALLDATETIME ) AS ArrivalDate
, CONVERT(
VARCHAR(10),
LAG( MemberStays.DepartureDate, 1, NULL ) OVER (
PARTITION BY MemberStays.LoyaltyMemberID, MemberStays.PropertyCode, MemberStays.ReservationNumber
ORDER BY MemberStays.LoyaltyMemberID, MemberStays.PropertyCode, MemberStays.ReservationNumber, MemberStays.ArrivalDate
)
, 101
) AS PrevDeparture
, ROW_NUMBER() OVER (
PARTITION BY MemberStays.LoyaltyMemberID, MemberStays.PropertyCode, MemberStays.ReservationNumber
ORDER BY MemberStays.LoyaltyMemberID, MemberStays.PropertyCode, MemberStays.ReservationNumber, MemberStays.ArrivalDate
) AS DepartureSeq
FROM ODS.C_DCustomerStay AS CS
LEFT OUTER JOIN [ODS].[MemberTransactions] AS CDC
ON CDC.SourceReferenceNumber = CS.ReservationNumber
LEFT JOIN [ODS].[Memberships] AS LP
ON LP.profileID = CDC.profileID
LEFT OUTER JOIN dbo.[Hotels] AS H
ON CS.CPropertyID = H.cidcode
WHERE
CAST( ArrivalDate AS DATE ) = DATEADD( DD, -1, GETDATE() ) --extracting records for yesterday
AND NULLIF( LoyaltyMemberID, '' ) IS NOT NULL
AND RoomNights >= 1 -- Min room nights > 1
AND CAST(totalrevenue AS FLOAT) >= 1 -- Min Revenue >= 1
AND DATEDIFF( DD, arrivaldate, departuredate ) >= 1
) AS LoyaltyInfo
ORDER BY
LoyaltyMemberID, PropertyCode, ReservationNumber, ArrivalDate;
它应该根据我从您最初的问题中收集到的内容为您提供所需的内容。
几点注意事项：
我缩短了
AND LoyaltyMemberID <> '' AND LoyaltyMemberID IS NOT NULL
至
AND NULLIF( LoyaltyMemberID, '' ) IS NOT NULL
它用更少的代码做同样的事情。
我改变了这个
CAST(ArrivalDate AS DATE) = DATEADD(day, -1, CONVERT(DATE, GETDATE()))
至
CAST( ArrivalDate AS DATE ) = DATEADD( DD, -1, GETDATE() )
您不需要转换 GETDATE()。已经是约会了。
案子...
WHEN ( DepartureSeq > 1 ) THEN 'Not Eligible'
不包括同一预订的任何后续住宿有资格获得积分。
- 您可以修改用于确定资格的 CASE 语句以返回可以在需要时对其执行数学运算的数值。
几个想法：
» 您的数据中似乎有重复的预订，编号为 #33401156。出于演示目的，我已将其删除。
» 为什么你必须投下你的到达和离开日期？它们不是数据库中的日期列吗？
» 您应该拿起一本书或查看一些有关 SQL 最佳实践的在线培训。我会直言不讳：您的 SQL 是一堆别名不一致、（可能的）数据类型问题和不一致的大小写敏感性。在以后重构代码时，改进这一点将大大有助于让您的生活更轻松。
PS添加
, ( '102898784', 'JW@gmail.com', 'WAANGE', 225.23, 'BR', 4, 'RNR', '33401155', 'R', 'Active', '08/03/2018', '8/4/2018' )
插入到@resultset 中的值将向您展示“不符合条件”如何在您的规则集中工作。
推荐阅读
- javascript - Javascript 上传 excel 文件 (XLS/XLSX)
- angular - 模板内的 ngOnInit()
- c++ - 是否可以将类的对象声明为同一类的成员变量？
- git - 使用 Github Actions 推送到带有访问令牌的 Gitlab
- javascript - 创建多个目录并异步写入多个文件
- excel - Excel VBA根据另一个单元格的值逐行更改单元格的值
- python - 从 cmd 行中运行的 python 恢复日志
- r - 未检测到 NA
- ios - 如何将我的可发布密钥传递给 stripe_payment Flutter 插件？
- amazon-cognito - AWS Cognito 代替 Keycloak