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]

Some additional considerations - My thought process on only loading the new month, or the past few new months is because 12 months of data is about 100k records. So in less than a year, I'd have over a million records just in the data table. Maybe I should always load just the most recent 12, and delete any prior matching 12?

 

[bs0d]

Any thoughts?

 

[Micron]

Kinda confusing. Maybe that's why no guesses, other than this one.
What about a table for either a) the last import date, or b) all the import dates. Then you have a textbox bound to some date field(s) (same table, or from the data itself - I can't tell which) thus you can use the built in calendar control. The query goes back 12 months from that selection. Maybe a spinner control or combo that provides the number of months to go back if you want to vary from 12.

I think I get this

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.

but cannot relate it to your grid. If I haven't helped, some sample data and expected results might get you more feedback.
I hope your tables don't look like that

 

[bs0d]

Kinda confusing. Maybe that's why no guesses, other than this one.
What about a table for either a) the last import date, or b) all the import dates. Then you have a textbox bound to some date field(s) (same table, or from the data itself - I can't tell which) thus you can use the built in calendar control. The query goes back 12 months from that selection. Maybe a spinner control or combo that provides the number of months to go back if you want to vary from 12.

I think I get this but cannot relate it to your grid. If I haven't helped, some sample data and expected results might get you more feedback.
I hope your tables don't look like that

I understand, it is kind of confusing. For what it's worth, let me try to re-state the issue as simple as possible.

Every month, I'll have a new month of data to work with. There's a chance some prior months data may change a bit due to trailing costs. So each update to the database will be 4 months of data: The (1) new month, and repeat of (3) historical months with updated values. When the data is saved, I have fields for report date, and a field for the historical date.

Most analysis I do, will look at the previous 12 months. The issue is, since I'm loading a repeat of historic months, I can't just query the last 12 months, because now there are duplicates. And through time, there may be up to 4 copies of data for the same month- the most valid being the most recent import (max(report date)). For instance, see the month of May in the chart. There's May data for each of the following report dates: May, June, July, and August. The latest & greatest for May would be the latest one, the August report.

So referring back to the illustration, if I want the most recent 12 months in August, it would be:

September -- from December report date
October -- from January report date
November -- from February report date
December -- from March report date
January -- from April report date
February -- from May report date
March -- from June report date
April -- from July report date
May, June, July, August -- from August report date.

 

[Micron]

you ought to be running an update query against old data first, not appending. For new data, an append query after the update. So it should be a 2 step process in that order so you don't have to try to separate old from new, as the new won't be there. If there is a chance that you would ever want to repeat the whole process, you'd have to provide the option to re-do the update by going back 11 months from the current month, and re-do the append.

So if this

How might I query the previous 12 months to acquire the latest data in each month?

is not clear to you yet, what I see is simply and update query that goes back 12 months from either the current month, or as mentioned above, the date span is calculated by going back x months from some table date whereby a field contains the download dates.

 

[bs0d]

you ought to be running an update query against old data first, not appending. For new data, an append query after the update. So it should be a 2 step process in that order so you don't have to try to separate old from new, as the new won't be there. If there is a chance that you would ever want to repeat the whole process, you'd have to provide the option to re-do the update by going back 11 months from the current month, and re-do the append.

So if this is not clear to you yet, what I see is simply and update query that goes back 12 months from either the current month, or as mentioned above, the date span is calculated by going back x months from some table date whereby a field contains the download dates.

Ok, thanks for the reply. I think a 12 month load each time is probably the cleanest approach. Two step: Delete any existing data for the new date range (11 months); append new data (12 months).

 

[Micron]

Well you could. But as I've said, I would take the other approach. If you delete and the append fails for any reason you lose records. Maybe all of them

 

[bs0d]

Well you could. But as I've said, I would take the other approach. If you delete and the append fails for any reason you lose records. Maybe all of them

Ok, since the report date is part of the table structure, I could append first -- then delete old.

 

[Micron]

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.