¶ Basic Information
| Key | Value |
|---|---|
| Table | DS21 Rates |
| Severity | CRITICAL |
| Unique ID | 1210602 |
| Summary | Is this rate duplicated by resource ID? |
| Error message | Count resource_ID > 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 titled "Duplicate Rate" is designed to ensure that each rate in the DS21 Rates table is unique by resource ID. This check is important because duplicate rates can lead to inaccuracies in project cost calculations and other analyses.
If the DIQ check returns an error, it means that there are one or more resource IDs in the DS21 Rates table that are associated with more than one rate. The error message "Count resource_ID > 1" indicates that the problematic resource IDs are those that appear more than once in the table.
To resolve this issue, you should review the DS21 Rates table and ensure that each resource ID is associated with only one rate. If a resource ID is associated with multiple rates, you will need to determine which rate is correct and remove the duplicates. Please note that the correct rate may vary depending on the specific circumstances of the project, so it's important to consult with the project manager or another knowledgeable individual if you're unsure.
¶ Why do we check this?
This test is being performed to check for duplicate rates in the 'DS21 Rates' table of the EVMS construction project management data. The test is looking for instances where the same resource ID is associated with more than one rate.
The importance of this check is to ensure data integrity and accuracy. Duplicate rates for the same resource ID could lead to incorrect calculations or analyses, which could in turn lead to incorrect decisions or conclusions. This could potentially have significant impacts on the project's budget, timeline, or resource allocation.
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 reviewed or used for further analysis. This underscores the critical importance of this check in maintaining the integrity and quality of the data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS21_Rates_PK] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
SELECT
*
FROM
DS21_rates
WHERE
upload_ID = @upload_ID
AND resource_ID IN (
SELECT resource_ID
FROM DS21_rates
WHERE upload_ID = @upload_ID
GROUP BY resource_ID
HAVING COUNT(*) > 1
)
)