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):
Thanks a lot for your answer. I now see that I forgot to mention I wanted to do the linkage in the script. I saw the link you've added but it seems to work only for predefined intervals. I'd want an interval between the min date and the date of reference; hence having one interval per date in the data. Do you know if there is any way I can apply the intervalmatch with intervals depending on the link's value?