Complex query problem

Question

Post reply

Complex query problem

DWaldock

SSC Rookie

Points: 41
More actions
July 1, 2004 at 2:26 pm

#162908

We have a bunch of rules (30 in one table) which have to be applied to each account which meets certain criteria. We then need to record the results of those rule tests into a log table, which can then be acted upon.
I imagine the best way to do this would involve a temp table. Some of the rules are pretty complex, I have to say.
I did have a UDF which returned a table with the results for one account, but I can't work out how to use the returned data.
Ideas, hints etc.. gratefully received.
David

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

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

Antares686 SSC Guru Points: 125444 More actions · Answer 1

Owe, my head hurst.

Can you give us an example of what you mean?

Jeswanth Jaishanker SSCommitted Points: 1869 More actions · Answer 2

If you are doing the same within a stored procedure then define one table variable or a temp table as you said and put the UDF output in that table with pointer to the account and Rule table. and finaly you can return the data. after getting the result what you wanted to do with this data is upto you. Other wise post your Full reqiurement here.

--------------------------------

DWaldock SSC Rookie Points: 41 More actions · Answer 3

Not as much as my head hurts I assure you

The rules are bonus/commission calculation rules (which may change from month to month, hence being stored as database entries).

There are two types of rule:

Normal Rule: For each x of sales of product p between date a and date b (start and end dates of the given accounting period), the account gets a credit of §y.

For the normal rule, calculation is fairly simple, as we just need to establish how many of product p has been sold within the given period, then multiply the bonus by the value.

Speed Rule: When a new account is added, they have a period of z days in which to successfully achieve x sales or product p. If they achieve this, they get a credit of §y. If they achieve this, they get the opportunity achieve this bonus again, and given an extra z days to achieve it. This is repeated ad infinitum.

For the speed rule, the calculation is more complex. We first need to know if the account is in the initial qualifying period (IQP), or if they have previously achieved this bonus. If it is, then we check for the requisite number of sales of p product between account opening and now. If they are not in their IQP and have previously achieved the bonus, or have previous achieved the bonus, then the qualifiying period has to be calculated on a different basis (ie: start date + z days + (z days * previous qualifications). This then needs to be tested against their sales, and the bonus awarded as necessary.

Now obviously, all of these calculations have to be auditable, hence the log file.

Have I clarified what I mean?

I wonder if it would better to process this outside of SQL Server.

Bob Johnson Mr or Mrs. 500 Points: 530 More actions · Answer 4

I wonder if it would better to process this outside of SQL Server.

It sounds like you are trying to incorporate too much middle tier business logic into you database. I might consider using a component that would derive values / formulas from a group of look up tables to do the calculations. This way the business logic is modularized, isolated, and flexible.