Setting a date dynamically
Try using CASE expressions as you can specify qualifiers like what you're describing.
something like...

Code :

Select Count (*)
CASE when date &gt;= 1st August year X THEN enteredondate = currentyear/08/01
ELSE enteredondate= previousyear/08/01
End
from DateTable