David Garamond <[EMAIL PROTECTED]> wrote:
CREATE TABLE `payments` ( `date` date NOT NULL, `payee` varchar(255), `amount` double )
The 'payments' table records the amount of money that should be paid to each person every month. But the actual cheque is only given when the total accumulated amount has reached $50 or more for that person.
I want to list the amount of money that needs to be paid by cheques.
Can I do this with in pure SQL (instead of having to create logic in programming language)?
SELECT payee, amount FROM payments GROUP BY payee HAVING amount > 49.995
Not exactly what I want. Actually I need to do a SUM of 'amount' per payee and per 'period', where period is one or more months. If in one month a person has not collected >= $50, then it will be accumulated and only paid when the amount has been sufficient.
Another example: Suppose 'bruce' makes $30 in July, $80 in August, and $40 in Sep. Normally I would pay bruce's July earning in Aug. But seeing that bruce hasn't earned $50 in July, I hold his earning until Sep. In Sep I pay him Jul & Aug's earnings ($30+$80 = $110).
Note that I will not be paying bruce's Sep earning in Oct, since in Sep bruce only earns $40. I will need to see whether in Oct bruce makes >= $10, in which case I'll need to pay him in Nov.
So 'period' can differ depending on payee and amount.
I hope I'm explaining it more clearly.
Beware using 'double' for currency; it gives roundoff errors.
Thanks for the tip. I do use DECIMAL for currencies/money. Sorry for the inappropriate type in the example.
-- dave
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
