On Wed, 25 Jun 2003 19:53:34 -0400
Mark Wilson <mwilson13@cox.net> wrote:
>
> On Wednesday, June 25, 2003, at 07:24 PM, MT wrote:
>
> > [snip]
> > I have this system set up with postgres and php. One of the things
> > this system does is allow the user to enter invoices. Invoice info is
> > entered into the "cart" table and the "cart_item" table. Invoices can
> > be paid in numerous ways, including by cheque. Sometimes, the cheque
> > amount will exceed the invoice total. This necessitates creating a
> > credit, which is the difference between the cheque amount and the
> > invoice amount. This amount is stored in a separate table called
> > "difference". In addition to the amount, the "difference" table
> > includes the customer's id (clientid) and the invoice id (cartid).
> >
> > [snip]
> > Now, what happens when the customer has placed several orders over a
> > period of time, and overpaid for each one? To access the pertinent
> > credit amount, I did the following which I think solved the problem:
> >
> > [snip]
>
> > This is all fine. My big problem is that I have two different sql
> > statements. One to determine if there's a credit for the invoice
> > currently being entered; another to determine if there's a credit for
> > a previously created invoice; and no way to determine when to use one
> > or the other.
> >
> > [snip]
>
> Why not create a `cumulative_credit' table (or view)? The two columns
> would be `clientid' and `amount'. `amount' would be the sum of the
> `amount' entries in the `difference' table for each `clientid'.
I don't need to create a "cumulative_credit" table for now. I just need 1 sql statement to fetch a credit amount in the
"difference"table (if it exists) that is associated with the invoice I'm either creating or reviewing.
>
Thanks,
Mark Tessier
