I have a simple table containing a history of states of a 'reader' object. The state can be ACTIVE or INACTIVE. Now I would like to have a query, which picks all readers, which where ACTIVE in a given period. The following image shows what I mean: The 'readers' with the green 'lifeline' where ACTIVE in the displayed period; so they shall be returned in the query.
reader_id | state | timestamp
1 | ACTIVE | 1467331201089
2 | ACTIVE | 1467332454545
1 | INACTIVE | 1467348875254
3 | ACTIVE | 1467350416546
1 | ACTIVE | 1467351871123
2 | INACTIVE | 1467352111545
SELECT DISTINCT a.reader FROM ReaderActivity a WHERE a.timestamp >= :startTimestamp AND a.timestamp < :endTimestamp AND a.state = 'ACTIVE'
SELECT DISTINCT a.reader FROM ReaderActivity a WHERE a.timestamp >= :startTimestamp AND a.timestamp < :endTimestamp AND a.state = 'ACTIVE' or (SELECT aa.reader FROM ReaderActivity aa where aa.reader = a.reader AND aa.timestamp < :startTimestamp AND aa.state = 'ACTIVE' GROUP BY aa.reader ORDER BY max(aa.timestamp)) != null
AND aa.state = 'ACTIVE'
The first part of your query will bring back records whose state changed to active during the time period as you wanted. The second part of the query needs some refining.
To do what you are asking, you are going to have to bring back the most recent state changes for every reader_id up until the moment of "startTimeStamp". From these, you will then simply select the ones that were listed as "ACTIVE" at that time.
Select a.reader_id From ReaderActivity a Where a.timeStamp in ( Select max(b.timeStamp) From ReaderActivity b Where b.timestamp < :startTimeStamp And b.reader_id = a.reader_id Group By b.reader_id ) and a.state = "ACTIVE"
This query will return all [reader_id]'s that were active at the start of the specified time period. You can run this inside your original query's where clause or you can just append it to the end of your query with a UNION and run a Select Distinct(reader_id) on that whole mess.
Selecting from the max value of a group in a table is always kind of quirky to me. But I hope this query helps!