SQL Server – How to Get MAX() with an Upper Bound

sql server

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, 
          buildingcity, 
          buildingstate, 
          zipcode, 
          countrycode 
ORDER  BY provisioned DESC

Best Answer

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?

Related Question