11 Replies Latest reply on Mar 26, 2017 1:09 AM by ammar.khwaira

    LOD with Two date columns




      for each week of creation_date, i would like to count number of issues created and number of "Open issues" till the end of that week.

      (open issues is basically the sum of counter) , if i would write this  in sql for a certain week , i woul say :


      select  sum(counter) as still_open

      from    table a

      where   creation_date <= '12-mar-2017'

      and     status_change_date <= '12-mar-2017'



      if i do this for all issue_creation week, the end result will look like (forget about the notes column):



      week of creation_date   created_issues    open_till_end_of_week      notes

      ==================  ===========   ======================       =====

      5-mar-2017                          2                        1                                   still open =  B

      12-mar-2017                        3                        2                                   still open =  C, D

      19-mar-2017                        1                        2                                   still open =  D, G



      how to translate 'still_open' column into LOD expression ?  notice that issue B was CREATED in week "5-march" and was OPEN. but closed later in Week "12-March".

      also, issue C was created on week 12-march and was open, then closed in week 19-march.


      i have calculated the end of each week (based on creation date) , but i could not translate this into LOD expression which will into account the status change date

      when summing the counter.



      Note : i have to show the start date and end date of week based on creation_date.