CODEWITHSUNDEEP
sqlsql-server
Sank_J
Sank_J

Reputation: 77

SQL: Query to filter MemberId if stay is not consecutive (same hotel, check in and check out occur on the same day)

Below is the explanation to include the condition along with the code I want to modify. I need to put this condition in my code below, but I am unable to do so and need some help.

So you have property A and property B.

A Member should not earn multiple credits if a reservation is booked at property A for 8/1 to 8/2 and a second reservation at property A for 8/2 to 8/3. This is within the same property and there is not 24 hours in between the checkout and next check in.

A Member should earn multiple credits if a reservation is booked at property A for 8/1 to 8/2 and a second reservation at property B for 8/2 to 8/3. This is within a different property so timing doesn’t matter.

Visual Example:

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

This is my code:

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

Data is shown below and only 2 unique reservation numbers per LoyaltyMemberid satisfying the above rule will be given credits.Not all reservations that qualify will get credits.

LoyaltyMemberID MemberEmail propertycode    TotalRevenue    MarketSubSegment    RoomNights  RateType    ReservationNumber   StayStatus  rlhc_hotelstatusname    arrivaldate departuredate
102282482   [email protected]      WAFEDW  118.8   PR  1   EXT1    88676       R       Active  7/30/2018   7/31/2018
102282482   [email protected]      ORPEND  285.6   BR  3   WEB     119223      R       Active  7/30/2018   8/2/2018
102296283   [email protected]       WAPOUL  246     PR  3   FDR     975372      R       Active  7/30/2018   8/2/2018
102898784   [email protected]        WAANGE  900.9   BR  4   RNR     33401155    R       Active  7/30/2018   8/3/2018
102898784   [email protected]        WAANGE  900.9   BR  4   RNR     33401156    R       Active  7/30/2018   8/3/2018
102898784   [email protected]        WAANGE  937.4   BR  4   RNR     33401170    R       Active  7/30/2018   8/3/2018
103723804   [email protected]    IDCANY  85.48   PR  1   EX1HR   168702      R       Active  7/30/2018   7/31/2018
103723804   [email protected]    WAKENT  499.75  IN  4   EX1     100803      R       Active  7/30/2018   8/3/2018
104157546   [email protected]          CAPERR  89.38   BR  1   EX1     71220       R       Active  7/30/2018   7/31/2018
104337973   [email protected]    ORPEND  160     BR  2   WEB     119221      R       Active  7/30/2018   8/1/2018
104408813   [email protected]  218     IN  2   GRPNP   164701      R       Active  7/30/2018   8/1/2018
104420433   [email protected] WAFEDW  245.1   C0  2   RNR     87476       R       Active  7/30/2018   8/1/2018
104420433   [email protected] WAFEDW  118.8   C0  1   EXT1    88676       R       Active  7/30/2018   7/31/2018

Upvotes: 1

Views: 101

Answers (1)

critical_error
critical_error

Reputation: 6706

If I am understanding you correctly, what you are stating is that if a 24-hour window has gone by since the previous checkout then a member is qualified for some sort of credit.

First thing I am going to point out is the inconsistency in the sample data you provided.

Property A – check in 8/4 to 8/5 - qualified

This is qualified for a credit because a full day passed from the previous "check out" of 08/03.

But then you say:

Property C – check in 8/30 to 9/15 – non qualified

How can this be when Property A qualifies under the exact same circumstance?

Regardless, I moved ahead believing this perhaps on oversight. Here is an example that I think will get you moving in the right direction to accomplish what you need.

You can run the following example in 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];

Returns

+----------+-------+--------------+------------+------------+------------------+---------------+
|  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     |
+----------+-------+--------------+------------+------------+------------------+---------------+

I broke the main logic into a table subquery to keep it simple for viewing purposes. The key here is using SQL Server's LAG function (coupled with its partitioning/ordering) to look at the previous checkout for a member's stay on a given hotel. Once you have that, you can then compare it against the current row's CheckIn to determine how much time has passed between the two. From there it gets simple. If the days passed is 0 (zero) then it does not qualify for consecutive credits, otherwise, it does.

