¶ Basic Information
| Key | Value |
|---|---|
| Table | DS04 Schedule |
| Severity | MINOR |
| Unique ID | 1040160 |
| Summary | Does this CD or BCP appear more than once in the schedule? |
| Error message | CD or BCP (milestone_level = 1xx) appears more than once in either the FC or BL (or both) (by subproject_ID). |
¶ 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 "Duplicate CD or BCP Entry" is designed to ensure that each CD or BCP (Critical Decision or Baseline Change Proposal) appears only once in the schedule. This check is performed on the DS04 Schedule table.
The error message "CD or BCP (milestone_level = 1xx) appears more than once in either the FC or BL (or both)" indicates that a CD or BCP has been duplicated in the schedule. This duplication is identified by the milestone_level field having a value between 100 and 199, excluding 138 and 139.
The duplication error is likely caused by data entry errors or system glitches that result in the same CD or BCP being entered more than once in the schedule. The expected values for the milestone_level field should be unique for each CD or BCP in the schedule, ensuring that each CD or BCP is represented only once.
To resolve this issue, review the DS04 Schedule table for any CDs or BCPs that appear more than once and correct the entries as necessary.
¶ Why do we check this?
This test is being performed to check for duplicate entries of Control Decision (CD) or Baseline Change Proposal (BCP) in the DS04 Schedule table. The test is looking for instances where a CD or BCP, identified by a milestone level of 1xx, appears more than once in either the Forecast (FC) or Baseline (BL) columns, or both.
The importance of this check is to ensure the accuracy and consistency of the project schedule data. Duplicate entries can lead to confusion, misinterpretation of data, and potential errors in project management and decision-making processes. Although the severity level is marked as an MINOR, which is less severe, it is still crucial to address this issue to maintain the integrity of the data and follow best practices.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS04_Sched_IsCDOrBCPDuplicated] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with Fails AS (
SELECT schedule_type, milestone_level, ISNULL(subproject_ID,'') SubP
FROM DS04_schedule
WHERE
upload_ID = @upload_ID
AND milestone_level BETWEEN 100 AND 199
AND milestone_level NOT IN (138,139)
GROUP BY schedule_type, milestone_level, ISNULL(subproject_ID,'')
HAVING COUNT(*) > 1
)
SELECT
S.*
FROM
DS04_schedule S INNER JOIN Fails F ON S.schedule_type = F.schedule_type
AND S.milestone_level = F.milestone_level
AND ISNULL(S.subproject_ID,'') = F.SubP
WHERE
upload_ID = @upload_ID
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2024-04-30 | Logic adjusted to account for the addition of 'subproject_id' field. |