I have a 3rd party piece of software which records data samples from industrial equipment. This data logging software stores data in sample data tables with approximately one day's worth of samples for a particular piece of equipment. There are many pieces of equipment being monitored so the database sample tables end up looking like this:
Equipment1_SampleData_1 Equipment1_SampleData_2 Equipment1_SampleData_3 ... Equipment1_SampleData_31
So those 31 tables contain sample data for one piece of equipment over the most recent rolling 31 day period.
There is a set of those tables for each equipment type. This makes it difficult to query for a specific sample value, given a specific date/time.
I was thinking of creating a view which unions all the equipment sample tables into one big view so that I can write a function which returns a particular sample given an equipment name and date/timestamp. Is that a bad way to do this? Can you suggest other or better ways of doing this? I do not have any control over the way the 3rd party data logging/sample tool stores data.
The end result of this is that I want to be able to say: "Give me the sample value recorded for equipment N, nearest (>=) the specified date/time" and get a value back.
This is on SQL Server 2008.