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.