week issues

Analytics
Enthusiast

week issues

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!
2 REPLIES
Enthusiast

Re: week issues

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 ;-)
Enthusiast

Re: week issues

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