What I need to do is to identify the number of times a specific guest should win depending on how many weekends the guest stayed.
The situation is if the guest stayed for 2 consecutive weekends (which could either be Fri+Sat or Sat+Sun), then that is already considered as 1 win. If on the next weekends the guest still hasn't checked-out, then that is already considered as 2 wins. If on the 3rd weekends the guest still hasn't checked-out, then that already is his 3rd winning... and so on as long the guest is still checked-in.
In the attached xls file, I have placed examples of guests' stay dates (which are already given), and the number of winnings (which I have to find-out).
I pray that someone will be able to help me on this... ;) thanks again!
Hi, I can suggest a simple solution based on a few assumptions as stated below. If these assumptioons are wrong then we will have to re-write this query. Assumptions: 1. If a guest checks out on a Friday then that weekend is not considered for a win. 2. If the customer checks in on a Sunday, then that weekend is not considered for a win.
If you see your data you have provided in the excel sheet, the week day # "7" has to appear either in the check in or the check out date for that weekend to get considered as a win. Hence if you break the check in and check out dates to a series of dates using the system calendar and count the # of "7" in this series per guest, you will get the total # of wins.
SELECT A.guest_id, COUNT(B.day_of_week) as num_wins FROM GUEST AS A JOIN systemcalendar AS B ON B.date BETWEEN A.check_in_day AND A.check_out_day WHERE B.day_of_week = 7 GROUP BY 1;
I coroborated this with the data you provided and it seems to work in all cases you have provided. Hope that helps. Guess you really have to have a dose of coffee now ;-)