July 30, 2018 at 5:09 pm
I have hotel A and hotel B.
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
August 3, 2018 at 2:01 pm
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.
August 4, 2018 at 1:17 am
sanket.wagh7689 - Monday, July 30, 2018 5:09 PMI 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?
😎
August 9, 2018 at 9:51 am
Eirikur Eiriksson - Saturday, August 4, 2018 1:17 AMsanket.wagh7689 - Monday, July 30, 2018 5:09 PMI 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 qualifyWhat version of SQL Server are you using?
😎
Microsoft SQL Azure (RTM) - 12.0.2000.8
August 10, 2018 at 12:12 am
sanket.wagh7689 - Thursday, August 9, 2018 9:51 AMEirikur Eiriksson - Saturday, August 4, 2018 1:17 AMWhat 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