Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662

    Angry Unanswered: HOWTO: Write formula engine using T-SQL

    This came from our actuaries, - create a formula generation and processing engine.

    They have a dozen of well-normalized base tables that contain statistical data on competitors, broken down by state, county, city, and zip.

    The result should contain aggregated breakdown by the same set of data elements, but also include along with other things an input formula and its result for the Base Rate.

    The whole thing is a mini-Monte Carlo simmulation.

    THE TRICK:
    If the formula today looks like:

    x = ((a + b) * (c - d + e)) / (a * (c - d) * (f - d) * (e + d))

    then tomorrow it may look like:

    x = a * b * c * d * e * f

    THE REAL TRICK: It should not be based on dynamic SQL!!!

    Any thoughts?...

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Use dynamic to build a view?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    After sitting on this for a week I also started looking for excuse to present dynamic SQL, at least partially. But the reason for it to be so much NOT DESIRED lies in the history of this department and a 6.5 version of the app that was using nothing but dynamic SQL. When 7.0 came up they decided to convert, without any changes. The problem came the next morning, actually the morning after, when a process that used to run under an hour had to be terminated after running for 23 hours and not completing.

    The presented sample of the formula is actually much more complicated, and I lose myself half-way-through when trying to design a logical model to fit the current and literally guess any future representations of this and other formulas.

    It would be great to hear from someone with financial and/or statistical analysis experience how/if they conquered this situation. While waiting for a myracle I am questioning systems and business analysis people if they know what's going on in actuarial world well enough to just tell me that "The formula can change beyond recognition."

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Whoa.

    That's a helluva problem.

    Is the formula being presented as a string? I think parsing it is going to be tough enough, without even actually building the statement.

    Aren't there 3rd party tools that will do this, such as SAS? If you build this thing you would certainly have something to crow about, but I wonder if it wouldn't be reinventing the wheel as well.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Using dynamic SQL to create a view is a very different thing than using it to actually run the view. If you dynamically create the view, that view is not functionally different than one you created manually at the Query Analyzer. As such, I don't think there is any reason to go for/against this solution on that basis alone.

    There are other reporting tools that do a very good job of dealing with these kinds of problems. SQL Reporting Services is one of those tools. WebFocus from Information Builders is another.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •