Those are definitely challenging issues--which is why I've delayed viewing this issue until I had some time to read it and respond...
For issue number 1, please provide a more detailed description of your current tables. I can tell that you have a table for logging each expense. Is that the only table or do you have other related tables that are part of this system? I'm thinking in terms of a report table where you have one record for each month to use to link to expenses where the month intersects the date range recorded with your expense.
For issue #2. (just the find part of it)
You have two date ranges and such ranges will intersect (if they do) in three different ways.
Date range 1 can enclose date range 2.
5/1/2012...5/31/2012 encloses the range 5/4/2012...5/7/2012
The beginning of date range 1 can fall within date range 2.
5/1/2012...5/31/2012 intersects 4/20/2012...5/5/2012
The end of date range 1 can fall within date range 2.
5/1/2012...5/31/2012 instersects 5/28/2012...6/3/2012
Heres a method that will pull up records for all three scenarios. It can be done manually or in a script if you use a pair of gobal fields for the search date range....
enter find mode
Specify >= Date1 in date start field
Specify <= Date2 in date end field
perform the find to find all records where their date range is enclosed by the specified date range
Return to find mode
Specify <= Date 1 in date start
Specify >= date 1 in date end
Use extend found set to find all records where Date 1 intersects the records' date range and add them to the found set from the first find
Return to find mode
Specify <= Date 2 in date start
Specify >= date 2 in date end
Use extend found set to find all records where Date 2 intersects the records' date range and add them to the found set from the first two finds.
Still thinking about the proportional costing issues and came up with a few more ideas...
The calculation cost period until - cost period from
will compute the number of days for your entire cost period.
Now let's say you have two global date fields, gDate1 and gDate2 that record the current time period of interest.
Let ( Period = Cost period until - cost period from ;
CostAmount * ( min ( gDate2 ; cost period until ) - max ( gDate1 ; Cost Period From ) ) / Period )
Should return the fraction of CostAmount relevant to the time intereval gDate1...gDate2.
Thus you can enter a date range in the two global date fields, run a script to perform the find, and a calculation field with the above expression will then compute the fractional cost amount.
Please note that I have not had time to test this calculation.
$x = cost per day = $amount / days of coverage
$datePeriodcost = number of days * $ x
That was easy... :)
$datePeriodCost = number of days * ($amount / days of coverage)
Consultant for hire, Here! :)
First you'd need some place to store the result so lets assume a table "A"
Next a start and stop date, the date range entries
Start = Your Value
End = Your value
Days() = the function for getting the day number out of a date, can't remember it right now
Note that a +1 or -1 may need to be inserted here or there for accuracy
DaysMonth1 = Days(Date(Month(start) + 1; 1 ; Year(start) ) - 1) - days(start)
DaysLastMonth= days(end) the function that pulls the day out of the date
In between months days(thatmonth'slastdate)
You can work out the looping, not enough coffee yet.
I think date(month ; 0 ; year) produces the last day of the month... which simplifies my daysmonth1 calc