This is where the second record for Property C threw me off. If I am to apply the logic you state, both Property C records qualify.

UPDATED TO REFLECT NEW RESULTSET SPECIFIED

I have updated my example to use the updated data from your resultset. You can run this code from within SSMS for review.

-- 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', '[email protected]', 'WAFEDW', 118.8, 'PR', 1, 'EXT1', '88676', 'R', 'Active', '7/30/2018', '7/31/2018' )
, ( '102282482', '[email protected]', 'ORPEND', 285.6, 'BR', 3, 'WEB', '119223', 'R', 'Active', '7/30/2018', '8/2/2018' )
, ( '102296283', '[email protected]', 'WAPOUL', 246, 'PR', 3, 'FDR', '975372', 'R', 'Active', '7/30/2018', '8/2/2018' )
, ( '102898784', '[email protected]', 'WAANGE', 900.9, 'BR', 4, 'RNR', '33401155', 'R', 'Active', '7/30/2018', '8/3/2018' )
, ( '102898784', '[email protected]', 'WAANGE', 937.4, 'BR', 4, 'RNR', '33401170', 'R', 'Active', '7/30/2018', '8/3/2018' )
, ( '103723804', '[email protected]', 'IDCANY', 85.48, 'PR', 1, 'EX1HR', '168702', 'R', 'Active', '7/30/2018', '7/31/2018' )
, ( '103723804', '[email protected]', 'WAKENT', 499.75, 'IN', 4, 'EX1', '100803', 'R', 'Active', '7/30/2018', '8/3/2018' )
, ( '104157546', '[email protected]', 'CAPERR', 89.38, 'BR', 1, 'EX1', '71220', 'R', 'Active', '7/30/2018', '7/31/2018' )
, ( '104337973', '[email protected]', 'ORPEND', 160, 'BR', 2, 'WEB', '119221', 'R', 'Active', '7/30/2018', '8/1/2018' )
, ( '104408813', '[email protected]', 'PAHARR', 218, 'IN', 2, 'GRPNP', '164701', 'R', 'Active', '7/30/2018', '8/1/2018' )
, ( '104420433', '[email protected]', 'WAFEDW', 245.1, 'C0', 2, 'RNR', '87476', 'R', 'Active', '7/30/2018', '8/1/2018' )
, ( '104420433', '[email protected]', 'WAFEDW', 118.8, 'C0', 1, 'EXT1', '88676', 'R', 'Active', '7/30/2018', '7/31/2018' );

Then...

