2 Replies Latest reply: Jun 18, 2017 2:17 AM by Antonio Mancini RSS

    Tracking occurrence dates and reporting on them

    Harinder Bakhshi

      Hi,

       

      I've created a daily incremental extract but I need to validate and track some occurrence IDs.

       

      So my data is in this format:

       

      DateID
      20/5/20177526-9856
      20/5/20174523-1826
      19/5/20176352-4736
      19/5/20174545-5236
      19/5/20174523-1826
      18/5/20174523-1826

       

      I'd like to be able to calculate 3 things:

       

      1. On today's date, how many occurrences are pre-existing and how many are new
      2. The number of days each occurrence has existed
      3. The average number of days an occurrence existed for

       

      So a table that looks like this:

       

      DateTotal No of OccurancesPre-Existing OccurancesNew Occurances
      20/5/2017211
      19/5/2017312
      18/5/2017101

       

      And another table that looks like:

       

      IDDays Existing
      7526-98561
      4523-18263
      6352-47361
      4545-52361

       

      I would then be able to work out the last one from the second table above.

       

      Thanks....