SQL to write a condition to qualify Members with consecutive days of stay in same and different hotels

  • I have hotel A and hotel 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

    Below is the sample data and only 2 unique ResNumber per MemberId with Qualify status should be given credits.


    MemberID ResNumber HotelName MemberEmail arrivaldate departuredate  status
    601315522 33350554  CAAUBU rjj@gmail.com 8/1/2018  8/2/2018          qualify
    601315522 33350566  CAAUBU rjj@gmail.com 8/2/2018  8/3/2018          should not qualify
    601315522 33350567  BAAUBU rjj@gmail.com 8/3/2018  8/4/2018          qualify
    601315522 33350568  CAAUBU rjj@gmail.com 8/4/2018  8/5/2018          qualify
    701315522 33350559  CAAUBU hjj@gmail.com 8/4/2018  8/5/2018          qualify
    701315522 33350566  ZAAUBU hjj@gmail.com 8/5/2018  8/6/2018          qualify

  • Can you post some DDL?
    Looking at the sample data, it looks like you'd just need to look at the status column and member email.  the other columns sshouldn't matter.
    So something like:
    SELECT SUM(CASE WHEN status = 'qualify' THEN 1 ELSE 0 END) OVER (partition by MemberID) as points
    FROM <table>

    If Status isn't a column in your data set, you are looking at a gaps and islands problem.  These are interesting problems with multiple solutions depending on the dataset and requirements.

    Which version of SQL are you writing this for?  Is it actually SQL 2000 or SQL 7 or is it for a newer version?

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • sanket.wagh7689 - Monday, July 30, 2018 5:09 PM

    I have hotel A and hotel 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

    Below is the sample data and only 2 unique ResNumber per MemberId with Qualify status should be given credits.


    MemberID ResNumber HotelName MemberEmail arrivaldate departuredate  status
    601315522 33350554  CAAUBU rjj@gmail.com 8/1/2018  8/2/2018          qualify
    601315522 33350566  CAAUBU rjj@gmail.com 8/2/2018  8/3/2018          should not qualify
    601315522 33350567  BAAUBU rjj@gmail.com 8/3/2018  8/4/2018          qualify
    601315522 33350568  CAAUBU rjj@gmail.com 8/4/2018  8/5/2018          qualify
    701315522 33350559  CAAUBU hjj@gmail.com 8/4/2018  8/5/2018          qualify
    701315522 33350566  ZAAUBU hjj@gmail.com 8/5/2018  8/6/2018          qualify

    What version of SQL Server are you using?
    😎

  • Eirikur Eiriksson - Saturday, August 4, 2018 1:17 AM

    sanket.wagh7689 - Monday, July 30, 2018 5:09 PM

    I have hotel A and hotel 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

    Below is the sample data and only 2 unique ResNumber per MemberId with Qualify status should be given credits.


    MemberID ResNumber HotelName MemberEmail arrivaldate departuredate  status
    601315522 33350554  CAAUBU rjj@gmail.com 8/1/2018  8/2/2018          qualify
    601315522 33350566  CAAUBU rjj@gmail.com 8/2/2018  8/3/2018          should not qualify
    601315522 33350567  BAAUBU rjj@gmail.com 8/3/2018  8/4/2018          qualify
    601315522 33350568  CAAUBU rjj@gmail.com 8/4/2018  8/5/2018          qualify
    701315522 33350559  CAAUBU hjj@gmail.com 8/4/2018  8/5/2018          qualify
    701315522 33350566  ZAAUBU hjj@gmail.com 8/5/2018  8/6/2018          qualify

    What version of SQL Server are you using?
    😎

    Microsoft SQL Azure (RTM) - 12.0.2000.8

  • sanket.wagh7689 - Thursday, August 9, 2018 9:51 AM

    Eirikur Eiriksson - Saturday, August 4, 2018 1:17 AM

    What version of SQL Server are you using?
    😎

    Microsoft SQL Azure (RTM) - 12.0.2000.8

    Then this should be straight forward using the LAG function to compare current entry to the previous one.
    😎

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply