May 28, 2007 at 3:38 am
Hi all (SQL Server 2000),
The user can enter a global formula in my app such as: Location * RiskFactor / NearestObject * Last inspections result....
What would be the best way to handle working out the result and storing it against the record?
The location is held against a related table and the riskfactor and nearest object are stored in the table I want to update. Last Inspection is held in a child table (the record may have multiple records so I need to get the values of the last inspection which occurred). Any of the values may be changed by the user at anytime. I was thinking about putting triggers on each of the tables involved then loading all records which need to be recalculated into a cursor then stepping through them one at a time to resolve each value and substitute it into the formula to work out the result then update each in turn - I realise this will be slow but fail to see any other way to accomplish this.
Any suggestions?
Thanks
CCB
May 28, 2007 at 9:09 am
If you're building the app, I'd write the calc into a stored proc and have the insert/update and the calc/update occur at the same time.
Are reports retrieving the values? Can you also calculate them in the report or is that slow?
However you do it, you might want to write a process to check every night and make sure you don't have mis-calculated values.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply