[EMAIL PROTECTED] wrote on 10/18/2005 12:01:50 PM:
> ----- Original Message -----
> From: "grKumaran" <[EMAIL PROTECTED]>
> To: <mysql@lists.mysql.com>
> Sent: Tuesday, October 18, 2005 16:08
> Subject: query help
>
>
> : Hello,
> :
> : CREATE TABLE t (
> : dt datetime
> : )
> :
> : Please assume this is the table structure and contains thousands of
> records.
> : And I want to list them only last 12 months (that mean last 1 year)
> records
> : exactly.
> :
> : For that I tried using the following query, but it list sometimes 13
> months
> : when the current date is in the middle of the month.
> :
> : SELECT * FROM t WHERE DATE_ADD(dt, INTERVAL 1 YEAR) >= NOW();
> :
> : I request you to help me. And thanking you for the consideration.
> :
>
> Thanking you all people. I think I am not clear in last mail, here I go
in
> more detail.
>
> Sample records:
> 2004-05-25
> 2004-06-25
> 2004-07-25
> 2004-08-25
> 2004-09-25
> 2004-10-25
> 2004-11-25
> 2004-12-25
> 2005-01-25
> 2005-02-25
> 2005-03-25
> 2005-04-25
> 2005-05-25
>
> Let us assume we are in any date of May month, then I want the records
> starts from June 01 to the current datetime.
>
> Required query should bring the following result
> -- if we are on 25th day or later of May month
> 2004-06-25
> 2004-07-25
> 2004-08-25
> 2004-09-25
> 2004-10-25
> 2004-11-25
> 2004-12-25
> 2005-01-25
> 2005-02-25
> 2005-03-25
> 2005-04-25
> 2005-05-25
>
> -- if we are before 25th day of May.
> 2004-06-25
> 2004-07-25
> 2004-08-25
> 2004-09-25
> 2004-10-25
> 2004-11-25
> 2004-12-25
> 2005-01-25
> 2005-02-25
> 2005-03-25
> 2005-04-25
>
> Once again thanking you all the people. And forgive me for any mistakes
in
> my English.
>
> Sincerely,
> R. Kumaran
>
>
Thank you for the better explanation. I think that you want the following:
The 12 months of data just before the beginning of the next month (all of
this month all of the way back to the "next" month for the previous year,
excluding later dates within the current month.
As an example:
a) Assume we have a table that contains daily records from 2004-01-01 to
2005-10-18.
b) You want to run a report based on the date 2005-05-25.
You have already said that you did not want to see the dates from
2004-05-26 to 2005-05025. I think what understand that you want to see is
from 2004-06-01 to 2005-05-25.
Use Michael's formula:
SET @last_month_yr = LAST_DAY(CURDATE()- INTERVAL 1 MONTH)
- INTERVAL 1 YEAR + INTERVAL 1 DAY;
To compute the earliest date and use the target date as the other end. So
for instance, for the target report date of 2005-05-25, combining
Michael's formula and your query would look like:
SET @report_target_date = '2005-05-25';
SET @last_month_yr = LAST_DAY(@report_target_date - INTERVAL 1 MONTH)
- INTERVAL 1 YEAR + INTERVAL 1 DAY;
SELECT ...
FROM ...
WHERE datefield BETWEEN @last_month_yr AND @report_target_date;
Does this do what you wanted?
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
