List:General Discussion« Previous MessageNext Message »
From:Christian Mack Date:June 1 1999 6:48pm
Subject:Re: date
View as plain text  
Michael Farr wrote:
> 
> Is there a way to take the value of a date, and return the value of the
> date at
> the end of that month
> eg
> 1999-04-01 + ONEMONTH() = 1999-04-30
> 
> The purpose of this is so I can group payments by month and find what a person
> had paid during one month and what they owed for that month.  A persons
> monthly
> owings are calculated on a dollar value per day.  Here is an SQL query (.. I
> know I spelt payed wrong)
> 
> SELECT (TO_DAYS(pay.datePayed) -
> TO_DAYS(p.treatmentCommenceDate))*p.daylyPayment + p.downPayment AS 'Total
> Debit', balance(SUM(pay.credit)) AS Credit, (TO_DAYS(pay.datePayed) -
> TO_DAYS(p.treatmentCommenceDate))*p.daylyPayment + p.downPayment -
> balance(SUM(pay.credit)) AS Owe, MONTHNAME(pay.datePayed) AS 'Date' FROM
> Payment pay, Patient p where p.patientId = pay.patientId AND p.patientId = 1
> GROUP BY 'Date' ORDER BY pay.datePayed;
> 
> it returns this
> +-------------+--------+--------+------+
> | Total Debit | Credit | Owe    | Date |
> +-------------+--------+--------+------+
> |      200.00 |  50.00 | 150.00 | May  |
> |      248.00 | 340.00 | -92.00 | June |
> |      494.00 | 500.00 |  -6.00 | July |
> +-------------+--------+--------+------+
> 
> it should return this (note the columns are all accumulative)
> +-------------+--------+--------+------+
> | Total Debit | Credit | Owe    | Date |
> +-------------+--------+--------+------+
> |      200.00 |  50.00 | 150.00 | May  |
> |      448.00 | 340.00 | -92.00 | June |
> |      208.00 | 500.00 |  -6.00 | July |
> +-------------+--------+--------+------+
> (Debit is the amount they owe, credit is the amount they have paid, I am not
> sure if this is correct.)
> 
> this person pays $7 per day, and started at $200 on May 1

Hi Michael

Perhaps you should read chapter '7.3.11 Date and time functions' in the manual.
There you can find what you look for.

Tschau
Christian

Thread
dateMichael Farr1 Jun
  • Re: dateChristian Mack1 Jun