¶ Basic Information
| Key | Value |
|---|---|
| Table | DS04 Schedule |
| Severity | MINOR |
| Unique ID | 1040202 |
| Summary | Are the start/finish dates between the Planned/Estimated Completion and End of PMB milestones misaligned? |
| Error message | ES_date & EF_date misaligned between Planned/Estimated Completion and End of PMB milestones (milestone_level = 170 and milestone_level = 175, respectively). |
¶ What causes this error?
The following text was generated by an AI tool and hasn't been reviewed for accuracy by a human! It might be useful, but it also might have errors. Are you a human? You can help by reviewing it for accuracy! Edit it as needed then remove this message.
The Data Integrity and Quality (DIQ) check for the DS04 Schedule table, titled "Planned/Estimated Completion Misaligned with End of PMB", is designed to identify any discrepancies between the start and finish dates of the Planned/Estimated Completion and End of PMB milestones.
The Planned/Estimated Completion and End of PMB milestones are represented by the milestone levels 170 and 175 respectively. The fields that are checked for alignment are the ES_date (Early Start date) and EF_date (Early Finish date).
If the DIQ check identifies a misalignment between these dates, it means that the start and/or finish dates for the Planned/Estimated Completion do not match the start and/or finish dates for the End of PMB. This could be due to an error in data entry or a change in the project schedule that was not accurately reflected in both milestones.
The expected values for the ES_date and EF_date fields should be the same for both the Planned/Estimated Completion and End of PMB milestones. If they are not, this will need to be corrected to ensure accurate project management data.
¶ Why do we check this?
This test is being performed to ensure that the start and finish dates between the Planned/Estimated Completion and End of PMB (Performance Measurement Baseline) milestones are aligned. The alignment of these dates is crucial for accurate project scheduling and management.
The test has a severity level of MINOR, which means that while it may not immediately prevent data review or analysis, it could potentially cause minor problems or indicate that the data does not adhere to all best practices. Misalignment of these dates could lead to inaccurate project timelines, which could in turn affect project planning, resource allocation, and overall project success.
The importance of this check lies in maintaining the integrity and quality of the project management data. Ensuring that these dates are aligned helps to provide a more accurate representation of the project timeline, which is essential for effective project management and decision-making.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS04_Sched_IsPlannedCompletionAlignedWithEndOfPMB] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with PCompl as (
SELECT task_ID, ES_date, EF_date, schedule_type
FROM DS04_schedule
WHERE upload_ID = @upload_ID AND milestone_level = 170
), EOPMB as (
SELECT task_ID, ES_date, EF_date, schedule_type
FROM DS04_schedule
WHERE upload_ID = @upload_ID AND milestone_level = 175
), ToFlag as (
SELECT P.task_ID 'CompTask', P.task_ID 'EOPTask', P.schedule_type
FROM PCompl P INNER JOIN EOPMB E ON P.schedule_type = E.schedule_type
WHERE P.ES_date <> E.ES_date OR P.EF_date <> E.EF_date
)
SELECT
S.*
FROM
DS04_schedule S LEFT JOIN ToFlag F ON S.task_ID = F.CompTask
AND S.schedule_type = F.schedule_type
LEFT JOIN ToFlag F2 ON S.task_ID = F2.EOPTask
AND S.schedule_type = F2.schedule_type
WHERE
upload_id = @upload_ID
AND F.CompTask IS NOT NULL
AND F2.EOPTask IS NOT NULL
)