On Thu, Jan 28, 2010 at 11:12 AM, Wade Preston Shearer <[email protected]> wrote: > My client just changed the requirements slightly. I tried massaging your > suggested code to meet the new requirements but couldn't get it to work. > Here is the new logic: > > select all user actions with completed date after most recent tri-annual > program anniversary > > > So, if the program started on 1 Aug 2000, then the query should return all > actions completed after 1 Aug 2009. Does that make sense? > > 1 Aug 2000 = program start > 31 July 2003 = first anniversary > 31 July 2006 = second anniversary > 31 July 2009 = third and most recent anniversary
It's always best to get the algorithm down in pseudo code first, before you worry about the actual SQL. As you can see it's just the date condition that is the hard part. In this case, you might want to consider the modulus function. Action Date >= NOW - (signupdate % 3years) Translated into SQL ua.date >= NOW() - INTERVAL MOD(YEAR(NOW()) - YEAR(u.sign_up_date), 3) YEAR _______________________________________________ UPHPU mailing list [email protected] http://uphpu.org/mailman/listinfo/uphpu IRC: #uphpu on irc.freenode.net
