Extract Data from the given

  • Guys ,

    i need to extract the information from the following Formula: prj(10517)(a*w/100)+prj(10742)(a*w/100)+prj(10804)(a*w/100)+prj(10808)(a*w/100)+prj(10809)(a*w/100)+prj(10810)(a*w/100)

    What would be the simplest and fastest way to do this...

    I did write a fetch statement but i want to do something simpler as it is a bit hectic...

    Basically i require the information from prj(10517) etc.

    Please assist.

  • (a*w/100) *(prj(10517)+prj(10742)+prj(10804)+prj(10808)+prj(10809)+prj(10810))

    I think this reduces the amount of multiplication operations to one by doing factorization, and also will probably reduce the error from having to round early.

    Other than that, without knowing what PRJ is, not sure I can help any further.

    Catch-all queries done right [/url]
    Gail Shaw's Performance Blog[/url]

  • 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;

Viewing 3 posts - 1 through 2 (of 2 total)

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