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

    LOD with Two date columns

    ammar.khwaira

      Hi

       

      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.

       

       

      Thanks