List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:January 5 2000 5:31am
Subject:Re: previous month query
View as plain text  
>This is running off of a cron on the first day of the month.
>I want a monthly log based off the full log that just shows
>last month's data. How do you deal with the year rollover i.e.
>january - december = -11. This doesn't seem to work:
>INSERT INTO month_log SELECT client_id, SUM(count)
>FROM big_log big WHERE MONTH(NOW()) - MONTH(big.when) = 1
># handle january - december
>OR MONTH(NOW()) - MONTH(tmp.when) = -11
>GROUP BY client_id;

You want an expression that will turn 1, 2, ..., 12
into 12, 1, ... 11.  MOD(n+10,12)+1 will do it, so you
can use this in your query:

WHERE MOD(MONTH(NOW())+10,12)+1 = MONTH(big.when)

Paul DuBois, paul@stripped
previous month queryJoel Bremson5 Jan
  • Re: previous month queryPaul DuBois5 Jan