I have to produce a report based upon a MS SQL Server 2008 database.
The background is that the database holds measurements made on patients suffering from dementia. Measurements are made on an irregular basis but usually once per six weeks.
The measurements are used by management to work out how much resources are required to look after these patients. They want a monthly total and average for all patients they are caring for.
The attached diagram shows what i am trying to present. Patients A - E are measured and their monthly values shown in the table. You can assume a simple table that has:
However there are some difficulties.
1) Not every patient has a measurement in each month. In that case the measurement from the previous month must be used.
2) The number of patients being treated varies. For example Patient B does not start treatment until March (Patient E in February), and Patient D has been moved so should not be included in the figures for June and July.
3) If a patient is measured more than once in a month the latest measurement should be used.
4) To find out when a patient arrives at the hospital is simple - it is the date of the first measurement.
5) To find out when a patient leaves a hospital is a little more difficult - but i guess it would be possible to write a function that tested this. So every time a patient does not have a measurement in a particular month it would be wise to check if they were present at the hospital by calling the function. (patients can move in and out of the hospital so the presence of measurements further on in time does not mean they are being treated in the current month.
This probably should be worth more than 500 points... but sorry that is the max.