How to get MAX() with an upper bound?

I am using the query below to generate a report. I have a requirement to generate the report so it only applies up to a maximum timestamp (such as until yesterday, 2016-08-02). To do so, I need to modify the second line of the query so that I no longer get values of eventsink.blueboxevent.created that are greater than the limit of yesterday.

For example, if I ran the report on the sample data below with a requirement to only look at the timeframe up until yesterday (2016-08-02), I would want to see a record as follows:

Desired Result

device1 | 2016-08-02

Sample Data

DeviceID | eventsink.blueboxevent.created
device1 | 2016-08-03
device1 | 2016-08-02
device1 | 2016-08-01

Current query to be modified

```
SELECT eventsink.blueboxevent.deviceid AS 'Device ID',
Max(eventsink.blueboxevent.created) AS 'Last Connection',
provisioned,
Max(softwareversion) AS 'S/W Ver.',
buildingname AS 'Name',
buildingaddr1 AS 'Address',
buildingcity AS 'City',
buildingstate AS 'State',
zipcode AS 'Zip Code',
countrycode AS 'Cnty'
FROM eventsink.blueboxevent
JOIN md.elevator
ON md.elevator.deviceid = eventsink.blueboxevent.deviceid
JOIN eventsink.blueboxmasterdataevent
ON eventsink.blueboxmasterdataevent.deviceid =
eventsink.blueboxevent.deviceid
JOIN (SELECT Min(eventenqueuedutctime) AS 'Provisioned',
deviceid
FROM eventsink.blueboxevent
WHERE id = '1'
GROUP BY deviceid) t
ON eventsink.blueboxevent.deviceid = t.deviceid
WHERE ( id = '9'
AND softwareversion LIKE '[1-2][.]%' )
GROUP BY eventsink.blueboxevent.deviceid,
provisioned,
buildingname,
buildingaddr1,

Change:

MAX(created)

with a CASE expression:

MAX(CASE WHEN created <= '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 <= '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 <= '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 <= with < (i.e. replacing the <= '2015-12-31' with < '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?

StackExchange Database Administrators Q#145747, answer score: 10