October 16, 2008 at 2:16 pm
I have two tables that contain data. One is a performance level table that indicates a level that should be met. In that table there are formulas to calculate.
In another table are targets where individuals should be.
I am trying to apply the individual targets to the formulas to then get a vaule that I can compare their actuals to.
In a simplified version, here is some code to generate the two table I indicate here:
CREATE TABLE #repPerfLevels (employee_number int, perf_level_measure_id int, perf_level int, min_target money, max_target money, min_formula varchar(50), max_formula varchar(50))
INSERT INTO #repPerfLevels (employee_number, perf_level_measure_id, perf_level, min_formula, max_formula)
VALUES(82, 11, 1, '0', '.65*min')
INSERT INTO #repPerfLevels (employee_number, perf_level_measure_id, perf_level, min_formula, max_formula)
VALUES(179, 11, 1, '0', '.65*min')
INSERT INTO #repPerfLevels (employee_number, perf_level_measure_id, perf_level, min_formula, max_formula)
VALUES(314, 11, 1, '0', '.65*min')
CREATE TABLE #quota (employee_number int, ytdMinQuota money, ytdminCRQuota money)
INSERT INTO #quota (employee_number, ytdMinQuota, ytdMinCRQuota)
VALUES(82, 450897, 53264)
INSERT INTO #quota (employee_number, ytdMinQuota, ytdMinCRQuota)
VALUES(179, 499789, 59878)
INSERT INTO #quota (employee_number, ytdMinQuota, ytdMinCRQuota)
VALUES(314, 352987, 25123)
What I am attempting to do is take the word 'min' in the formula contained in the #repPerfLevels table in the max_formula field and replace it with the combination of the two targets from the quota table and then put the calculation in the max_target field of the #repPerfLevels table. Once I am able to do this I can then compare it the Perf Level table to actuals to find out what level they fall into for performance.
Following was my first attempt at this. Obviously, this won't work because mas_formula is a varchar field and I am attempting to replace it with monetary values and then run a calculation.
UPDATE r
SET r.min_target = 0, r.max_target = (SELECT REPLACE(r.max_formula, 'min', q.ytdminQuota + q.ytdminCRQuota))
FROM #repPerfLevels r JOIN #quota q ON r.employee_number = q.employee_number
WHERE r.perf_level = 1
If anyone has any ideas I would certainly appreciate your input.
Thanks
October 16, 2008 at 2:25 pm
I think I understand what you are trying to do, but being a bit more of a visual based person it would help if you also provided the expected results of the process based on the sample data you have already provided.
😎
October 16, 2008 at 2:53 pm
Absolutely, makes sense.
So, given the sample data I would expect to see the following in the #repPerfLevels table after the update is run:
INSERT INTO #repPerfLevels (employee_number, perf_level_measure_id, perf_level, min_target, max_target, min_formula, max_formula)
VALUES(82, 11, 1, 0, 327704.65, '0', '.65*min')
INSERT INTO #repPerfLevels (employee_number, perf_level_measure_id, perf_level, min_target, max_target, min_formula, max_formula)
VALUES(179, 11, 1, 0, 363783.55, '0', '.65*min')
INSERT INTO #repPerfLevels (employee_number, perf_level_measure_id, perf_level, min_target, max_target, min_formula, max_formula)
VALUES(314, 11, 1, 0, 245771.50, '0', '.65*min')
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply