s/DATE_DIFF/DATEDIFF/ sorry 'bout that :)
On Thu, Jan 28, 2010 at 9:11 AM, justin <[email protected]> wrote: > On Thu, Jan 28, 2010 at 4:37 AM, Wade Preston Shearer > <[email protected]> wrote: >> I have a difficult MySQL query to write. Is there anyone that can assist? >> >> I have a users table. I have another table with things the user does. >> >> I need to select all the things the user has done within the current three >> year window since the date the user's account was created. >> >> >> Example: >> >> So, if the user's account was created in Feb 2001, they did something in Mar >> 2003, something in Nov 2009, and something else in Jan 2010, the query would >> return 2 records, because they did… >> >> . one thing in the first three years >> . nothing in the second three years >> . and two things in the third (current) three year period >> >> >> …and we only want the things that were done within the current three year >> period. >> >> >> Is that possible in a single query? >> > > If I read it correctly, it sounds like this is your logic: > > # number of days since last 3 yr mark. > x = (current date - hire date) % (365 days * 3) > > # slice you care about > select everything between (current date - x) and current date > > > MySQL would look something like this (disclaimer: i didn't actually > run this, but it looks right): > > > SELECT a.* > FROM users u > LEFT JOIN user_actions a > ON a.user_id = u.id > WHERE > u.id = ? > AND > DATE(a.created_at) > BETWEEN > DATE_SUB( > CURDATE(), > MOD( > DATE_DIFF( > CURDATE(), > DATE(u.created_at) > ), > (365 * 3) > ) DAYS > ) > AND > CURDATE() > > > > -- > justin > http://justinhileman.com > -- justin http://justinhileman.com _______________________________________________ UPHPU mailing list [email protected] http://uphpu.org/mailman/listinfo/uphpu IRC: #uphpu on irc.freenode.net
