¶ Basic Information
| Key | Value |
|---|---|
| Table | DS04 Schedule |
| Severity | MAJOR |
| Unique ID | 1040107 |
| Summary | Is this CD or BCP out of sequence chronologically in the milestone list? |
| Error message | CD or BCP milestone (milestone_level = 1xx) occurs out of sequence with successive milestone (ES_date > ES_date of the successive milestone). |
¶ 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 "CD / BCP Milestones Out of Sequence" is designed to ensure that the milestones in the DS04 Schedule table are in the correct chronological order. This check is particularly focused on milestones that are classified as CD or BCP, which are identified by a milestone level of 1xx.
The error message "CD or BCP milestone (milestone_level = 1xx) occurs out of sequence with successive milestone (ES_date > ES_date of the successive milestone)" indicates that a milestone has been found where the estimated start date (ES_date) is later than the estimated start date of the milestone that follows it in the sequence. This is not expected, as milestones should be listed in the order they are planned to occur.
The fields that are likely causing this issue are the 'milestone_level', 'ES_date', 'schedule_type', and 'task_ID' fields in the DS04 Schedule table. The 'milestone_level' field should contain values between 100 and 199 for CD or BCP milestones. The 'ES_date' field should contain the estimated start date of the milestone, and these dates should be in ascending order when sorted by 'milestone_level'. The 'schedule_type' and 'task_ID' fields are used to group and identify the milestones.
If you encounter this error, you should review the milestones in your schedule to ensure they are in the correct order. If necessary, adjust the 'ES_date' values to correct the sequence.
¶ Why do we check this?
This test is being performed to ensure that the CD or BCP milestones in the DS04 Schedule table are in the correct chronological order. The test checks if a CD or BCP milestone (with a milestone level of 1xx) occurs out of sequence with the successive milestone, meaning the estimated start date (ES_date) of the milestone is later than the ES_date of the successive milestone.
The importance of this check is to maintain the integrity and accuracy of the project schedule. If milestones are out of sequence, it could lead to confusion, misinterpretation of the project timeline, and potential delays in project execution. It could also impact the ability to accurately track and report on project progress.
The severity of this check is marked as a MAJOR. This means that while it may not prevent the data from being reviewed, it is likely to cause problems during analysis and should be addressed to ensure accurate and efficient project management.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS04_Sched_Are1xxMSsOutOfOrder] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with MSList as (
SELECT ES_date,
LEAD(ES_date,1) OVER (PARTITION BY schedule_type ORDER BY milestone_level) as NextES,
schedule_type,
task_ID,
milestone_level,
LEAD(milestone_level,1) OVER (PARTITION BY schedule_type ORDER BY milestone_level) as NextMilestoneLevel,
ISNULL(subproject_ID,'') SubP
FROM DS04_schedule
WHERE upload_ID = @upload_ID
AND (
milestone_level BETWEEN 100 AND 139
OR milestone_level = 150
OR milestone_level BETWEEN 170 AND 199
)
UNION
SELECT ES_date,
LEAD(ES_date,1) OVER (PARTITION BY schedule_type ORDER BY milestone_level) as NextES,
schedule_type,
task_ID,
milestone_level,
LEAD(milestone_level,1) OVER (PARTITION BY schedule_type ORDER BY milestone_level) as NextMilestoneLevel,
ISNULL(subproject_ID,'') SubP
FROM DS04_schedule
WHERE upload_ID = @upload_ID AND milestone_level BETWEEN 140 AND 145
UNION
SELECT ES_date,
LEAD(ES_date,1) OVER (PARTITION BY schedule_type ORDER BY milestone_level) as NextES,
schedule_type,
task_ID,
milestone_level,
LEAD(milestone_level,1) OVER (PARTITION BY schedule_type ORDER BY milestone_level) as NextMilestoneLevel,
ISNULL(subproject_ID,'') SubP
FROM DS04_schedule
WHERE upload_ID = @upload_ID AND milestone_level BETWEEN 160 AND 165
), Flags as (
SELECT *
FROM MSList
WHERE
milestone_level = 170 AND NextMilestoneLevel = 175 AND ES_date > NextES
OR ((milestone_level <> 170 OR NextMilestoneLevel <> 175) AND milestone_level <> NextMilestoneLevel AND ES_date >= NextES)
)
SELECT S.*
FROM DS04_schedule S INNER JOIN Flags F ON S.task_ID = F.task_ID
AND S.schedule_type = F.schedule_type
AND ISNULL(S.subproject_ID,'') = F.SubP
WHERE upload_id = @upload_ID
)
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2024-04-30 | 1. Logic adjusted to account for the addition of 'subproject_id' field. |
| 2. Exception created for milestone_level = 170 (planned/estimated completion without UB), which should have the same date as milestone_level = 175 (End of PMB). |