hihi I am a filemaker pro 13 user on mac. I have a simple database and need some help to create a calculation or find procedure.
My database is one hotel has many rooms, and rooms have many seasonal prices. So what i am looking for is can I find a price in a certain season/ date range and if the date starts in one date range how can i assure the routine finds 3days in one range and shows me 1day in the next for instance
my database looks a bit like this, hotel-A, has Lakeview room, 1Jan2014 to 30Jun2014 the price is 200 dollar and 1July to 31Dec2014 the price is 220
the fields are room-id, start date, end date, price
with a relation to hotel, and lakeview room tables
now if somebody wants to book a room from 29Jun2014 to 2July,
the calculation is total 3 nights = (2nights * 200) + (1 * 220)
as you can imagine the date range will be extended every year as new prices come in so the calculation I am looking for needs to take care of that too. The date range can be 4 or 5 per annum covering for more expensive periods in the year etc. So not just 2 because i could potentially use an if or nested if function. I have a hotel that gave me a range of 6 date ranges already
Thanks for your help!