SQLite - get dates in range and calculate dates & avg
Posted: Sun Nov 04, 2007 11:27 pm
Ok, I have a table that stores bill information for a range of time, along with some other assorted data including the total number of days covered by that bill.
The table is still under construction but is currently as shown below ...
Syntax may look a little odd, but keep in mind this is for SQLite not mySQL.
Anyhow, as you can see, bills are stored with start and end dates, along with the number of days that span represents. This data is actually from an imported electronic bill, and that number of days field is in that data set - it's mainly kept just so that no data is discarded from the original set, but it may have a purpose in the solution.
Anyhow, my current task is to pull all bills for a specific M_ID in a specific timeframe (start date - end date).
So, the search parameter might be 01/01/07 - 30/03/07, and some example dates would be ..
But the actual purpose of pulling this data is to get the total cost for the entire span. So if I were to pull the rows I need, I'd then have to find a way to calculate the number of days which are within the range in each bill, then divide the bill cost by the total number of days and multiply the result by the number of applicable days.
But I'm wondering if there isn't a cleaner solution. I can pull the average cost per day using the total days and linking to the total cost in the linked table, but I'm actually really interested if anyone can think of a way to pull the number of applicable days. I mean, if there was a way to pull that value, then I could just filter the results based on bills with applicable days instead of worrying about ranges.
Anyone have any ideas on how to do this or if it would be possible?
Many thanks
The table is still under construction but is currently as shown below ...
Code: Select all
CREATE TABLE tbl_bills (B_PK VARCHAR(12) PRIMARY KEY, M_ID INTEGER(11), bill_date DATE, start_date DATE, end_date DATE, bill_days INTEGER(3), kwh_usage INTEGER(11));Anyhow, as you can see, bills are stored with start and end dates, along with the number of days that span represents. This data is actually from an imported electronic bill, and that number of days field is in that data set - it's mainly kept just so that no data is discarded from the original set, but it may have a purpose in the solution.
Anyhow, my current task is to pull all bills for a specific M_ID in a specific timeframe (start date - end date).
So, the search parameter might be 01/01/07 - 30/03/07, and some example dates would be ..
So first I need to figure how to pull the correct records. I figure I should be able to work something with selecting the dates ... I'll have to tinker with that soon.21/12/06 - 18/01/07
19/01/07 - 20/02/07
21/02/07 - 16/03/07
17/03/07 - 20/04/07
But the actual purpose of pulling this data is to get the total cost for the entire span. So if I were to pull the rows I need, I'd then have to find a way to calculate the number of days which are within the range in each bill, then divide the bill cost by the total number of days and multiply the result by the number of applicable days.
But I'm wondering if there isn't a cleaner solution. I can pull the average cost per day using the total days and linking to the total cost in the linked table, but I'm actually really interested if anyone can think of a way to pull the number of applicable days. I mean, if there was a way to pull that value, then I could just filter the results based on bills with applicable days instead of worrying about ranges.
Anyone have any ideas on how to do this or if it would be possible?
Many thanks