[UPHPU] select records with date

[justin]

On Thu, Jan 28, 2010 at 4:37 AM, Wade Preston Shearer
<wadeshearer.lists at me.com> 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