¶ Basic Information
| Key | Value |
|---|---|
| Table | DS04 Schedule |
| Severity | MINOR |
| Unique ID | 1040136 |
| Summary | Do multiple replanning milestones exist in the current period? |
| Error message | Multiple replanning milestones (milestone_level = 139) found in the current period (ES_date within 35 days of CPP Status Date). |
¶ 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 titled "Multiple Replanning Milestones In Current Period" is designed to identify any instances where multiple replanning milestones exist within the current period in the DS04 Schedule table.
A replanning milestone is identified by a milestone level of 139. The current period is defined as any date within 35 days of the CPP Status Date.
If the DIQ check identifies multiple replanning milestones within the current period, this could indicate a potential issue with the scheduling or planning process. It's important to note that this check only identifies replanning milestones that occur before the CPP Status Date, and ignores any that occur after this date.
The fields that are likely causing this issue are the 'milestone_level', 'ES_date', 'EF_date', and 'CPP_status_date'. The expected values for these fields are 139 for 'milestone_level', and for 'ES_date' and 'EF_date', the dates should be within 35 days of the 'CPP_status_date'.
If the DIQ check identifies multiple replanning milestones within the current period, it's recommended to review the scheduling and planning process to ensure that it's functioning as expected.
¶ Why do we check this?
This test is being performed to check for the presence of multiple replanning milestones within the current period in the DS04 Schedule table. The presence of multiple replanning milestones (milestone_level = 139) within a short time frame (ES_date within 35 days of CPP Status Date) could indicate a potential issue with the project schedule or planning process.
The severity of this check is marked as an MINOR, which means it's not a critical error but could indicate minor problems or deviations from best practices. It's important to perform this check to ensure the accuracy and reliability of the project schedule data. Multiple replanning milestones in a short period could indicate instability in the project planning, which could potentially lead to issues in project execution and management. Therefore, this check helps to maintain the integrity and quality of the project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS04_Sched_DoMultipleReplanMSsExistInTheCurrentPeriod] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
WITH ReplanMS AS (
-- get the Replan MSs by schedule type & task ID
SELECT schedule_type, task_ID
FROM DS04_schedule
WHERE upload_ID = @upload_id AND milestone_level = 139
AND DATEDIFF(d, COALESCE(ES_date,EF_date), CPP_status_date) < 35
AND DATEDIFF(d, COALESCE(ES_date,EF_date), CPP_status_date) >= 0 --ignore if the MS is after the status date
), ReplanCount AS (
-- count by schedule type
SELECT schedule_type, COUNT(*) AS cnt
FROM ReplanMS
GROUP BY schedule_type
HAVING COUNT(*) > 1
), Flags as (
-- problem MSs
SELECT R.schedule_type, R.task_ID
FROM ReplanMS R INNER JOIN ReplanCount Cnt ON R.schedule_type = Cnt.schedule_type
WHERE Cnt.cnt > 1
)
SELECT
S.*
FROM
DS04_schedule S INNER JOIN Flags F ON S.schedule_type = F.schedule_type AND S.task_ID = F.task_ID
WHERE
upload_ID = @upload_id
)