3 Replies Latest reply: Oct 10, 2014 10:16 AM by Marcus Sommer RSS

    Conditional sum assigned to value in field


      Hello,

       

      I have the following problem: I have a table with the following fields: CaseID, CaseOpenDate, CaseCloseDate.

       

      What I want to have a daily status of how many cases are currently open. For doing so, I'd have to assign a conditional sum assigned to each CaseCloseDate, in order to get the the number of cases that were open at that time. I guess I'd have to sum the cases with an open date lower or equal than the CaseClosedDate and then substract the cases with a closed date lower or equal the CaseClosedDate. I would want to have this value for every  CaseCloseDate. I though about making a loop, but I'm not sure which condition I should set the loop to.

       

      For better understanding, this is what I have at the moment (in a simplified way):

       

      Case IDCaseOpenDateCaseCloseDate
      3458945403/08/201404/08/2014
      4748754703/08/201405/08/2014
      4753602404/08/201407/08/2014
      4792354805/08/201406/08/2014
      4715740905/08/201406/08/2014
      4849057406/08/201406/08/2014
      4910954306/08/201407/08/2014

       

      So, what I'd want is to get the following:

      CaseCloseDateOpenCases
      04/08/20142
      05/08/20143
      06/08/20142
      07/08/20140

       

      Can you please help me?

       

      Best regards,


      Sergio Peschiera