Why am I getting ‘Error converting data type nvarchar to numeric’ [closed]

Can someone tell me why this Stored Procedure is erroring during Execute? –

(5134 rows affected)  *Msg 8114, Level 16, State 5, Procedure sp_PBI_GetProjectMetrics, Line 65 [Batch Start Line 2] Error converting data type nvarchar to numeric.* 
USE [Local_Test_Database] GO  /****** Object:  StoredProcedure [dbo].[sp_PBI_GetProjectMetrics]    Script Date: 10/8/2021 12:32:51 PM ******/ SET ANSI_NULLS ON GO  SET QUOTED_IDENTIFIER ON GO  Alter PROCEDURE [dbo].[sp_PBI_GetProjectMetrics]  AS BEGIN       SELECT tf.CL_INVESTMENT_ID,         task.CHARGE_CODE_GROUP,         SUM(CASE WHEN task.CHARGE_CODE_GROUP = 'Billable' THEN daily_hours ELSE NULL END) Billable,         SUM(CASE WHEN task.CHARGE_CODE_GROUP = 'Non-Billable' THEN daily_hours ELSE NULL END) NonBillable,                       max(tf.LastRefreshed) LastRefreshed     INTO #tf     FROM Iodw_cl_rpt_vw_pio p     LEFT JOIN iodw_cl_time_fact tf on tf.CL_INVESTMENT_ID = p.CL_INVESTMENT_ID     LEFT JOIN Iodw_cl_task_dim task on tf.cl_task_id = task.cl_task_id     WHERE --1/8/2021 Changed from LIKE '%Digital Channels%' to exclude APAC - Digital Channels          p.FD_IMP_PORT1_DESC = 'Digital Channels'        AND p.IS_TEMPLATE = 0         GROUP BY tf.CL_INVESTMENT_ID, CHARGE_CODE_GROUP      SELECT  [CL_STATUS_REPORT_ID]             ,[CL_INVESTMENT_ID]             ,[INVESTMENT_ID]             ,[COP_REPORT_DATE]             ,[COP_REPORT_UPDATE]             ,[FD_OVERALL_STATUS]             ,[COP_SCHEDULE_STATUS_DESC]             ,[COP_SCHEDULE_EXP]             ,[COP_SCOPE_STATUS_DESC]             ,[COP_SCOPE_EXP]             ,[COP_COST_EFT_STATUS_DESC]             ,[COP_EFFORT_EXP]             ,ROW_NUMBER() OVER (PARTITION BY cl_investment_ID ORDER BY COP_REPORT_DATE DESC) RNUM         INTO #StatusReport         FROM [DC_Prime].[dbo].[Iodw_cl_status_report]      SELECT P.CL_investment_id,         COUNT(DISTINCT Issue.Issue_id) OpenIssueCount     INTO #OpenIssues     FROM Iodw_cl_rpt_vw_pio p         LEFT JOIN iodw_cl_issue issue on issue.cl_investment_id = p.cl_investment_id             AND ISNULL(ISSUE.status_Desc, 'X') NOT IN ('Closed', 'Resolved')     WHERE --1/8/2021 Changed from LIKE '%Digital Channels%' to exclude APAC - Digital Channels          p.FD_IMP_PORT1_DESC = 'Digital Channels'        AND p.IS_TEMPLATE = 0     GROUP BY P.cl_investment_id      SELECT P.CL_investment_id,         COUNT(DISTINCT risk.Risk_ID) OpenRiskCount     INTO #OpenRisks     FROM Iodw_cl_rpt_vw_pio p         LEFT JOIN iodw_cl_risk risk on risk.cl_investment_id = p.cl_investment_id             AND ISNULL(risk.status_Desc, 'X') NOT IN ('Closed', 'Resolved')     WHERE --1/8/2021 Changed from LIKE '%Digital Channels%' to exclude APAC - Digital Channels          p.FD_IMP_PORT1_DESC = 'Digital Channels'        AND p.IS_TEMPLATE = 0             GROUP BY P.cl_investment_id      CREATE CLUSTERED INDEX ix_tempCIndexAft ON #tf (CL_INVESTMENT_ID);     CREATE CLUSTERED INDEX ix_tempCIndexAftSR ON #StatusReport (CL_INVESTMENT_ID);     CREATE NONCLUSTERED INDEX ix_tempCIndexAftSRRNUM ON #StatusReport (RNUM);     CREATE CLUSTERED INDEX ix_tempCIndexAftOI ON #OpenIssues (CL_INVESTMENT_ID);     CREATE CLUSTERED INDEX ix_tempCIndexAftOR ON #OpenRisks (CL_INVESTMENT_ID);      SELECT          P.INVESTMENT_ID 'PR Code',         P.INVESTMENT_NAME 'Project Name',            P.INVESTMENT_DESC 'Investment Description',          P.FD_IMP_PORT1_DESC 'Impacted Portfolio 1',         CASE WHEN P.INVESTMENT_ID like 'OW%' THEN 'Other Work' ELSE p.OBJ_REQUEST_TYPE_DESC END 'Project Type',         p.OBJ_REQUEST_CATEGORY_DESC 'Project Sub-Type',         CASE WHEN P.INVESTMENT_ID like 'OW%' THEN 'NA' ELSE p.TECH_OWNER_FULL_NAME END 'Delivery Owner',         P.MANAGER_FULL_NAME 'Project Manager',         CASE WHEN PD.COMMITTED_DELIVERY_DATE < GETDATE() AND ISNULL(P.PROGRESS_DESC, 'x') <> 'Completed' THEN '#e68f96' ELSE 'Grey' END 'Highlight Target Delivery',         PD.COMMITTED_DELIVERY_DATE 'Target Delivery Date',         PD.COMMITTED_START_DATE 'Target Start',         PD.COMMITTED_FINISH_DATE 'Target Finish',         P.STATUS_DESC 'Project Status',         P.WRK_STATUS_DESC 'Project Work Status',         p.PROGRESS_DESC 'Project Progress',          ISNULL(P.FD_OVERALL_STATUS, 'No Status') 'Overall Status',           P.IS_ACTIVE 'Active Project',         p.COMPANY_NAME 'Client',         P.FD_BILL_METHOD_DESC 'Billing Method',         p.FD_ALT_WRK_FLW_TYPE_DESC 'Alternative Workflow',         CASE P.FD_OVERALL_STATUS WHEN 'On Track' Then '#32CD32' WHEN 'Minor Variance' THEN '#f5e44c' WHEN 'Significant Variance' THEN 'Salmon' ELSE 'LightGrey' END 'Overall Status Hightlight',         CASE WHEN ISNULL(p.COMPANY_NAME COLLATE Latin1_General_CS_AS, 'INTERNAL') = 'INTERNAL' THEN 0 ELSE 1 END 'Client Delivery',         iom.SPONSOR_3 'MC - 2',         iom.TECHNOLOGY_LEADER 'Delivery Leader',         iom.AppDevCategory 'App Dev Category',         ISNULL(AM.Value, 'All Others') EMR,              ISNULL(AM.Value, CASE WHEN P.INVESTMENT_ID like 'OW%' THEN 'Other Work' ELSE p.OBJ_REQUEST_TYPE_DESC END) 'Review Category',         --Calculating in DAX. Raw data is at resource level and SUM isn't accurate         --im.TotalPlannedEffortHours - im.TotalApprovedEffortHours  'Hours Planned vs. Approved',                --im.LTDActualCost - im.TotalPlannedEffortCost 'Cost Planned vs LTD',                    --im.[PlannedBenefit(3FullYears)] - TotalPlannedEffortCost  'Planned Benefit vs Cost',         ima.Platform 'Platform 1',         ima.LEVEL3_NAME 'Platform 1 Level 3',         iml.BenefitingBusinessLine 'Benefitting Business Line 1',         iml.BusinessLine 'Business Line 1',         CONVERT(float, im.TotalPlannedEffortCost) TotalPlannedEffortCost,         CONVERT(float, im.TotalPlannedEffortHours) TotalPlannedEffortHours,         CONVERT(float, im.TotalApprovedEffortHours) TotalApprovedEffortHours,         CONVERT(float, im.TotalApprovedEffortCost) TotalApprovedEffortCost,         CONVERT(float, im.[Planned Benefit(3 Full Years)]) PlannedBenefit,         CONVERT(float, im.TotalRemainingHours) 'Total Remaining Hours',         CONVERT(float, im.LTDActualCost) 'LTD Cost',         CONVERT(float, im.LTDActualHours) 'LTD Actuals',         CONVERT(float, im.LTDActualHours) + CONVERT(float, im.TotalRemainingHours) 'EAC Hours (LTD Actuals + Remaining Hours)',         CASE WHEN ISNULL(im.TotalApprovedEffortHours, '0') = '0'             THEN 'No Approved Hours'              ELSE CONVERT(varchar, ROUND(100 * CONVERT(float, im.LTDActualHours) + CONVERT(float, im.TotalRemainingHours) / NULLIF(CONVERT(float, im.TotalApprovedEffortHours),0), 0)) + '%'         END 'Hours Variance % (EAC / Approved Hours) ',         CASE WHEN CONVERT(float, im.LTDActualHours) = 0 AND CONVERT(float, im.TotalApprovedEffortHours) = 0             THEN NULL              WHEN CONVERT(float, im.LTDActualHours) > 0 AND CONVERT(float, im.TotalApprovedEffortHours) = 0             THEN 'No Approved Hours'             WHEN CONVERT(float, im.LTDActualHours) >= CONVERT(float, im.TotalApprovedEffortHours) * 1.25              THEN 'Over by >= 25%'              WHEN CONVERT(float, im.LTDActualHours) > CONVERT(float, im.TotalApprovedEffortHours)             THEN 'Over by < 25%'             ELSE NULL         END 'LTD 25% Over Approved Hours',           CASE WHEN CONVERT(float, im.LTDActualHours) = 0 AND CONVERT(float, im.TotalApprovedEffortHours) = 0             THEN NULL             WHEN CONVERT(float, im.LTDActualHours) > 0 AND CONVERT(float, im.TotalApprovedEffortHours) = 0             THEN '#E68F96'             WHEN CONVERT(float, im.LTDActualHours) >= CONVERT(float, im.TotalApprovedEffortHours) * 1.25              THEN '#DE6A73'              WHEN CONVERT(float, im.LTDActualHours) > CONVERT(float, im.TotalApprovedEffortHours)             THEN '#E68F96'             ELSE NULL         END 'LTD 25% Over Approved Hours Highlight',                     CASE WHEN CONVERT(float, im.LTDActualHours) + CONVERT(float, im.TotalRemainingHours) = 0 AND CONVERT(float, im.TotalApprovedEffortHours) = 0             THEN NULL             WHEN CONVERT(float, im.LTDActualHours) + CONVERT(float, im.TotalRemainingHours) > 0 AND CONVERT(float, im.TotalApprovedEffortHours) = 0             THEN 'No Approved Hours'             WHEN CONVERT(float, im.LTDActualHours) + CONVERT(float, im.TotalRemainingHours) >= CONVERT(float, im.TotalApprovedEffortHours) * 1.15              THEN 'Over by >= 15%'              WHEN CONVERT(float, im.LTDActualHours) + CONVERT(float, im.TotalRemainingHours) > CONVERT(float, im.TotalApprovedEffortHours)             THEN 'Over by < 15%'             ELSE NULL         END 'EAC 15% Over Approved Hours',           CASE WHEN CONVERT(float, im.LTDActualHours) + CONVERT(float, im.TotalRemainingHours) = 0 AND CONVERT(float, im.TotalApprovedEffortHours) = 0             THEN NULL             WHEN CONVERT(float, im.LTDActualHours) + CONVERT(float, im.TotalRemainingHours) > 0 AND CONVERT(float, im.TotalApprovedEffortHours) = 0             THEN '#E68F96'             WHEN CONVERT(float, im.LTDActualHours) + CONVERT(float, im.TotalRemainingHours) >= CONVERT(float, im.TotalApprovedEffortHours) * 1.15              THEN '#DE6A73'              WHEN CONVERT(float, im.LTDActualHours) + CONVERT(float, im.TotalRemainingHours) > CONVERT(float, im.TotalApprovedEffortHours)             THEN '#E68F96'             ELSE NULL         END 'EAC 15% Over Approved Hours Highlight',         CASE WHEN CONVERT(float, im.LTDActualHours) > CONVERT(float, im.TotalApprovedEffortHours)              OR CONVERT(float, im.TotalPlannedEffortCost) > CONVERT(float, im.[Planned Benefit(3 Full Years)])             OR CONVERT(float, im.LTDActualHours) + CONVERT(float, im.TotalRemainingHours) > CONVERT(float, im.TotalApprovedEffortHours) * 1.15              THEN 1             ELSE 0         END 'Cost Effort Tab',         --CASE WHEN im.TotalApprovedEffortCost = '0' THEN 'No Approved Cost'         --  ELSE CONVERT(varchar, ROUND(100 * (CONVERT(float, im.TotalApprovedEffortCost) - CONVERT(float, im.LTDActualCost)) / CONVERT(float, im.TotalApprovedEffortCost), 2)) + '%'         --  END 'Approved Cost Variance ((Approved Cost-LTD Cost)/Approved Cost)',         CASE WHEN P.INVESTMENT_ID like 'OW%' THEN 'Non-Mission Critical' ELSE p.FD_MISSION_CRTCL_DESC END 'Mission Critical',         StatusReport.COP_REPORT_DATE 'Status Report Date',         StatusReport.COP_REPORT_UPDATE 'Status Report',         --StatusReport.FD_OVERALL_STATUS 'Overall Status',         StatusReport.COP_SCHEDULE_STATUS_DESC 'Schedule Health',         StatusReport.COP_SCHEDULE_EXP 'Schedule Explanation',         StatusReport.COP_SCOPE_STATUS_DESC 'Scope Health',         StatusReport.COP_SCOPE_EXP 'Scope Explanation',         StatusReport.COP_COST_EFT_STATUS_DESC 'Cost and Effort Health',         StatusReport.[COP_EFFORT_EXP] 'Cost and Effort Explanation',         PrevStatusReport.FD_OVERALL_STATUS 'Prior Overall Status',         CASE StatusReport.COP_SCHEDULE_STATUS_DESC             WHEN 'Significant Variance' THEN 'Salmon'             WHEN 'Minor Variance' THEN '#f5e44c'             WHEN 'On Track' THEN '#32CD32'             ELSE NULL         END 'Schedule Status Highlight',         CASE StatusReport.COP_SCOPE_STATUS_DESC             WHEN 'Significant Variance' THEN 'Salmon'             WHEN 'Minor Variance' THEN '#f5e44c'             WHEN 'On Track' THEN '#32CD32'             ELSE NULL         END 'Scope Status Highlight',         CASE StatusReport.COP_COST_EFT_STATUS_DESC             WHEN 'Significant Variance' THEN 'Salmon'             WHEN 'Minor Variance' THEN '#f5e44c'             WHEN 'On Track' THEN '#32CD32'             ELSE NULL         END 'Cost Effect Status Highlight',         CASE WHEN StatusReport.COP_SCHEDULE_STATUS_DESC <> 'On Track' AND StatusReport.COP_SCHEDULE_EXP IS NULL              THEN 'Salmon'             ELSE NULL         END 'Schedule Explaination Highlight',         CASE WHEN StatusReport.COP_SCOPE_STATUS_DESC <> 'On Track' AND StatusReport.COP_SCOPE_EXP IS NULL              THEN 'Salmon'             ELSE NULL         END 'Scope Explaination Highlight',         CASE WHEN StatusReport.COP_COST_EFT_STATUS_DESC <> 'On Track' AND StatusReport.[COP_EFFORT_EXP] IS NULL              THEN 'Salmon'             ELSE NULL         END 'Cost Effort Explaination Highlight',         task.cl_task_id 'Task ID',         task.TASK_NAME 'Task Name',         task.IS_MILESTONE 'Milestone Task',         task.task_status_name 'Task Status',         task.Finish_date 'Task Finish Date',         task.TASK_HEALTH_STATUS 'Task Health Status',         OIssue.OpenIssueCount,         ORisk.OpenRiskCount,         CASE WHEN P.FD_OVERALL_STATUS <> 'On Track' AND OIssue.OpenIssueCount = 0 AND ORisk.OpenRiskCount = 0              THEN 'Salmon'             ELSE NULL         END HighlightRICounts,         issue.ISSUE_ID 'Issue ID',         issue.ISSUE_NAME 'Issue Name',         issue.ISSUE_DESC 'Issue Description',         issue.PRIORITY_DESC 'Issue Priority',         issue.TARGET_RESOLUTION_DATE 'Issue Target Resolution Date',         issue.status_Desc 'Issue Status',         issue.ASSIGNED_TO_RES_FULL_NAME 'Issue Assigned To',         CASE issue.PRIORITY_DESC             WHEN 'High' THEN 'Salmon'             WHEN 'Medium' THEN '#f5e44c'             WHEN 'Low' THEN '#32CD32'             ELSE NULL             END 'Issue Priority Highlight',         risk.RISK_ID 'Risk ID',         risk.RISK_NAME 'Risk Name',         risk.RISK_DESC 'Risk Description',         risk.PRIORITY_DESC 'Risk Priority',         risk.PROBABILITY_DESC 'Risk Probability',         risk.IMPACT_DESC 'Risk Impact',         risk.TARGET_RESOLUTION_DATE 'Risk Target Resolution Date',         risk.status_Desc 'Risk Status',         risk.ASSIGNED_TO_RES_FUL_NAME 'Risk Assigned To',         risk.CalculatedRisk,         risk.CalculatedRiskHighlight,         CASE risk.PRIORITY_DESC              WHEN 'High' THEN 'Salmon'             WHEN 'Medium' THEN '#f5e44c'             WHEN 'Low' THEN '#32CD32'             ELSE NULL             END 'Risk Priority Highlight',         CASE risk.PROBABILITY_DESC              WHEN 'High' THEN 'Salmon'             WHEN 'Medium' THEN '#f5e44c'             WHEN 'Low' THEN '#32CD32'             ELSE NULL             END 'Risk Probability Highlight',         CASE risk.IMPACT_DESC              WHEN 'High' THEN 'Salmon'             WHEN 'Medium' THEN '#f5e44c'             WHEN 'Low' THEN '#32CD32'             ELSE NULL             END 'Risk Impact Highlight',         tf.billable,         tf.NonBillable,         CONVERT(float, im.TotalApprovedEffortHours) - CONVERT(float, im.LTDActualHours) 'LTD Actuals vs Approved',         CONVERT(float, im.[Planned Benefit(3 Full Years)]) - CONVERT(float, im.TotalPlannedEffortCost) 'Planned Cost vs Benefit',         CASE WHEN CONVERT(float, im.TotalPlannedEffortHours) > CONVERT(float, im.TotalApprovedEffortHours) THEN 'Planned Hours > Approved Hours'              ELSE 'Planned Hours <= Approved Hours'          END 'Hours Planned Over Approved',               CASE WHEN CONVERT(float, im.LTDActualCost) > CONVERT(float, im.TotalPlannedEffortCost) THEN 'LTD Cost > Planned Cost'              ELSE 'LTD Cost <= Planned Cost'          END 'Cost LTD Over Planned',             CASE WHEN CONVERT(float, im.LTDActualHours) > CONVERT(float, im.TotalApprovedEffortHours) THEN 'LTD Actuals > Approved Hours'              ELSE 'LTD Actuals <= Approved Hours'          END 'LTD Actuals Over Approved Hours',               CASE WHEN CONVERT(float, im.TotalPlannedEffortCost) > CONVERT(float, im.[Planned Benefit(3 Full Years)])             THEN 'Planned Cost > Planned Benefit'              ELSE 'Planned Cost <= Planned Benefit'          END 'Planned Cost Over Benefit',         CASE WHEN p.PROGRESS_DESC = 'Not Started' AND CONVERT(float, im.LTDActualHours) > 0             THEN 'Progress Not Started with Actuals'              ELSE NULL         END 'Progress Not Started with Actuals',         CASE WHEN ISNULL(task.task_status_name, 'x') <> 'Completed' AND Task.Finish_Date BETWEEN GETDATE() AND DATEADD(Day, 14, GETDATE())              THEN 'Upcoming Milestone'              WHEN ISNULL(task.task_status_name, 'x') <> 'Completed' AND Task.Finish_Date < GETDATE()              THEN 'Late Milestone'             ELSE NULL         END 'Milestone',         CASE WHEN PD.COMMITTED_DELIVERY_DATE IS NULL THEN 'Missing Target Delivery' END 'Missing Target Delivery',         CASE WHEN PD.COMMITTED_START_DATE IS NULL THEN 'Missing Target Start' END 'Missing Target Start',                CASE WHEN PD.COMMITTED_FINISH_DATE IS NULL THEN 'Missing Target Finish' END 'Missing Target Finish',         CASE WHEN PD.COMMITTED_DELIVERY_DATE IS NULL THEN 'Salmon' END 'Missing Target Delivery Highlight',         CASE WHEN PD.COMMITTED_START_DATE IS NULL THEN 'Salmon' END 'Missing Target Start Highlight',                CASE WHEN PD.COMMITTED_FINISH_DATE IS NULL THEN 'Salmon' END 'Missing Target Finish Highlight',         CASE WHEN PD.COMMITTED_DELIVERY_DATE IS NULL OR PD.COMMITTED_START_DATE IS NULL OR PD.COMMITTED_FINISH_DATE IS NULL THEN 1 ELSE 0 END 'Missing Target Dates',         CASE WHEN PD.COMMITTED_DELIVERY_DATE < GETDATE() THEN 'Past Delivery Date' ELSE NULL END 'Past Target Delivery Date',         CASE WHEN PD.COMMITTED_FINISH_DATE < GETDATE() THEN 'Past Finish Date' ELSE NULL END 'Past Target Finish Date',         CASE WHEN PD.COMMITTED_DELIVERY_DATE < GETDATE() THEN 'Salmon' ELSE NULL END 'Past Target Delivery Date Highlight',         CASE WHEN PD.COMMITTED_FINISH_DATE < GETDATE() THEN 'Salmon' ELSE NULL END 'Past Target Finish Date Highlight',         CASE WHEN PD.COMMITTED_DELIVERY_DATE < GETDATE() OR PD.COMMITTED_FINISH_DATE < GETDATE() THEN 'Past Target Dates'             WHEN PD.COMMITTED_DELIVERY_DATE BETWEEN GETDATE() AND DATEADD(Day, 14, GETDATE()) THEN 'Upcoming Target Dates'             ELSE NULL         END 'Target Dates',         --CASE WHEN StatusReport.COP_REPORT_DATE < DATEADD(Day, -7, GETDATE())          --      AND (ISNULL(P.FD_TGT_STRT_DT, '1/1/1999') <= GETDATE() OR CONVERT(float, im.LTDActualHours) > 0)         --  THEN 'Late Status Report'          --  WHEN StatusReport.COP_REPORT_DATE IS NULL          --      AND (ISNULL(P.FD_TGT_STRT_DT, '1/1/1999') <= GETDATE() OR CONVERT(float, im.LTDActualHours) > 0)         --  THEN 'No Status Report'         --  ELSE 'On Time'          --END 'Late Status Report',         --11/19/2020 Active Approved Projects only         CASE WHEN (ISNULL(PD.COMMITTED_START_DATE, '1/1/2000') <= GetDate() AND P.IS_ACTIVE = 1) THEN 'Active Target Started' ELSE NULL END 'Active Target Started',         CASE WHEN StatusReport.COP_REPORT_DATE < DATEADD(Day, -7, GETDATE())                  --AND (P.STATUS_DESC = 'Approved' AND P.IS_ACTIVE = 1)                 --2/16/2021 Where Target Start Date <= Today                 AND (PD.COMMITTED_START_DATE <= GetDate() AND P.IS_ACTIVE = 1)             THEN 'Late Status Report'              WHEN StatusReport.COP_REPORT_DATE IS NULL                  --AND (P.STATUS_DESC = 'Approved' AND P.IS_ACTIVE = 1)                 --2/16/2021 Where Target Start Date <= Today OR IS NULL                 AND (ISNULL(PD.COMMITTED_START_DATE, '1/1/2000') <= GetDate() AND P.IS_ACTIVE = 1)             THEN 'No Status Report'             ELSE 'On Time'          END 'Late Status Report',         CASE WHEN P.STATUS_DESC = 'Unapproved' AND CONVERT(float, im.LTDActualHours) > 250 THEN 'Unapproved with Over 250 Actuals'              ELSE NULL          END 'Unapproved with Over 250 Actuals',         CR.CH_RQST_ID 'Change Request ID',         CR.CH_RQST_NAME 'Change Request Name',         CR.CH_RQST_DESC 'Change Request Description',         CR.STATUS_DESC 'Change Request Status',         CR.FD_CR_WRK_STTS_DESC 'Change Request Work Status',         CR.ASSIGNED_TO_RES_FULL_NAME 'Change Request Assigned To',         CR.EFFECT_ON_COST 'Change in Cost',         CR.EFFECT_ON_SCHEDULE 'Change in Schedule',         CR.EFFECT_ON_RESOURCES 'Change in Resources',         CR.ASSUMPTIONS 'Change Request Reasons',         CR.FD_CR_BILL_METHOD_DESC 'Change Request Billing Method',         CR.BENEFITS 'Change Request Benefits',         CR.IMPACT_DESCRIPTION 'Change Request Impact on Project',         CR.IMPACT_ON_BASELINE 'Change Request Impact on Baseline',         CASE WHEN ISNULL(im.TotalApprovedEffortHours, '0') = '0'             THEN 'No Approved Hours'              ELSE CONVERT(varchar, ROUND(100 * convert(float, CR.EFFECT_ON_RESOURCES) / NULLIF(convert(float, im.TotalApprovedEffortHours), 0), 0)) + '%'         END 'Hour Variance % (Change in Resources / Approved Hours)',         CASE WHEN ISNULL(im.TotalApprovedEffortHours, '0') = '0'             THEN NULL             WHEN convert(float, CR.EFFECT_ON_RESOURCES) / NULLIF(convert(float, im.TotalApprovedEffortHours), 0) > 0.25             THEN 'Salmon'             ELSE NULL         END 'Hour Variance % Highlight',         CASE WHEN CR.STATUS_DESC NOT IN ('Closed', 'Resolved') THEN 'Salmon' ELSE NULL END 'Change Request Open',         CASE WHEN ISNULL(CR.EFFECT_ON_COST, '0') = '0'              THEN '0'             WHEN ISNULL(im.TotalApprovedEffortCost, '0') = '0'             THEN 'No Approved Cost'             ELSE CONVERT(varchar, ROUND(100 * convert(float, CR.EFFECT_ON_COST) / NULLIF(convert(float, im.TotalApprovedEffortCost), 0), 0)) + '%'         END 'Cost Change Variance %',         CASE WHEN (ISNULL(im.TotalApprovedEffortCost, '0') = '0' AND ISNULL(CR.EFFECT_ON_COST, '0') <> '0')              OR convert(float, CR.EFFECT_ON_COST) / NULLIF(convert(float, im.TotalApprovedEffortCost), 0) > 0.25             OR convert(float, CR.EFFECT_ON_COST) / NULLIF(convert(float, im.TotalApprovedEffortCost), 0) < - 0.25             THEN 'Salmon'             ELSE NULL         END 'Cost Change Variance % Highlight',         ig.CAPITALIZATION_BEG_DATE 'Cap Begin Date',         ig.CAPITALIZATION_END_DATE 'Cap End Date',         ig.CAPITAL_CONFIRMED 'Cap Confirmed',         CASE WHEN ig.CAPITALIZATION_END_DATE BETWEEN GETDATE() - 1 AND GETDATE() + 31             THEN 'Upcoming Cap End Date'             ELSE NULL         END 'Upcoming Cap End Date'     FROM Iodw_cl_rpt_vw_pio p         LEFT JOIN Iodw_cl_project_dim pd on pd.INVESTMENT_ID = p.CL_INVESTMENT_ID --10/8/21 Added Iodw_cl_project_dim to bring in COMMITTED DATES - Digital Solutions         LEFT JOIN IODW_ATTRIBUTE_MV am ON am.ATTRIBUTE = 'MANAGEMENT REPORT' AND am.ID = p.CL_INVESTMENT_ID         LEFT JOIN iodw_rp_investment_measure im ON im.CL_INVESTMENT_ID = p.CL_INVESTMENT_ID         LEFT JOIN iodw_rp_investment_measure_app ima ON ima.CL_INVESTMENT_ID = p.CL_INVESTMENT_ID                AND PLATFORM_POSITION = 1            LEFT JOIN iodw_rp_investment_measure_line iml ON iml.CL_INVESTMENT_ID = p.CL_INVESTMENT_ID             and LINE_POSITION = 1         LEFT JOIN Iodw_cl_investment_ownership_mv iom on p.cl_investment_id =iom.cl_investment_id          LEFT JOIN #OpenIssues OIssue ON OIssue.CL_investment_id = p.cl_investment_id         LEFT JOIN #OpenRisks ORisk ON ORisk.CL_investment_id = p.cl_investment_id         LEFT JOIN iodw_cl_task_dim task on task.cl_investment_id = p.cl_investment_id             and task.IS_MILESTONE = 1         LEFT JOIN iodw_cl_issue issue on issue.cl_investment_id = p.cl_investment_id             AND ISNULL(ISSUE.status_Desc, 'X') NOT IN ('Closed', 'Resolved')         LEFT JOIN iodw_cl_risk risk on risk.cl_investment_id = p.cl_investment_id             AND ISNULL(risk.status_Desc, 'X') NOT IN ('Closed', 'Resolved')         LEFT JOIN #tf tf on tf.cl_investment_id = p.cl_investment_id         LEFT JOIN #StatusReport StatusReport ON StatusReport.cl_investment_id = p.cl_investment_id             AND StatusReport.RNUM = 1         LEFT JOIN #StatusReport PrevStatusReport ON PrevStatusReport.cl_investment_id = p.cl_investment_id             AND StatusReport.RNUM = 2         LEFT JOIN [dbo].[Iodw_cl_change_request] CR ON CR.CL_investment_ID = P.CL_investment_id              LEFT JOIN Iodw_cl_investment_gl_vw ig              on ig.cl_investment_id = p.cl_investment_id     WHERE --1/8/2021 Changed from LIKE '%Digital Channels%' to exclude APAC - Digital Channels          p.FD_IMP_PORT1_DESC = 'Digital Channels'        AND p.IS_TEMPLATE = 0         END  GO