¶ Basic Information
| Key | Value |
|---|---|
| Table | DS04 Schedule |
| Severity | MINOR |
| Unique ID | 1040137 |
| Summary | Do multiple reprogramming milestones exist in the current period? |
| Error message | Multiple reprogramming milestones (milestone_level = 138) 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 Reprogramming Milestones In Current Period" is designed to identify any instances where multiple reprogramming milestones exist within the current period in the DS04 Schedule table.
A reprogramming milestone is identified by a milestone level of 138. The current period is defined as any date within 35 days of the CPP Status Date. If the Early Start (ES) date or Early Finish (EF) date of a milestone is within this range, it is considered to be in the current period.
The DIQ check will flag any instances where more than one reprogramming milestone for the same schedule type exists within the current period. This is likely caused by an error in data entry or scheduling, as there should typically only be one reprogramming milestone per schedule type in any given period.
The fields that are likely causing this issue are the 'milestone_level', 'ES_date', 'EF_date', and 'CPP_status_date' fields in the DS04 Schedule table. The expected values for these fields are 138 for the 'milestone_level', and dates within 35 days of the 'CPP_status_date' for the 'ES_date' and 'EF_date' fields.
If this DIQ check flags any data, it is recommended to review the scheduling and data entry processes to ensure that reprogramming milestones are being correctly assigned and recorded.
¶ Why do we check this?
This test is being performed to check for the presence of multiple reprogramming milestones within the current period in the DS04 Schedule table. Reprogramming milestones are significant events or turning points in a project that may require changes in the project's schedule or budget. Having multiple reprogramming milestones in the same period could indicate a high level of change or instability in the project, which could potentially lead to issues in project management and execution.
The severity of this check is classified as an MINOR. This means that while it is not a critical error that would prevent the data from being reviewed, it is a potential issue that could cause minor problems or indicate that the data does not fully adhere to best practices. It is important to monitor and manage the number of reprogramming milestones to ensure that the project is progressing as planned and that any necessary adjustments are made in a timely and effective manner.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS04_Sched_DoMultipleRpgMSsExistInTheCurrentPeriod] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
WITH RPGMS AS (
-- get the RPG MSs by schedule type & task ID
SELECT schedule_type, task_ID
FROM DS04_schedule
WHERE upload_ID = @upload_id AND milestone_level = 138
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
), RPGMSCount AS (
-- count by schedule type
SELECT schedule_type, COUNT(*) AS cnt
FROM RPGMS
GROUP BY schedule_type
HAVING COUNT(*) > 1
), Flags as (
-- problem MSs
SELECT R.schedule_type, R.task_ID
FROM RPGMS R INNER JOIN RPGMSCount 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
)