Filemaker Pro 11 - Month Report Help
I am currently working on a database which holds information regarding inpatients.
Some of the fields included are "admission" (a date) and "discharge" (also a date).
A calculation field calculates the amount of days each patient is admitted ("Length of Stay")
Further fields calculate the correct cost of each patient depending on the type of patient as shown below:
Admission Rate = Case ( Account Class = "Serving Member";Case (Length of Stay<15;681;390); Account Class = "Veteran";0;740)
Admission Revenue = Case ( Discharge = "";0;(Case ( Account Class = "Serving Member";Case (Length of Stay < 15;Length of Stay * Admission Rate;(14 * 681) + (390 * (Length of Stay - 14)));Account Class = "Veteran";0;Length of Stay * Admission Rate)))
These calculations work perfectly and produce the total expense of each inpatient over their stay.
The problem I'm having is that I'm not sure how to produce a month report (for any given month) which separates the cost into the correct month. At the moment, I use this calculation field to sort my records into a report
MonthYear = MonthName ( Admission ) & ", " & Year (Admission)
which produces something like "July, 2014", etc. for each record. This is based off the admission field however patients can span across multiple months, e.g. July to August
The cost for each day in the first month needs to be included in the first month (e.g. July) and the rest of the cost put into the summary of August. I'm not sure how to do this.
For example, if they stay from July 15th to August 15th, and they are a serving member, then the first 14 days are charged at $681 and then the rest at $390. However, all the days charged in July should be added to the July total summary and all the August days to August total. (there is nothing to stop a patient spanning more than 2 months either)
At the current time, I only have a summary field of the total of "Admission Revenue" and then a sub summary report layout sorting records by "MonthYear" to create month totals but this is counting cost from the entire length of stay to the month of admission only)
Any help in splitting this cost to the correct month would be appreciated.
If it helps, costing works as follows:
As in the admission rate calculation above, its free for veterans, $681 for first 14 days of serving member and then 390 for the rest of their stay and 740 for all other types. (All per day)
Thanks in Advanced
(If you require more information, let me know)