July 30, 2018 at 5:24 pm
I have hotel A and hotel B.
Visual Example: This is just a simple way of understanding which members would get a credit. The ones that qualify get one and the others don't get it. the below example is pretty straightforward except for PropertyCode C in which checkin to checkout date is 8/30-9/15, in this case the LoyaltyMemberId would get a credit just for one night as he is staying in the same hotel for consecutive dates.
PropertyCode A – check in 8/1 to 8/2 – qualified stay
PropertyCode A – check in 8/2 to 8/3 – non qualified
PropertyCode A – check in 8/4 to 8/5 - qualified
Propertycode A- check in 8/6-8/7-qualified
PropertyCode B – check in 8/5 to 8/6 – qualified
PropertyCode C – check in 8/6 to 8/29 - qualified
PropertyCode C – check in 8/30 to 9/15 – non qualified
I tried the below query to get the data i need:
SELECT x.LoyaltyMemberID, x.MemberEmail, x.propertycode,x.TotalRevenue
,x.MarketSubSegment,x.RoomNights,x.RateType,
x.ReservationNumber, x.StayStatus, x.hotelstatusname,x.arrivaldate, x.departuredate
from (select LP.LoyaltyMemberID, LP.MemberEmail, H.propertycode,
CASE
WHEN cs.totalrevenue = 'false' THEN CAST(0 AS Varchar(20))
ELSE CAST(cs.TotalRevenue AS float(20))
END AS TotalRevenue
,cdc.MarketSubSegment,CS.RoomNights,cs.RateType,
CS.ReservationNumber, CS.StayStatus, H.hotelstatusname,
CAST(CS.ArrivalDate AS DATEtime) AS ArrivalDate,cast( cs.departuredate as datetime) as DepartureDate,
ROW_NUMBER () OVER (PARTITION BY loyaltyMemberID ORDER BY ReservationNumber ASC) AS RN
FROM ODS.C_DCustomerStay AS CS
LEFT OUTER JOIN [ODS].[C_MemberTransactions] AS CDC ON CDC.SourceReferenceNumber = CS.ReservationNumber
LEFT JOIN [ODS].[C_Memberships] AS LP ON LP.profileID = CDC.profileID
LEFT OUTER JOIN dbo.[D365-Hotels] AS H ON CS.CPropertyID = H.cnidcode ) X
WHERE
CAST(ArrivalDate AS DATE)=DATEADD(day, -1, convert(date, GETDATE())) --extracting records for yesterday
AND LoyaltyMemberID <> '' AND LoyaltyMemberID IS NOT NULL
AND LoyaltyMemberID NOT IN ('603514708', '601231742')
AND StayStatus IN ('I','R','O')
AND hotelstatusname NOT IN ('Terminated', 'Active, Pending Termination', 'inactive','suspended')
and marketsubsegment not in ('CO','CT','C4','RD') --excluding MarketCodes
and RoomNights >= 1 --Min room nights > 1
and cast(totalrevenue as float) >= 1
sample data has been attached
July 31, 2018 at 12:20 am
What is your question here exactly? This looks a lot like a homework assignment; not something that the users here will be happy to simply give you an answer for. If we just give you an answer, you won't learn anything and the point of the homework is lost.
What part are you struggling with? What have you tried so far (this is very important, make sure you include it).
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 31, 2018 at 1:09 am
Thom A - Tuesday, July 31, 2018 12:20 AMWhat is your question here exactly? This looks a lot like a homework assignment; not something that the users here will be happy to simply give you an answer for. If we just give you an answer, you won't learn anything and the point of the homework is lost.What part are you struggling with? What have you tried so far (this is very important, make sure you include it).
Sorry for the incomplete post. I have updated it now.
July 31, 2018 at 1:12 am
Sample data is a lot more useful if you provide it as SQL (Create Table... Insert Into.. etc) so we can just use it rather than doing that for you. Also some idea of what you expect the output should look like helps people to clarify what you want to accomplish.
July 31, 2018 at 1:16 am
sanket.wagh7689 - Tuesday, July 31, 2018 1:09 AMThom A - Tuesday, July 31, 2018 12:20 AMWhat is your question here exactly? This looks a lot like a homework assignment; not something that the users here will be happy to simply give you an answer for. If we just give you an answer, you won't learn anything and the point of the homework is lost.What part are you struggling with? What have you tried so far (this is very important, make sure you include it).
Sorry for the incomplete post. I have updated it now.
That'smmuch better, thanks. We need to know what your expected results are now. Then we can have a look at your query and guide you to where you went wrong, and how to fix it.
andycadley - Tuesday, July 31, 2018 1:12 AMSample data is a lot more useful if you provide it as SQL (Create Table... Insert Into.. etc) so we can just use it rather than doing that for you. Also some idea of what you expect the output should look like helps people to clarify what you want to accomplish.
Andy is right here as well. It'll make it far easier for us. Have a look at the link in my signature on how to achieve this.
Thanks! 🙂
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
July 31, 2018 at 1:39 am
Thom A - Tuesday, July 31, 2018 1:16 AMsanket.wagh7689 - Tuesday, July 31, 2018 1:09 AMThom A - Tuesday, July 31, 2018 12:20 AMWhat is your question here exactly? This looks a lot like a homework assignment; not something that the users here will be happy to simply give you an answer for. If we just give you an answer, you won't learn anything and the point of the homework is lost.What part are you struggling with? What have you tried so far (this is very important, make sure you include it).
Sorry for the incomplete post. I have updated it now.
That'smmuch better, thanks. We need to know what your expected results are now. Then we can have a look at your query and guide you to where you went wrong, and how to fix it.
andycadley - Tuesday, July 31, 2018 1:12 AMSample data is a lot more useful if you provide it as SQL (Create Table... Insert Into.. etc) so we can just use it rather than doing that for you. Also some idea of what you expect the output should look like helps people to clarify what you want to accomplish.Andy is right here as well. It'll make it far easier for us. Have a look at the link in my signature on how to achieve this.
Thanks! 🙂
I have attached the expected output.
July 31, 2018 at 9:14 am
sanket.wagh7689 - Tuesday, July 31, 2018 1:39 AMI have attached the expected output.
Most people won't open an Excel file posted by a random stranger. There is a reason that "SQL Code" has it's own button at the bottom of the post/reply editing box.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply