Calculations within a date range

[iankh]

Anyone know how I would do calculations within a date range, essentially breaking down each day in range to perform a specific and different calculation with each date?

 

I need to take a range and then by date assign a value (each different for each date) and then total all the values.

 

Thanks.

[LingoJango]

Please elaborate - what kind of value, what kind of date, what you're trying to achieve, etc.

[iankh]

Please elaborate - what kind of value, what kind of date, what you're trying to achieve, etc.

 

I have another posting that is quite elaborate, but no one responded so I thought I'd make break it down into the components.

 

It is for a booking system. There is a CheckIn date and a CheckOut date. So let's say the check in is 10 March 2008 and the check out is 17 March 2008, I need to identify each date in between and then do a unique value look up for each:

 

10 March = 3

11 March = 5

12 March = 5

13 March = 8

14 March = 8

15 March = 3

16 March = 3

 

The total value for room nights in this case = 35

 

I'm not sure how I take the range and then do the necessary manipulation.

 

Thanks.

[AHunter3]

Instead of doing a lookup separately for each date BETWEEN CheckIn date and CheckOut date, how about setting up a relationship like so?:

 

CheckIn date ≤ Booking Date

AND

CheckOut date ≥ Booking Date

 

Then you harvest all the values from the related records via that relationship.

[iankh]

Instead of doing a lookup separately for each date BETWEEN CheckIn date and CheckOut date, how about setting up a relationship like so?:

 

CheckIn date ≤ Booking Date

AND

CheckOut date ≥ Booking Date

 

Then you harvest all the values from the related records via that relationship.

 

I am currently doing this and it works great, the only problem is that the table for all the records to look up is nearing 100,000 records and will increase again in a few months by another 12,000 records. In this table I currently have a record for every room type for every resort for every day of the year.

 

I'm trying to see if there might be another way to tackle the problem so that I don't end up with a daily table that is out