although that is a simpler way to do it ...we have tons of formulas that do different calculations.
"PRJ" stands for project. the number within the brackets is the project id which is used to identitfy the project.
however i have a request to extract the information from the ID. Please do understand that i am doing someone elses job and it is tricky for me.
Here is the fetch statement that i wrote:
DECLARE @TEMP_SINGLE_NODE AS TABLE(Node VARCHAR(max))
DECLARE @TEMP_NODE_ID AS TABLE (NodeId int, nodeTypeKey varchar(3))
DECLARE @TEMP_NODES AS TABLE
(nodeId int,
nodeName varchar(max),
targetValue1 decimal,
actualValue1 decimal,
targetValue2 decimal,
actualValue2 decimal,
targetValue3 decimal,
actualValue3 decimal,
targetValue4 decimal,
actualValue4 decimal)
---------Extract the formula----------------
DECLARE @formula VARCHAR(max);
SET @formula = (SELECT Formula FROM tb_FormulaTypes
WHERE
FormulaTypeID IN (SELECT FormulaTypeID FROM tb_KeyPerformanceIndicators
WHERE FinancialYearID=@FinancialYearID
AND IndicatorID =@IndicatorID
AND VersionID=@VersionID
AND IsActive=1
AND IsDeleted=0
AND StatusID <> 6 ));
---------Split the formula---------------------
INSERT INTO @TEMP_SINGLE_NODE SELECT VALUE FROM dbo.fn_REPORT_Split(@formula,'+')
--Extract node Id
INSERT INTO @TEMP_NODE_ID
SELECT
SUBSTRING(Node, CHARINDEX('(',Node)+1,CHARINDEX(')',Node) - CHARINDEX('(',Node) -1),
SUBSTRING(LTRIM(Node),1,3)
FROM
@TEMP_SINGLE_NODE
DECLARE Node_Cursor CURSOR
FOR
SELECT NodeId,nodeTypekey FROM @TEMP_NODE_ID
OPEN
Node_Cursor
DECLARE @NodeId INT;
DECLARE @nodeTypekey VARCHAR(3);
FETCH NEXT FROM Node_Cursor
INTO
@NodeId,@nodeTypekey
WHILE @@FETCH_STATUS = 0
BEGIN
If( @nodeTypekey ='prg')
INSERT INTO @TEMP_NODES
SELECT
programmeId,
'prg('+ CAST(ProgrammeID AS varchar)+') '+ProgrammeName,
t.TargetValue1,
t.ActualValue1,
t.TargetValue2,
t.ActualValue2,
t.TargetValue3,
t.ActualValue3,
t.TargetValue4,
t.ActualValue4
FROM
tb_Programmes prg
-- left join @TEMP_NODES n on prg.ProgrammeID=n.nodeId
INNER JOIN tb_targets t ON prg.ProgrammeID =t.ReferenceID
WHERE
ProgrammeID= @NodeId
AND FinancialYearID=@financialyearId
AND VersionID=@versionId
AND t.NodeTypeID =(SELECT NodeTypeID FROM tb_NodeTypes WHERE NodeTypeKey='programmes')
AND prg.IsActive =1
AND prg.IsDeleted=0
AND prg.StatusID <> 6
Else
If(@nodeTypekey='prj')
INSERT INTO @TEMP_NODES
SELECT
ProjectID,
ProjectName,
t.TargetValue1,
t.ActualValue1,
t.TargetValue2,
t.ActualValue2,
t.TargetValue3,
t.ActualValue3,
t.TargetValue4,
t.ActualValue4
FROM
tb_Projects prj
-- left join @TEMP_NODES n on prj.ProjectID=n.nodeId
INNER JOIN tb_targets t ON prj.ProjectID =t.ReferenceID
WHERE
ProjectID =@NodeId
AND FinancialYearID=@financialyearId
AND VersionID=@versionId
AND t.NodeTypeID = (SELECT NodeTypeID FROM tb_NodeTypes WHERE NodeTypeKey ='projects')
AND prj.IsActive =1
AND prj.IsDeleted=0
AND prj.StatusID <> 6
Else If(@nodeTypekey ='spj')
INSERT INTO @TEMP_NODES
SELECT
ActivityID,
ActivityName,
t.TargetValue1,
t.ActualValue1,
t.TargetValue2,
t.ActualValue2,
t.TargetValue3,
t.ActualValue3,
t.TargetValue4,
t.ActualValue4
FROM
tb_Activities spj
-- left join @TEMP_NODES n on spj.ActivityID=n.nodeId
INNER JOIN tb_targets t ON spj.ActivityID =t.ReferenceID
WHERE
activityID =@NodeId
AND FinancialYearID=@financialyearId
AND VersionID=@versionId
AND spj.NodeTypeID=(SELECT NodeTypeID FROM tb_NodeTypes WHERE NodeTypeKey='sub_projects')
AND t.NodeTypeID=(SELECT NodeTypeID FROM tb_NodeTypes WHERE NodeTypeKey='sub_projects')
AND spj.IsActive =1
AND spj.IsDeleted=0
AND spj.StatusID <> 6
Else if(@nodeTypekey ='act')
INSERT INTO @TEMP_NODES
SELECT
ActivityID,
'act('+CAST(ActivityID as varchar)+') '+ActivityName,
t.TargetValue1,
t.ActualValue1,
t.TargetValue2,
t.ActualValue2,
t.TargetValue3,
t.ActualValue3,
t.TargetValue4,
t.ActualValue4
FROM
tb_Activities act
-- left join @TEMP_NODES n on act.ActivityID=n.nodeId
INNER JOIN tb_targets t ON act.ActivityID =t.ReferenceID
WHERE
activityID =@NodeId
AND FinancialYearID=@financialyearId
AND VersionID=@versionId
AND act.NodeTypeID=(SELECT NodeTypeID FROM tb_NodeTypes WHERE NodeTypeKey='activities')
AND t.NodeTypeID=(SELECT NodeTypeID FROM tb_NodeTypes WHERE NodeTypeKey='activities')
AND act.IsActive =1
AND act.IsDeleted=0
AND act.StatusID <> 6
Else
INSERT INTO @TEMP_NODES
SELECT
ActivityID,
'tsk('+CAST(ActivityID as varchar)+') '+ActivityName,
t.TargetValue1,
t.ActualValue1,
t.TargetValue2,
t.ActualValue2,
t.TargetValue3,
t.ActualValue3,
t.TargetValue4,
t.ActualValue4
FROM
tb_Activities tsk
-- left join @TEMP_NODES n on tsk.ActivityID=n.nodeId
INNER JOIN tb_targets t ON tsk.ActivityID =t.ReferenceID
WHERE
activityID =@NodeId
AND FinancialYearID=@financialyearId
AND VersionID=@versionId
AND tsk.NodeTypeID=(SELECT NodeTypeID FROM tb_NodeTypes WHERE NodeTypeKey='tasks')
AND t.NodeTypeID=(SELECT NodeTypeID FROM tb_NodeTypes WHERE NodeTypeKey='tasks')
AND tsk.IsActive =1
AND tsk.IsDeleted=0
AND tsk.StatusID <> 6
FETCH NEXT FROM Node_Cursor
INTO
@NodeId,@nodeTypekey
END
CLOSE Node_Cursor
DEALLOCATE Node_Cursor
-- SELECT * FROM @TEMP_NODES ORDER BY nodeId;