| Key | Value |
|---|---|
| Table | DS05 Schedule Logic |
| Severity | CRITICAL |
| Unique ID | 1050236 |
| Summary | Is this row duplicated by schedule_type, type, task_ID, predecessor_task_ID, subproject_ID, and predecessor_subproject_ID? |
| Error message | Count of schedule_type, type, task_ID, predecessor_task_ID, subproject_ID, and predecessor_subproject_ID combo > 1. |
The Data Integrity and Quality (DIQ) check titled "Non-Unique Relationship" for the DS05 Schedule Logic table is designed to identify any instances where the combination of schedule_type, task_ID, predecessor_task_ID, subproject_id, and predecessor_subproject_id is not unique.
In the context of EVMS construction project management data, each task should have a unique predecessor and a specific schedule type. If the same combination of these three fields appears more than once in the DS05 Schedule Logic table, it indicates a potential data integrity issue.
The error message "Count of schedule_type, task_ID, predecessor_task_ID, subproject_ID, and predecessor_subproject_ID combo > 1" is triggered when the DIQ check finds more than one row with the same combination of these three fields. This could be caused by a data entry error, such as duplicating a row of data, or a system error that has resulted in duplicated records.
To resolve this issue, you should review the data in the DS05 Schedule Logic table, specifically focusing on the schedule_type, task_ID, predecessor_task_ID, subproject_id, and predecessor_subproject_id fields. Ensure that each task has a unique predecessor and that the schedule type is correctly assigned. If you find any duplicate records, you should correct or remove them as appropriate to maintain the integrity and quality of your project management data.
This test is being performed to ensure that there are no duplicate entries in the 'DS05 Schedule Logic' table for the combination of 'schedule_type', 'task_ID', 'predecessor_task_ID', 'subproject_id', and 'predecessor_subproject_id'. The presence of duplicate entries could lead to incorrect calculations or misinterpretations of the data, which could in turn lead to incorrect decisions being made based on this data.
The severity of this check is marked as 'CRITICAL', which is the highest level of severity. This means that if this issue is not resolved, the data cannot be reviewed or used for further analysis. This highlights the importance of this check, as maintaining data integrity and quality is crucial for accurate and reliable project management. Without unique relationships in the data, it would be impossible to accurately track tasks and their relationships, which is a fundamental aspect of project management.
CREATE FUNCTION [dbo].[fnDIQ_DS05_Logic_PK] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with Dupes as (
SELECT schedule_type, task_ID, predecessor_task_ID, ISNULL(subproject_ID,'') SubP, ISNULL(predecessor_subproject_ID,'') PSubP, [type]
FROM DS05_schedule_logic
WHERE upload_ID = @upload_ID
GROUP BY schedule_type, task_ID, predecessor_task_ID, ISNULL(subproject_ID,''), ISNULL(predecessor_subproject_ID,''), [type]
HAVING COUNT(*) > 1
)
SELECT L.*
FROM DS05_schedule_logic L INNER JOIN Dupes D ON L.schedule_type = D.schedule_type
AND L.task_ID = D.task_ID
AND L.predecessor_task_ID = D.predecessor_task_ID
AND ISNULL(L.subproject_ID,'') = D.SubP
AND ISNULL(L.predecessor_subproject_ID,'') = D.PSubP
AND L.[type] = D.[type]
WHERE upload_id = @upload_ID
)
| Date | Version # | Description of Changes |
|---|---|---|
| 2024-04-30 | Logic adjusted to account for the addition of 'subproject_ID' and 'predecessor_subproject_ID' fields. | |
| 2024-05-30 | 5.0.3 | Added DS05_schedule_logic.[type] field to the unique key for identifying duplicates. |