Page 1 of 1

SQLite - get dates in range and calculate dates & avg

Posted: Sun Nov 04, 2007 11:27 pm
by Stryks
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 ...

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));
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 ..
21/12/06 - 18/01/07
19/01/07 - 20/02/07
21/02/07 - 16/03/07
17/03/07 - 20/04/07
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.

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

Posted: Mon Nov 05, 2007 4:31 am
by aaronhall
I'm not too familiar with SQLite's date functions, but it might be easier if you store dates as unix timestamps (INTEGER type). The "number of billable days" calculation goes something like

Code: Select all

floor(($date_end - $date_start)/(60*60*24))
And you can sub floor() with ceil() if you want to charge partial days as whole days.

Posted: Mon Nov 05, 2007 5:05 am
by Stryks
Thanks for the reply. That helps me to understand some of what I need to do, though I'm still having trouble seeing how I'm going to implement it to achieve what I'm after.

Maybe it would make sense to point out that it's for a graph. With the example data I showed above, you'd specify the date ranges, and it would return the cost per day for each day within the range, but the cost would be calculated from the bill which covered that day.

This makes for a pretty boring graph I guess, so the return might be best as a calendar month. Still, the idea is the same. Bill 1 covers the first x days of the first month, and the rest are covered by Bill 2. We need to know what the total cost of each bill is, and then divide that by the number of days of that bill which are in the specified range. Done across all the bills in the range, this would give the first months figures as half one value, and half another (being that they are derived from different bills). Then I'd add them all and divide by the number of days in that month and I have the average monthly cost.

But thats seeming like a pretty scary kind of exercise in code. I'm actually starting to think that it might be best to pull all the bill data for the range and rig up some kind of calendar walker to go through each day, check to make sure the current row (bill) still covers the current day, get the average and then move on to the next, grouping by month as we go. I mean, it seems as though any solution is going to need to be calendar aware in order to group into months properly anyhow, taking into account leap years etc. etc.

Thats why I was thinking that maybe I could simplify things by ... I dunno ... pulling a select showing number of days within the range given where days > 0, along with the average daily cost of the bill. But I guess I'm really just postponing the inevitable manual handling to pull this into a form usable in a graph.

Any other hints, tips or ideas are greatly appreciated everyone.