How to identify the Reopened requests in an incremental load

[pranaview]

Hi All,

Requirement: I am extracting data directly from a database and it sort of comes as an incremental load, existing records gets updated and new ones gets appended. One record per requests. Each request has a status and Open/Closed. We show KPIs for each month for requests closed, month filter being applied on the Closed Date. Now, sometimes a request which was closed in the previous month might get reopened for some reason and gets closed again in the current month.

I have already used that request for my calculation during last month but now that request's closed date has changed which means that all the calculation will happen again and this request will be shown for the current month and it's been obviously removed from last month KPI metrics.

What the client want is that any request that gets reopened in the coming months should not be considered again for that new month it was closed the second time but it should always be considered under the month it was closed the first time.

For Ex: Request 3464 gets opened in Aug and Closed in Aug, it's been already considered for the calculations. Now it gets reopened in Sep and gets closed in Sep. I have to somehow ignore this new status and Closed Date change and keep using the Status and Closed date from Aug. The problem here is that i don't have that historic record in the database, i only have Sep record for it and hence it's showing in Sep KPIs.

I was thinking if there's any way i can flag such change and ignore any update once the request gets closed for the first time, like creating snapshots for every month and comparing it, I'm really not sure how to do this so any help or suggestion will be really appreciated.

Thanks!

[edwin]

it appears you DB does not contain information you require as the data is overwritten ("existing records gets updated").
you have two options: 
1 which to m is the rt option is to ask the DB team to add some flag/field that will for example tell you the date the first time it was opened.  i say this should be on DB side as it appears to be an important information the business needs and is absent from the DB
2 next obvious option which you did allude to: create a QVD that stores the first time or all the times the request was opened/closed.  the minimum of course is the request.  so if the request is in the QVD - this means to exclude it in next run

hope that makes sense