Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Advice on complex logic with embedded functions. Expand / Collapse
Author
Message
Posted Monday, September 16, 2013 4:22 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, February 09, 2014 8:54 AM
Points: 32, Visits: 77
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


Post #1495322
Posted Monday, September 16, 2013 7:03 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 9:48 AM
Points: 35,950, Visits: 30,232
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." -- 04 August 2013
(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1495345
Posted Tuesday, September 17, 2013 10:42 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, April 02, 2014 11:24 AM
Points: 17, Visits: 76
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
Post #1495585
Posted Tuesday, September 24, 2013 1:57 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, February 09, 2014 8:54 AM
Points: 32, Visits: 77
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.



Post #1498030
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse