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
_______________________________________________
UPHPU mailing list
[email protected]
http://uphpu.org/mailman/listinfo/uphpu
IRC: #uphpu on irc.freenode.net
