Maybe you can create a revisions of entity query, where you set the revisions timestamp to be between two given values and status equal to 0?
Thanks a lot for your reply.
But so I have to create for each day a query? Is that right?
I have the where condition, that is the time period (one day) and the status = 0. And I combine the tables revisions and the task_aud table?
Is that right?
I don't understand what you mean by " I have to create for each day a query" ?
thanks a lot for your reply.
I have to get the historized data per day in a summary.
So I think I have to write a sql statement for each day?
Perhaps I try to make it easier to understand, the first example is too complicated.
I have 10 rows of items in a database table. One column is an integer value for the status. The status is 0 if the item is finished, or another integer value if it is not finished. This column is audited.
So, on the first day, my result is 10, because of 10 rows.
On the second day I finished two items, so I have 8 rows -> result = 8
Also possible is that an item will switch from finish back to open, so on the third day, I have for example:
9 rows -> result = 9
And I have to calculate this for example at the third day, but I have to get this mapping:
So, what I can do: I do a count on all items in the original database table, so I will always get in my example the number 10.
But then I have to "ask" the audited table: "Tell me how many items has the status=0 on day1?" "how many items has the status=0 on day2, ..."
So I have the total number and I can subtract the number:
on day1: 0 -> 10-0 = 10
on day2: 2 -> 10-2 = 8
on day3: 1 -> 10-1 = 9
So, how can I write the SQL statement to get this data?