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

    Problem to query special data from an audited table

    Tim Bennen Newbie

      Hello!

       

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

      http://img697.imageshack.us/img697/617/featureb.jpg

      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
          Adam Warski Master

          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

          • 2. Re: Problem to query special data from an audited table
            Tim Bennen Newbie

            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.

            • 3. Re: Problem to query special data from an audited table
              Adam Warski Master

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

               

              Adam

              • 4. Re: Problem to query special data from an audited table
                Tim Bennen Newbie

                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.