|
|
|
Forum Newbie
Group: Forum Members
Last Login: Yesterday @ 8:22:02 PM
Posts: 5,
Visits: 17
|
|
Hello,
I hope someone could help me with my task:
Weekends = Fri, Sat, Sun
Weekend days = 6, 7, 1
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!
|
|
|
|
|
Forum Guru
Group: Forum Members
Last Login: Yesterday @ 12:42:08 AM
Posts: 74,
Visits: 32
|
|
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 ;-)
Strive to success. Arun.
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: Yesterday @ 8:22:02 PM
Posts: 5,
Visits: 17
|
|
Hello Kumar,
Sorry I wasn't able to get back to you as soon as I got your reply/solution...
Anyways, this did solved my task. Thank you so much, such a genius ;P
|
|
|
|