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

  • sanket.wagh7689

    SSC-Addicted

    Points: 431

    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

  • bmg002

    SSC-Insane

    Points: 22362

    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?

  • Eirikur Eiriksson

    SSC Guru

    Points: 182344

    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?
    😎

  • sanket.wagh7689

    SSC-Addicted

    Points: 431

    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

  • Eirikur Eiriksson

    SSC Guru

    Points: 182344

    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 5 (of 5 total)

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