I am currently doing the below manually at the moment and wandered if it could be automated
I have a table called metrics, it lists all the metrics that run overnight against an accounting application (SQL 2008 R2)
The table holds the metric name and id, examples of which are
I am currently using the last 12 billing metrics and putting them in a query i.e. any metric beginning with Billing, the metric has a number at the end, in the above examples 2345,2547. The numbers do not go up by one exh time i.e. 2345,2346 etc. All I know is the lastest running 2547 will be higher than last running
There are tables with the same name as the Metric name, i.e. based on the example above there is a table called Billing2345 and Billing2547.
At the moment I am looking at the metrics table and getting the last 12 tables based on the number at the end of the table name and putting them in a query as below
When there is a latest version of the metric I deleted the first part of the query and add the latest table on at the end
This is all a pain.
Is there a way of automating this