I'm looking for a solution to report the history of accounts from a separate SQL. To limit the load, I take an initial snapshot that I complete at the end of each week/month with accounts where a change took place (based on different conditions). It is possible that a week/month goes by without any change to a specific account. So that account is not transferred to the separate SQL. In the end I want to show in a graph per week/month how many customers and prospects there are for that week/month (incl the possibility to click-trough/drill down, to see which accounts are selected). Which formula should I use to achieve this?
The problem lies in the fact that not every ID is mentioned in every week / month. So I have to check for each id the last mentioned value and recreate it for every week/month to report it into a graph with a timeline as x-axis.