¶ Basic Information
| Key | Value |
|---|---|
| Table | DS11 Variance |
| Severity | CRITICAL |
| Unique ID | 1110495 |
| Summary | Is this VAR duplicated by WBS ID & Narrative type? |
| Error message | Count of WBS_ID & narrative_type combo > 1. |
¶ 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 for the DS11 Variance table, titled 'Duplicate VAR', is designed to identify any instances where the combination of the Work Breakdown Structure ID (WBS_ID) and the Narrative Type (narrative_type) are duplicated.
In the context of EVMS construction project management data, each WBS_ID and narrative_type combination should be unique. This is because the WBS_ID represents a specific task or activity within the project, and the narrative_type provides additional context or explanation for that task. Having duplicates of this combination could lead to confusion or misinterpretation of the project data.
If this DIQ check identifies any records where the count of a specific WBS_ID and narrative_type combination is greater than 1, it indicates that there are duplicate entries in the DS11 Variance table. This could be due to a data entry error, a system glitch, or a misunderstanding of the data input requirements.
To resolve this issue, it is recommended to review the identified records and remove or correct the duplicate entries. This will ensure the integrity and quality of the project management data, and support accurate and effective project monitoring and decision-making.
¶ Why do we check this?
This test is being performed on the 'DS11 Variance' table to check for duplicate entries in the VAR (Variance) field. The test is specifically looking for instances where the combination of 'WBS_ID' and 'narrative_type' appears more than once, which would indicate a duplicate VAR.
The importance of this check is to ensure the accuracy and reliability of the data. Duplicate entries can distort the true picture of the project's status and lead to incorrect decisions or actions. For example, if a variance is counted twice, it could falsely indicate a larger deviation from the plan than actually exists.
The severity of this test is marked as 'CRITICAL', which is the highest level of severity. This means that if any duplicates are found, they must be corrected before the data can be further reviewed or used. This underscores the critical nature of maintaining data integrity in the EVMS construction project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS11_VAR_PK] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with Dupes as (
SELECT WBS_ID, ISNULL(narrative_type,'') narrative_type
FROM DS11_variance
WHERE upload_ID = @upload_ID
GROUP BY WBS_ID, ISNULL(narrative_type,'')
HAVING COUNT(*) > 1
)
SELECT
V.*
FROM
DS11_variance V INNER JOIN Dupes D ON V.WBS_ID = D.WBS_ID
AND ISNULL(V.narrative_type,'') = D.narrative_type
WHERE
upload_ID = @upload_ID
)