/*
    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;

Returns

+-----------------+----------------------+--------------+--------------+------------------+------------+----------+-------------------+------------+----------------------+-------------+---------------+---------------+--------------+----------------+
| LoyaltyMemberID |     MemberEmail      | PropertyCode | TotalRevenue | MarketSubSegment | RoomNights | RateType | ReservationNumber | StayStatus | rlhc_HotelStatusName | ArrivalDate | DepartureDate | PrevDeparture | DepartureSeq | CreditEligible |
+-----------------+----------------------+--------------+--------------+------------------+------------+----------+-------------------+------------+----------------------+-------------+---------------+---------------+--------------+----------------+
|       102282482 | [email protected]       | ORPEND       | 285.60       | BR               |          3 | WEB      |            119223 | R          | Active               | 07/30/2018  | 08/02/2018    | NULL          |            1 | Eligible       |
|       102282482 | [email protected]       | WAFEDW       | 118.80       | PR               |          1 | EXT1     |             88676 | R          | Active               | 07/30/2018  | 07/31/2018    | NULL          |            1 | Eligible       |
|       102296283 | [email protected]        | WAPOUL       | 246.00       | PR               |          3 | FDR      |            975372 | R          | Active               | 07/30/2018  | 08/02/2018    | NULL          |            1 | Eligible       |
|       102898784 | [email protected]         | WAANGE       | 900.90       | BR               |          4 | RNR      |          33401155 | R          | Active               | 07/30/2018  | 08/03/2018    | NULL          |            1 | Eligible       |
|       102898784 | [email protected]         | WAANGE       | 937.40       | BR               |          4 | RNR      |          33401170 | R          | Active               | 07/30/2018  | 08/03/2018    | NULL          |            1 | Eligible       |
|       103723804 | [email protected]     | IDCANY       | 85.48        | PR               |          1 | EX1HR    |            168702 | R          | Active               | 07/30/2018  | 07/31/2018    | NULL          |            1 | Eligible       |
|       103723804 | [email protected]     | WAKENT       | 499.75       | IN               |          4 | EX1      |            100803 | R          | Active               | 07/30/2018  | 08/03/2018    | NULL          |            1 | Eligible       |
|       104157546 | [email protected]           | CAPERR       | 89.38        | BR               |          1 | EX1      |             71220 | R          | Active               | 07/30/2018  | 07/31/2018    | NULL          |            1 | Eligible       |
|       104337973 | [email protected]     | ORPEND       | 160.00       | BR               |          2 | WEB      |            119221 | R          | Active               | 07/30/2018  | 08/01/2018    | NULL          |            1 | Eligible       |
|       104408813 | [email protected] | PAHARR       | 218.00       | IN               |          2 | GRPNP    |            164701 | R          | Active               | 07/30/2018  | 08/01/2018    | NULL          |            1 | Eligible       |
|       104420433 | [email protected]  | WAFEDW       | 245.10       | C0               |          2 | RNR      |             87476 | R          | Active               | 07/30/2018  | 08/01/2018    | NULL          |            1 | Eligible       |
|       104420433 | [email protected]  | WAFEDW       | 118.80       | C0               |          1 | EXT1     |             88676 | R          | Active               | 07/30/2018  | 07/31/2018    | NULL          |            1 | Eligible       |
+-----------------+----------------------+--------------+--------------+------------------+------------+----------+-------------------+------------+----------------------+-------------+---------------+---------------+--------------+----------------+

Mind you, I cannot test this against your database, but my thoughts are if you modify your above SQL to:

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;

It should give you what you need based on what I've gathered from your initial question.

Couple of notes:

I shortened

AND LoyaltyMemberID <> '' AND LoyaltyMemberID IS NOT NULL

to

AND NULLIF( LoyaltyMemberID, '' ) IS NOT NULL

It does the same thing with less code.

I changed this

CAST(ArrivalDate AS DATE) = DATEADD(day, -1, CONVERT(DATE, GETDATE()))

to

CAST( ArrivalDate AS DATE ) = DATEADD( DD, -1, GETDATE() )

You don't need to convert GETDATE(). It is already a date.

The CASE...

WHEN ( DepartureSeq > 1 ) THEN 'Not Eligible'

excludes any subsequent stays from the same reservation from being eligible for credit.

  • You can modify the CASE statement used for determining eligibility to return numeric values that you can perform math on if needed.

Couple of thoughts:

» You appear to have a duplicate reservation in your data with #33401156. I have removed it for this demonstrations purposes.

» Why are you having to cast your arrival and departure dates? Are they not date columns in your database?

» You should pick up a book or look into some online training in regard to SQL Best Practices. I'm going to be blunt: Your SQL is a mess of alias inconsistencies, (possible) datatype issues and inconsistent case sensitivity. Improving this will go a long ways toward making your life easier when it comes to refactoring code later on down the road.

P.S.Adding

, ( '102898784', '[email protected]', 'WAANGE', 225.23, 'BR', 4, 'RNR', '33401155', 'R', 'Active', '08/03/2018', '8/4/2018' )

to the values inserted into @resultset will demonstrate you how a "Not Eligible" works within your ruleset.

Upvotes: 1

Related Questions