4 Replies Latest reply on Jul 6, 2010 6:01 AM by phantomias

Problem to query special data from an audited table

phantomias Jun 13, 2010 5:30 PM

Hello!

I have a problem using Envers. This ER diagram is a part of my system:

I have a Feature and many Tasks which are mapped. The status is an integer which I audit. That is the only data field which I audit in this table. So, is there a change on the status, a new entry into the audit table will be created and I have a timestamp in the revinfo table.

For example, the feature has a time slot from

01.06.2010 - 20.06.2010 and I can get all tasks for a special Feature without a problem from the database table.

But now, I have to create a graph on the x axis are the days, on the y axis the number of tasks.

So I need to have a list in this form:

day : tasks left

01.06.2010 : 10

02.06.2010 : 8

03.06.2010 : 8

04.06.2010 : 7

05.06.2010 : 6

The tasks which are not done, have the status 3. Tasks which are done have the status 0.

How can I get this data, for each day?

The amount of left tasks, I can check with the task table. But the historical data, I have to get from the audited table.

So I need a time mapping on which day which task is set to status 0.

How can I solve this problem? Does anyone know?

Thanks a lot in advance & Best Regards.

1. Re: Problem to query special data from an audited table

adamw Jun 24, 2010 3:17 AM (in response to phantomias)

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?

Adam
Actions
2. Re: Problem to query special data from an audited table

phantomias Jun 26, 2010 6:05 AM (in response to adamw)

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?

Best Regards.
Actions
3. Re: Problem to query special data from an audited table

adamw Jul 6, 2010 2:44 AM (in response to phantomias)

I don't understand what you mean by " I have to create for each day a query" ?

Adam
Actions
4. Re: Problem to query special data from an audited table

phantomias Jul 6, 2010 6:01 AM (in response to adamw)

Adam -

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:
day1: 10
day2: 8
day3: 9

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?

Best Regards.
Actions

Go to original post