function where date = last month WordPress
- SOLVED
Hello,
This is my select statement, which works great! It returns the sum for the previous calendar month as expected. But it won't work in January, 2011 :(
SELECT
SUM(T.quantity) as sum
FROM
wp_transactions T,
wp_items I,
wp_location L
WHERE
T.item_id=I.item_id
AND T.location_id=L.location_id
AND T.trans_type= 'Inventory'
AND MONTH(T.date)=MONTH(CURDATE())-1
Please correct the WHERE T.date = last month part of this statement. I have tried many 'solutions' I've found on line, none have worked.
Answers (3)
John Cotton answers:
Try this as your WHERE clause:
( YEAR(T.Date) = YEAR(CURDATE()) AND MONTH(T.Date) = MONTH(CURDATE()) - 1 ) OR
( YEAR(T.Date) = YEAR(CURDATE()) - 1 AND MONTH(T.Date) = MONTH(CURDATE()) + 11 )
Patricia Moff comments:
oh my gosh, that is so tricky, it just might work! It works when I execute it on the db, but I can't check the 'OR' unless I change the date on the server.
Have you ever used this before?
Duncan O'Neill answers:
Hi, what plugin are you using which creates the extra tables please?
cheers,
Patricia Moff comments:
Sorry, It's a plugin I am writing (it won't be released, as it's for internal office use only)
Patricia Moff comments:
Do you think this will work? It can't really be tested without changing the current date on the server, unfortunately:
AND month(T.date) = CASE month(now()) WHEN 1 THEN 12 ELSE month(now()) - 1 END
Duncan O'Neill comments:
Patricia,
I really can't tell you if that will work or not, because 1) it looks like you know more about MySQL selects than I do 2) I have no idea of the structure of your db, or the table you're working with.
All I can do is to make suggestions, which may be way off the mark, but here goes;
1) If you're not already using it, does your host provide phpmyadmin access to your database, so that you can test these SQL statements by typing them in directly?
2) I think your T.date column needs to be of data type datetime.
3) Unlikely, but could this be an issue with reserved words?
http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html
I guess you could test this by putting back-ticks ( ` ) around the date string in the select statement.
Hope this helps.
I hope this helps.
Patricia Moff comments:
Duncan,
yeah, I can execute SQL directly on the database (a life saver, btw). The T.date column is type=date. If displays as yyyy-mm-dd.
The problem with the way I have the select statement written now (I think), is it won't work in January of any given year, as 1-1=0, not 12.
As I said above, I think
AND month(T.date) = CASE month(now()) WHEN 1 THEN 12 ELSE month(now()) - 1 END
Gabriel Reguly answers:
Hi Patricia,
You can generate the last month from PHP too, as in
$last_month = date( 'n', mktime( 0, 0, 0, date('n') - 1, date('d'), date('Y') ) );
$query = '
SELECT SUM(T.quantity) as sum
FROM wp_transactions T,
wp_items I,
wp_location L
WHERE T.item_id=I.item_id
AND T.location_id=L.location_id
AND T.trans_type= "Inventory"
AND MONTH(T.date) = ' . $last_month;
This way you can test without changing the server date, as in
$last_month = date( 'n', mktime( 0, 0, 0, date('n', mktime( 0, 0, 0, 1, 1, 2011 ) ) - 1, date('d'), date('Y') ) );
This would get you december 2010.
Regards,
Gabriel