Re: Fw: query help In-reply-to: <[e-mail]>

[Michael Stassen]

SGreen@stripped wrote:
> grkumaran@stripped wrote on 10/18/2005 12:01:50 PM:
> 
> 
>>----- Original Message -----
>>From: "grKumaran" <grkumaran@stripped>
>>To: <mysql@stripped>
>>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

If I understand correctly, report_target_date is always "today" (the day we 
run the query), so I'll simply use CURDATE() or NOW(), as appropriate.

I think the above query will be close, but not quite right, for three reasons:

1) Peter Brawley is right that we should take leap year into account.  That 
requires changing the order of operations:

   SET @start = LAST_DAY(CURDATE()- INTERVAL 1 YEAR)
                - INTERVAL 1 MONTH + INTERVAL 1 DAY;

2) If today is any day in May, the report is supposed to start with June 1 
of the previous year, not May 1 of the previous year.  Hence:

   SET @start = LAST_DAY(CURDATE()- INTERVAL 1 YEAR) + INTERVAL 1 DAY;

3) dt is a DATETIME, so we will miss today's entries (unless they were at 
exactly midnight) if the end of the range in the WHERE clause is a DATE 
instead of a DATETIME.

Try this instead:

   SET @start = LAST_DAY(CURDATE()- INTERVAL 1 YEAR) + INTERVAL 1 DAY;
   SELECT * FROM t
   WHERE dt BETWEEN @start and NOW();

Of course, so long as there are no rows with dt in the future, this 
simplifies to

   SET @start = LAST_DAY(CURDATE()- INTERVAL 1 YEAR) + INTERVAL 1 DAY;
   SELECT * FROM t
   WHERE dt >= @start;

Michael