How to get MAX() with an upper bound?
Change:

MAX(created)


with a CASE expression:

MAX(CASE WHEN created &lt;= '2015-12-31' THEN created ELSE NULL END)


The ELSE NULL is not needed as it is the default. Now, if you want to return that '2015-12-31 in case no date is before the threshold date, we can use ISNULL() or COALESCE:

COALESCE( MAX(CASE WHEN created &lt;= '2015-12-31' THEN created END), '2015-12-31' )


or, if in that case, you want the next available date, even if it bigger than the threshold date:

COALESCE( MAX(CASE WHEN created &lt;= '2015-12-31' THEN created END), MIN(created) )




Note: the answer assumes that created is a DATE (or a DATETIME that doesn't have a time part). If it does have time parts, I suggest you replace the use of &lt;= with &lt; (i.e. replacing the &lt;= '2015-12-31' with &lt; '2016-01-01'). See a detailed explanation of the problem with inclusive bounds at Aaron Bertrand's blog post: What do BETWEEN and the devil have in common?