3

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 
2
  • Have you tried to add something like eventsink.blueboxevent.created < <vour_value> to where condition?
    – frlan
    Aug 3, 2016 at 12:52
  • I did try that, but I think that still does a true MAX() and then crops off any results that exceed my WHERE limit. I want essentially a pseudo-MAX() where I get all of the results, but for where results exceed my upper limit, I roll back to the greatest value that does not exceed the upper limit. Does that make sense? The goal is to be able to query on a pre-defined time window in the past. eventsink.blueboxevent.created are timestamp values, and I want to get the most recent that is not more recent than the time window I am querying on. Thanks! Aug 3, 2016 at 12:55

1 Answer 1

10

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?

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.