How to Calculate the Values for a Column having Formulas , The Formula Variables being columns from the Same SQL Server Table
-
I have the table contents as shown in this picture:
screenshot
The formula column contains the the column names as variables in the formula expression.
I am trying to get a computed column Score corresponding for each emp_id,Tsk_id and processed date which would be calculated dynamically based on the formulae given in the formula column and push it to a temp table
I have tried to achieve it by dynamic SQL within a cursor using the below code which has been successful. Can Anyone suggest a better way to do it, maybe using a CTE or something ?
DECLARE @EMP_ID nVARCHAR(255)
DECLARE @TSK_ID nVARCHAR(255)
DECLARE @PROCESSEDDATE nVARCHAR(255)
DECLARE @SQLCMD nVARCHAR(max)
DECLARE @SQLTEXT nvarchar(max)
DECLARE db_cursor CURSOR FOR
SELECT EMP_ID,TSK_ID,PROCESSEDDATE from dbo.[Formula_Cal] group by EMP_ID,TSK_ID,PROCESSEDDATE
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @EMP_ID,@TSK_ID,@PROCESSEDDATE
WHILE @@FETCH_STATUS = 0
BEGIN
Set @SQLTEXT = (Select formula from dbo.[Formula_Cal] where EMP_ID=@EMP_ID and TSK_ID =@TSK_ID and PROCESSEDDATE=@PROCESSEDDATE )
Set @SQLCMD ='select emp_id,TSK_ID,convert(decimal(18,2),( ' +@SQLTEXT+ ')) As Score FROM [Formula_Cal] where TSK_ID = '+@TSK_ID+' and EMP_ID = '+@EMP_ID
--Select @SQLTEXT
--Select @SQLCMD
insert into dbo.TMP_ACHSCR(emp_id,TSK_id,Score)
Exec sp_executesql @SQLCMD
FETCH NEXT FROM db_cursor INTO @EMP_ID,@TSK_ID,@PROCESSEDDATE
END
CLOSE db_cursor
DEALLOCATE db_cursor
-
How many possible combinations are there for "formula"? You could write a CASE statement that determined which formula to use to calculate the answer... surely must be faster than a cursor...
-
there is no defined set of formulas used here , it may vary for every row. So writing formulas into a case would ask of hardcoding , aim is to do the calculation dynamically using the variable column names in the table
-
bineet.dungdung (11/23/2016)
there is no defined set of formulas used here , it may vary for every row. So writing formulas into a case would ask of hardcoding , aim is to do the calculation dynamically using the variable column names in the tableYou do have a fixed number of column names (at this point), so this will do the job.
Some of the more experienced guys may be able to improve on it.
CREATE TABLE #Data (
EMP_ID INT NOT NULL
, TSK_ID INT NOT NULL
, PROCESSEDDATE DATE NOT NULL
, ColA DECIMAL(6, 2) NOT NULL
, ColB DECIMAL(6, 2) NOT NULL
, ColC DECIMAL(6, 2) NOT NULL
, Formula VARCHAR(2000) NOT NULL
, Result DECIMAL(6, 2)
-- Note that this key is used in the dynamic SQL for the join
, PRIMARY KEY CLUSTERED (EMP_ID, TSK_ID)
);
INSERT INTO #Data ( EMP_ID, TSK_ID, PROCESSEDDATE, ColA, ColB, ColC, Formula )
SELECT src.EMP_ID, src.TSK_ID, src.PROCESSEDDATE, src.ColA, src.ColB, src.ColC, src.Formula
FROM (VALUES ( 719200, 66, '2016-10-11', 3.00, 109.00, 117.00, '@ColA/@ColB*100' )
, ( 719200, 67, '2016-10-11', 3.00, 109.00, 117.00, '@ColB' )
, ( 719200, 68, '2016-10-11', 3.00, 109.00, 117.00, '@ColC' )
, ( 719200, 69, '2016-10-11', 3.00, 109.00, 117.00, '(@ColC/@ColA)*100' )
, ( 728836, 66, '2016-10-11', 0.00, 67.00, 52.00, '(@ColA/@ColC)*100' )
) src(EMP_ID, TSK_ID, PROCESSEDDATE, ColA, ColB, ColC, Formula);
GO
DECLARE
@Sprtr VARCHAR(MAX) = ' union all '
, @Lf CHAR(1) = CHAR(10)
, @sql VARCHAR(MAX)
SELECT @sql = 'WITH cteResults (EMP_ID, TSK_ID, Rslt) AS (' + @Lf
+ STUFF( (
SELECT @Sprtr + 'SELECT ' + CONVERT(VARCHAR(20), d.EMP_ID)
+ ', ' + CONVERT(VARCHAR(20), d.TSK_ID)
+ ', ' + REPLACE(
REPLACE(
REPLACE(d.Formula
, '@ColA', CONVERT(VARCHAR(20), d.ColA))
, '@ColB', CONVERT(VARCHAR(20), d.ColB))
, '@ColC', CONVERT(VARCHAR(20), d.ColC))
+ @Lf
FROM #Data AS d
FOR XML PATH('')
), 1, LEN(@Sprtr), '')
+ ')' + @Lf
+ 'SELECT d.EMP_ID, d.TSK_ID, d.PROCESSEDDATE, d.ColA, d.ColB, d.ColC, d.Formula, r.Rslt' + @Lf
+ 'FROM #Data AS d' + @Lf
+ 'INNER JOIN cteResults AS r ON d.EMP_ID = r.EMP_ID AND d.TSK_ID = r.TSK_ID' + @Lf
+ 'ORDER BY d.EMP_ID, d.TSK_ID;'
--PRINT (@SQL);
EXEC (@SQL);
Edit: Formatted REPLACE for better readability.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply