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