Trailing Month Query

[bs0d]

I'm building a database that will host financial data by month. I've initially loaded the previous 12 months of history into the database. With each new month, I plan to add no only the new month, but updated versions of perhaps the past ~3 months as well, in the event some trailing costs have been booked. However, assuming the original dates are not removed, this complicates a query to get the most recent previous 12 months of data. This maybe best observed through the example image below:

In the above example, assume it's August, and I'd like the most recent 12 months of data. In August, I would have loaded May-Aug. But the most recent month of April is from the July import, March from the June import, February from the May import... and so on.

How might I query the previous 12 months to acquire the latest data in each month? Somehow it needs to be the max import date for each month between the min and max date selected... Since it's still under construction, advice on better managing this is welcome as well. Maybe to keep things clean, if I add ~3 trailing months, I should delete any historical records for those months as well. The only drawback would be that it would eliminate the possibility of re-creating data that would match prior work.

 

[bs0d]

Perhaps I missed a vital clue in your prior post because I'm not understanding your predilection for deleting anything. Perhaps you have a good reason for not updating instead.
At any rate, hope I was able to help at least a bit.

For what it's worth, my thought process is to delete rather than update, as the update may not always be 1:1. There could be a scenario where an account with a value existed in one of the historical months. But since then, has been re-classified in another account which would be reflected in the newest import. If I ran an update, it seems to me that the record with the deprecated value would not be affected, as that same record would not exist in the updated version.

So If I ran a query after the update, it would pull the old value, and the new value- double dipping. The only way to be sure that isn't going on, without extended efforts would be to flag the records for exclusion, or remove them entirely, being replaced by a new data set of the same time period.

 

[Micron]

Makes sense. Probably not applicable in this case either but to avoid loss of data I should have suggested using a transaction. If that's not familiar, check it out. Could be useful some day.

 

[xenou]

It's perfectly fine to delete data. That's what delete queries are for. In the rare case that something catastrophic happens (ie., power failure mid-transaction) you will probably have to restore from a backup anyway since any kind of transaction update, insert, delete) that crashes midstream will be bad news bears.