March 11, 2002 at 5:53 pm
I am trying to process time series data. Specifically, I have 15-min data (96 intervals a day) that I need to manipulate (add, subtract, multiply, etc). I am looking for a way to quickly perform arithmetic in Sql Server using time series data. Currently, I am doing the arithmetic in a stored proc or cursor where I select int001, int002, ... , int096. There must be an easier way. Any help is greatly appreciated.
March 11, 2002 at 5:58 pm
So you have 96 columns? Could you post DDL for the table and maybe a couple inserts with sample data, plus a sample of the calculation you're trying to do?
Andy
March 11, 2002 at 6:06 pm
Example:
TableA has columns id, int001 ... 1nt096
TableB has columns id, int001 ... int096
I want to multiple a row in A times a row in B. I currently am doing an insert into table c as select a.int001 * b.int001, etc. I know there must be a better way to store and/or process the data. Could I convert the time series data into a single value and store it in only one column? Can I use UDFs to manipulate the data quickly?
Edited by - sdonoghu on 03/11/2002 6:07:04 PM
March 11, 2002 at 7:49 pm
Not sure that UDF would really help you, at best would just clean up the statement a little. Not sure how you could compress it all into one value, other than XML which isn't really the same thing. You could put all the code into a view or an indexed view to make working with it everywhere else a little easier. Only other option would be to change the columns to rows so that you would have a table consisting of pkey, batchid (which would could be the day), interval name, and interval value.
Are you trying to achieve speed or elegance? Or both?!
Andy
March 11, 2002 at 8:13 pm
Can you post your SP so I can fully understand what you want to accomplish. Looks like you want to just multiply TableAs columns by there counterpart in TableB.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply