need sql help

[eluser]theprodigy[/eluser]
I can kind of see what you are talking about, but not really. The fact that I can pay over my bill amount won't do too much to this website. Me and my GF are manually entering the bills. If I have a bill that has an amount due of $59, and I pay $60, then that's what I pay. When the next bill comes in the mail, it will show the credit of $1, and the adjusted amount due will be $58. The $58 is what I will enter as the amount due.

I'm not looking for anything full boat here. I'm not going to be building a general ledger, T-accounts, accounts payable, accounts receivable type deal. Just a manual data entry bill tracking.

I completely understand that if I pay over, then that payment is spanning multiple bills (this one and the next one), but I'm not setting this up to handle that kind of detail.

All I will be entering is what is due, regardless of why it is lower or higher than the previous month.

[eluser]jedd[/eluser]
Sure. It'd be an edge case to have the next bill prepared by the company prior to you overpaying the current bill. I used to have that happen to me fairly regularly, but I was pretty slack with my mobile bill and with the picking-up of my mail from my postoffice box.

My point is that for any given bill you can't identify a set of payments whose sum perfectly matches that bill. Wrangling some logic that says there's a set of payments that will equal or exceed each bill is messier, I think, than the approach of simply cycling through all the companies on the list, and doing a comparison of bill total to payment total on the PHP side, and flagging any deltas.

How many companies are you likely to have recorded?

I reckon there's ((n * 2) +1) SQL queries if you do it this way - pretty reasonable, given your company count is likely to be around 10, and you'll be running this set of queries a couple of times a week.

[eluser]theprodigy[/eluser]
If I understand correctly what you're saying, I don't think it will work.

Take, for example, a credit card bill. Let's say my minimum due is $150. Bill comes in, I pay the $150. At the end of the month, I have extra money left over, so I pay an additional $200 to the credit card to try to pay it down some.
Bill comes in for $150.........doesn't show up on the list because my payments exceed my total due to company, but I still have that bill to pay.

Plus, in all actuality, I don't think my current table structure will work anyway. I need to have an Account table in between Companies and Bills. I have one company that I have several accounts with. I need some way of keeping those accounts separate, and don't want to hack it by saying they are different companies.

I'm open for any new table structures that may make this easier, or even pre-written software that I can use. It has to be web-based or at least use the internet protocol. Right now, me and my GF are in different locations (different states), and we both need to be able to read / edit the records.

[eluser]jedd[/eluser]
Credit card bills are a curious one, to be sure.

They're actually two bill amounts on one bit of paper - there's the total that you owe the credit card issuer, and there's the minimum amount they're willing to accept from you this month.

Of course, that those two numbers are different is what keeps them in business, but that's beside the point.

The bill you should be tracking is actually for the total amount owing. You might need to track the two things as separate entities - the monthly 'minimum' as one bill, the total credit debt as another.

I think this assertion:

... doesn't show up on the list because my payments exceed my total due to company ...

demonstrates a profound misunderstanding of the nature of the arrangement. Again, though, the wilful and convenient obfuscation of the usury arrangement is why lenders remain wealthy, and it's why sensible people will always recommend that you pay the full amount, on time every month ... but it's kind of skewing off-topic.

Plus, in all actuality, I don't think my current table structure will work anyway. I need to have an Account table in between Companies and Bills. I have one company that I have several accounts with.

Replace companies with accounts, then. You could normalise so common company information is kept in a company table, but ultimately you're just shifting the bill:company relationship to a bill:account one.


Personally I think you're making this way more complex than it needs to be. Considering your geographical limitation, I'd suggest a google-apps spreadsheet, separate page for each account, a row for each bill & payment. Your credit card(s) can then be handled as a special case on their own page(s), and a summary page would be almost trivial to design.

[eluser]theprodigy[/eluser]
A friend of mine came up with a solution.

Code:

public function get_current($type='service')
{
    //select all bill information from bills where sum of payments is less than bills.amount
                                      
    //SELECT `c`.`name` as company, `c`.`nickname` as nickname, `c`.`website`, `b`.*, b.amount-(select sum(p2.amount) from payments p2 where p2.bills_id=b.id) leftover
    //FROM (`bills` b)
    //JOIN `companies` c ON `b`.`company_id` = `c`.`id`
    //WHERE (b.amount > (select sum(p.amount) from payments p where p.bills_id=b.id) OR (select sum(p3.amount) from payments p3 where p3.bills_id=b.id) IS NULL)
    //AND `type` = 'service'  
        
    $where = '(b.amount > (select sum(p.amount) from payments p where p.bills_id=b.id) OR (select sum(p3.amount) from payments p3 where p3.bills_id=b.id) IS NULL)';
                                    
    $this->db->select('c.name as company, c.nickname as nickname, c.website, b.*, b.amount-(select sum(p2.amount) from payments p2 where p2.bills_id=b.id) leftover');
    $this->db->from('bills b');
    $this->db->join('companies c','b.company_id = c.id');
    $this->db->where($where);
    $this->db->where('type',$type);
                              
    return $this->db->get();
}