Advice on complex logic with embedded functions.

  • Hi,

    I have a scenario were under certain circumstances I need to use the earliest start date and then the latest finish date then work out days between these dates for records that need to be grouped together by a type

    Latest finish date - earliest start date.

    In other circumstances, but using data from the same table I need to just use the start and finish date working out the days between without grouping by type.

    Finish date - start date.

    This determines the number of days another calculation needs to be greater than in order to qualify for my query. If the days between falls between certain ranges this gets me the days to be greater than called a qualifying period. The other calculation takes the start date, same as number 2 above and measures the days between.

    Actual finish - start date

    I then check to see if this number of days is > the qualifying days.

    To determine if I should use just the start / finish date or the latest start / finish date I have a function that analysis about 10 parameters to work out what the rows type are first and then I can work out which method to use.

    All the information for parameters comes from three tables. All joined with inner joins.

    At the moment I feel I have functions embedded with functions and its not clean but messy.

    Just woundering how other people tacke complex scenarios like these. One option is I can pass in the rows unique id, have a select statement in a function get everything it needs and perform the calculation, but this feels I would be selecting from the same tables twice when I can use things like Max(date) over (Id) type logic.

    I could have a view with all the complex logic and self join using the Id mentioned above but this feels it would perfom not as well.

    Because the data is from 3rd party database, we cannot add indexes to improve performance or add application logic. I can select data only.

    Thank you

    Eliza

  • This doesn't seem that complex but I can't know for sure because I know nothing of your tables and nothing of the data. As it is, my recommedation would be to "peel just one potato at a time" (divide'n'conquer) and the answer will reveal itself. Of course, you'd know what the tables and data look like.

    My other recommendation is to take a look at the article at the first link in "Helpful Links" in my signature line below. Sometimes the process of prepping the question jars something loose and you come up with your own solution. If not, it does prepare your question to get better help, usually in the form of a tested code.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Eliza (9/16/2013)


    Hi,

    All the information for parameters comes from three tables. All joined with inner joins.

    At the moment I feel I have functions embedded with functions and its not clean but messy.

    Because the data is from 3rd party database, we cannot add indexes to improve performance or add application logic. I can select data only.

    Can you create custom tables in that another database? If you're looking to document and optimize your calculation, you could set up a job that:

    1. Finds new rows not already processed

    2. Pull the necessary data into a properly indexed table (with your three inner joins) with NULL columns for your calculations

    3. Perform the document the calcs with the new columns

    4. Do what you need to with the result

  • busraker (9/17/2013)


    Eliza (9/16/2013)


    Hi,

    All the information for parameters comes from three tables. All joined with inner joins.

    At the moment I feel I have functions embedded with functions and its not clean but messy.

    Because the data is from 3rd party database, we cannot add indexes to improve performance or add application logic. I can select data only.

    Can you create custom tables in that another database? If you're looking to document and optimize your calculation, you could set up a job that:

    1. Finds new rows not already processed

    2. Pull the necessary data into a properly indexed table (with your three inner joins) with NULL columns for your calculations

    3. Perform the document the calcs with the new columns

    4. Do what you need to with the result

    Thank you for the replies. In this instance the data needs to be live as we need a real time postion of our data so we can respond. So a delay in pulling data from schedule routines would not help.

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

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