Hi there, I have a disgustingly organized database to extract data from. I have some experience in SQL, but not quite what I would require in order to get data in the exact form that I require for an automated extraction to work. I have built a barebones query, but it functions under an assumption that is not always true. I think I need help building a loop, but since I have never seen anything quite this bad, I think I need another opinion before I go crazy trying to build something. Any help would be appreciated.
The database is built up in a series of tables where each table represents an entity in an oil field (a well). Each of these tables has a timestamp (point type char(30) for reasons beyond my comprehension), and three variables to pull regarding the entity (one shown in the example). The trick, and the most frustrating part of this, is that each of the three variables is set up across 40 columns in the table, each with different data through time. That is, there is a variable for pressure, and it has 40 separate data points in a single row. The output I require has a timestamp for each data point, and has each data point in a single column, as such, the data has to be reorganized and 39 of the 40 data points require a synthesized timestamp (see attached
My current query functions based on a 15 second space between all data points and therefore does not display all data properly. It also lives on the wrong side of a VPN so I had to screenshot it, I apologize. It sets up to retrieve an external table name (for the well name) and a timestamp for the where statement, and then builds a table out of a bunch of union statements. I imagine that this is sub-optimal, but as mentioned, I am inexperienced with SQL.
I guess what I really need help with is, is this a ridiculous way of pulling data/is there a better way, and if not really, how do I apply the interpolation of data? I have set up the math for interpolation but have no idea how to make the logic occur in a structured manner.
Advice and instruction is appreciated.