| Key | Value |
|---|---|
| Table | DS06 Resources |
| Severity | CRITICAL |
| Unique ID | 1060259 |
| Summary | Is this resource / role duplicated by schedule type, task ID, EOC, subproject ID, and resource or role ID? |
| Error message | Count of schedule_type, task_ID, EOC, subproject_ID and resource_ID or role_id combo > 1. |
The Data Integrity and Quality (DIQ) check titled "Non-Unique Resource or Role" is designed to identify any instances where a resource or role is duplicated within the DS06 Resources table. This duplication is determined based on the combination of schedule type, task ID, subproject_id, and either resource ID or role ID.
If the count of any combination of these fields exceeds 1, it indicates that there is a duplicate entry in the data. This could be due to an error in data entry or a glitch in the data import process.
If this DIQ check identifies any issues, it is recommended to review the data entry process for these fields to ensure that each combination of schedule type, task ID, and either resource ID or role ID is unique.
This test is being performed to ensure that there are no duplicate entries in the 'DS06 Resources' table for a given combination of schedule type, task ID, subproject_id, and resource or role 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.
CREATE FUNCTION [dbo].[fnDIQ_DS06_Res_PK] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with Dupes as (
SELECT schedule_type, task_ID, ISNULL(EOC,'') EOC, ISNULL(resource_ID,'') resource_ID, ISNULL(role_id,'') role_id, ISNULL(subproject_ID,'') SubP
FROM DS06_schedule_resources
WHERE upload_id = @upload_ID
GROUP BY schedule_type, task_ID, ISNULL(EOC,''), ISNULL(resource_ID,''), ISNULL(role_id,''), ISNULL(subproject_ID,'')
HAVING COUNT(*) > 1
)
SELECT R.*
FROM DS06_schedule_resources R INNER JOIN Dupes D ON R.schedule_type = D.schedule_type
AND R.task_ID = D.task_ID
AND ISNULL(R.resource_ID,'') = D.resource_ID
AND ISNULL(R.role_id,'') = D.role_id
AND ISNULL(R.subproject_ID,'') = D.SubP
AND ISNULL(R.EOC,'') = D.EOC
WHERE upload_id = @upload_ID
)
| Date | Description of Changes |
|---|---|
| 2024-04-30 | Logic adjusted to include 'EOC' and 'subproject_ID' fields as part of the test. |