| Key | Value |
|---|---|
| Table | DS06 Resources |
| Severity | CRITICAL |
| Unique ID | 9060299 |
| Summary | Are the rates missing for this resource? |
| Error message | Resource_ID missing in DS21.resource_ID list. |
The Data Integrity and Quality (DIQ) check titled "Resource Missing Rates" is designed to ensure that all resources listed in the DS06 Resources table have corresponding rates in the DS21 Rates table.
If an error message "Resource_ID missing in DS21.resource_ID list" is displayed, it means that there are one or more resources in the DS06 Resources table that do not have a corresponding rate in the DS21 Rates table. The error is grouped by the 'resource_id' field.
The likely cause of this error is that when data was entered or imported into the DS21 Rates table, one or more resource IDs from the DS06 Resources table were omitted.
To resolve this issue, you should review the DS06 Resources table and the DS21 Rates table. Ensure that for every resource listed in the DS06 Resources table, there is a corresponding rate in the DS21 Rates table. The 'resource_id' field in both tables should match.
Please note that this DIQ check is only performed when there are rates present in the DS21 Rates table. If the DS21 Rates table is empty, this check will not be performed.
This test is being performed to ensure that all resources in the 'DS06 Resources' table have corresponding rates. The test checks if there are any resources that are missing their rates, which is crucial for accurate project management and cost estimation. The severity of this test is marked as 'CRITICAL', which is the highest level of severity. This means that if any resource is found without a rate, it is a critical issue that must be fixed before the data can be reviewed or used for further analysis. The absence of rates for resources can lead to inaccurate project cost calculations and can significantly impact the project's budgeting and scheduling. Therefore, this check is of utmost importance to maintain the integrity and quality of the EVMS construction project management data.
CREATE FUNCTION [dbo].[fnDIQ_DS06_Res_IsResourceMissingInDS21] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with Rates as (
SELECT resource_ID
FROM DS21_rates
WHERE upload_ID = @upload_ID
)
SELECT
*
FROM
DS06_schedule_resources
WHERE
upload_id = @upload_ID
AND resource_ID NOT IN (SELECT resource_ID FROM Rates)
AND (SELECT COUNT(*) FROM Rates) > 0 --run only if there are rates in DS21
)