Daily averages over variable date range
Posted: Sun Sep 23, 2007 6:53 am
Hey guys ... I have a database I'm working on and I'm a bit stuck for the best way to solve a database issue.
I am storing a series of values for a date range. The best example would be like a gas or electricity bill. So for the billing period between the 1st Jan and the 18th March, you used X and your bill comes to Y.
If I pull back a specific bill, thats what I should get. Easy.
But, I also need to be able to take a user specified date range and pull values for that range. This would require taking each bill in a related period, breaking it down to a daily average and then multiplying that value by the number of days from that bill are within the specified period.
My first thought is that I could just make a table that holds averages. It has an ID of a bill, and then an entry for each date the bill covers set to the value of the average for the bill. Then to pull a range is a simple query of avg($value ) of all entries where bill ID = x and date is between set values grouped by month. I don't know how to do this but I feel it should be possible.
It seems like overkill though. Maybe better is to save the average daily value with each bill. Then I could pull back the bill average and then multiply that value by the amount of days from that bill. This requires double handling though ... I think.
Or maybe this is possible in a single query.
Anyone have any suggestions on how to approach this?
One thing to note .... it's on mySQL now ... but it will eventually need to be on sqlite (it would be now but I cant seem to set it up).
Many thanks.
I am storing a series of values for a date range. The best example would be like a gas or electricity bill. So for the billing period between the 1st Jan and the 18th March, you used X and your bill comes to Y.
If I pull back a specific bill, thats what I should get. Easy.
But, I also need to be able to take a user specified date range and pull values for that range. This would require taking each bill in a related period, breaking it down to a daily average and then multiplying that value by the number of days from that bill are within the specified period.
My first thought is that I could just make a table that holds averages. It has an ID of a bill, and then an entry for each date the bill covers set to the value of the average for the bill. Then to pull a range is a simple query of avg($value ) of all entries where bill ID = x and date is between set values grouped by month. I don't know how to do this but I feel it should be possible.
It seems like overkill though. Maybe better is to save the average daily value with each bill. Then I could pull back the bill average and then multiply that value by the amount of days from that bill. This requires double handling though ... I think.
Or maybe this is possible in a single query.
Anyone have any suggestions on how to approach this?
One thing to note .... it's on mySQL now ... but it will eventually need to be on sqlite (it would be now but I cant seem to set it up).
Many thanks.