Post reply

T SQL to build complicated time series data

  • December 7, 2021 at 12:32 am

    #3961044

    I have a database full of financial time series data like

    Symbol, Date, Price, Volume

    AAPL, 05/25/2020,150.10, 546285467

    AAPL, 05/26/2020,153.34, 465055454

    MSFT, 05/25/2020,100.10, 746285467

    MSFT, 05/26/2020,103.34, 865055454

    GOLD, 05/25/2020,2000.10, 450285467

    GOLD 05/26/2020,20010.50, 350055454

    etc

    Many symbols.

    Lets say I had an INPUT BOX on a client asp.net form that said this.

    ((Symbol1 - Symbol2)/1000)*Symbol3

    The above input box could do all these in any combination: +, -, /, *, ()

    How would a developer plan to code this in TSQL ?

    Any ideas?

     

  • December 7, 2021 at 5:54 pm

    #3961389

    Personally - I would not do this on the SQL side.  The ONLY way I can think of to do this would be with dynamic SQL and to me that sounds risky and it is going to be complicated trying to build that up.  Plus dynamic SQL introduces risk.

    Now, doing this on the application side sounds (to me) to be a bit easier, mind you a bit time consuming.  You would need to do a lot of find and replaces.  Depending on the number of rows in the source table, it may not work too as it may take too much time/memory.  If you have only a handful of symbols to work with (100 or so), I would start by pulling the table data into memory in a variable, then loop through the table to do a find and replace of symbol with the price.  Where you will hit some snags is that you have symbols showing up twice.  For example, GOLD has 2 dates and 2 prices and quantities.  If someone were to type in "GOLD+GOLD" for example, what should the result be?  The latest date value or the oldest date value or all combinations?  In the application layer, once you have the string set up to ONLY have numeric values, then there are multiple solutions which can be seen here:

    https://stackoverflow.com/questions/21750824/how-to-convert-a-string-to-a-mathematical-expression-programmatically

    Which depends on the approach you want to take.  The above link shows you multiple ways to convert a string containing a mathematical expression to a result value which you could output.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • December 7, 2021 at 6:02 pm

    #3961398

    Thanks

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply