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:
device1 | 2016-08-02
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