So for the business question, let divide by parts: I have a list of employees of a company. Each employee has the normal values (not dated) like, Id, Name, Birth date.
Now, for each employee, I've got the data relative to company, that is dated. For this case, lets have a bool field that says if the employee is Director or not, and this can change over time, like a promotion. So an example data would be
Name : Alberto Soares,
BirthDate : 1990-01-01,
effectiveDate : 2010-05-16,
propertyValue : false // I'm a simple employee
effectiveDate : 2011-09-22,
propertyValue : true // Because I've worked very hard, I'm now a director
So now I've this data (some more records) and I want to know, at any given date, who were the directors of the company, lets say 2010-12-20. In sql I do it like the OP, but I'm complety lost on how to do it Elastic.
I hope that my example makes sense...
Edit: On my mappings (I'm using nest) I've already nested the Items (effectiveDate with propertyValue), was this a correct thing to do?
Edit2: My problem right (for the searches that I made) is this: I want a list of all current employees that are not directors. When I do a search where propertyValue : false, and the effectiveDate < today, the record with id 1 is returned, because it has a record that matches the criteria. So the main ideia here is to get only the current is Director record, with sql I would use max(effectiveDate) to get this, but I'm completly lost on ES.