¶ Basic Information
| Key | Value |
|---|---|
| Table | DS06 Resources |
| Severity | MAJOR |
| Unique ID | 1060266 |
| Summary | Is this resource duplicated across subprojects? |
| Error message | Count of Resource_id > 1 across distinct 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.
This Data Integrity and Quality (DIQ) check is designed to identify instances where a resource is duplicated across different subprojects within the DS06 Resources table. The purpose of this check is to ensure that each resource is uniquely assigned to a single subproject, maintaining the integrity of resource allocation and scheduling data.
When this DIQ test flags an issue, it indicates that there is at least one resource (resource_id) that appears more than once across distinct subprojects (subproject_id). This could potentially lead to inaccuracies in project management, resource allocation, and scheduling within the EVMS construction project management data.
The likely cause of this error is the incorrect assignment of a resource to multiple subprojects or an error in data entry where a resource that should be unique has been entered into the system under different subprojects. The expected behavior is that each resource should be associated with a single subproject, ensuring clear and unambiguous resource planning and tracking.
To resolve this issue, review the resource assignments for the flagged resource_ids, ensuring that each resource is correctly assigned to only one subproject. This may involve correcting data entry errors or reevaluating the resource allocation strategy to prevent duplication across subprojects.
¶ Why do we check this?
The "Duplicate Resource" DIQ test is being performed on the "DS06 Resources" table to identify if a resource is duplicated across different subprojects. This test is crucial because it checks for the count of Resource_id that appears more than once across distinct subproject_id. The importance of this check lies in ensuring that resources are uniquely assigned and managed within each subproject to prevent misallocation or double counting of resources. This could lead to inaccuracies in resource planning, budgeting, and project management. The severity level of MAJOR indicates that while this issue may not immediately invalidate the data, it is likely to cause problems during analysis, such as skewed resource allocation reports or financial discrepancies. Addressing this issue is important for maintaining accurate and reliable project management data, which is essential for effective decision-making and project execution within the US Department of Energy's EVMS construction project management.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS06_Res_IsResourceDuplicated] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
WITH dupeResourcesInSubprojects AS (
SELECT
[resource_id],
[schedule_type]
FROM
[dbo].[DS06_schedule_resources]
WHERE upload_id = @upload_id
GROUP BY
[resource_id],
[schedule_type]
HAVING
COUNT(DISTINCT ISNULL([subproject_ID], '')) > 1
)
SELECT ds06.*
FROM dupeResourcesInSubprojects dris
INNER JOIN DS06_schedule_resources ds06 ON ds06.resource_ID = dris.resource_ID
AND ds06.schedule_type = dris.schedule_type
WHERE ds06.upload_ID = @upload_id
)
¶ Changelog
| Date | Version # | Description of Changes |
|---|---|---|
| 2024-07-19 | 5.0.4 | Updated SQL to only return unique rows. Duplicates were returned previously. |