I have a requirement as below and please advise me on this.
Person Name XYZ
Hotel Check In Date 01/Aug/2013
Hotel Check Out Date 05/Aug/2013
When I search by giving date as 03/Aug/2013 in this particular hotel, I should see person XYZ is already a inhouse guest.
Inserting rows from 01 to 04 as Check In Date would work and if this is ok, how to handle this.
So I guess something like above or what you have mentioned would work but my confusion is that how to handle between two dates and it is not a series.
Read Henrics blg post on the issue:
An alternative solution is to use the IntervalMatch-statement. Combining your transactions with a calendar using interval match could solve your problem.
The benefit is that you dont need to create additional transaction rows.
LOAD * Inline [
Guest Name,Hotel Check In Date,Hotel Check Out Date
WeekName(Date) as Week,
Month(Date) as Month,
QuarterName(Date) as Quarter,
Year(Date) as Year;
LOAD Date(MinDate+IterNo()-1) as Date
While MinDate+IterNo()-1 <= MaxDate;
LOAD Date(Min([Hotel Check In Date])) as MinDate,
Date(Max([Hotel Check Out Date])) as MaxDate
LOAD [Hotel Check In Date], [Hotel Check Out Date]
When I select Date 04/Aug/2013 in the calender, both customers should get selected.i.e I want to know number of in-house guests in a particular day.
Would it work under your scenario ?