During the development of a dashboard for one of our clients I found a challange. I'm able to figure it out. I hope anyone could help me solve this issue. I attached an example with data to clarify the issue.
1.I would like to discover the runtime of dossier_schade_sleutel. Starting date is always datum_melding. The difficulty is that there are multiple lines in the change log per dossier_schade_sleutel. The event code " Schade gesloten" is the closing date. I want to measure the date in days between datum_melding and the last creation_date with the event code "Schade gesloten".
2. For the experts (this is my wish), the event code "Schade heropend" means that the dossier_schade_sleutel is reopened and active again. In best case I would like to measure the time between start date (datum melding), first closing date (creation date - event code "schade gesloten") + date reopening (creation date - event code "schade heropend") and again (creation date - event code " schade gesloten"). The dossier_schade_sleutel could be reopened more than once. I hope this is clear. Please contact me if I can clarify the issue.
