¶ Basic Information
| Key | Value |
|---|---|
| Table | DS21 Rates |
| Severity | MINOR |
| Unique ID | 9210602 |
| Summary | Is this resource EOC missing in the forecast resources? |
| Error message | resource_ID & EOC combo not found in DS06.resource_ID & DS06.EOC (where schedule_type = FC). |
¶ 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 "Resource & EOC Combo Missing in Forecast Resources" is designed to ensure that each combination of resource ID and EOC (Element of Cost) in the DS21 Rates table is also present in the DS06 Schedule Resources table, specifically for forecasted (FC) schedule types.
If this DIQ check fails, it indicates that there are one or more combinations of resource ID and EOC in the DS21 Rates table that do not have a corresponding entry in the DS06 Schedule Resources table for forecasted schedule types. This discrepancy could be due to a data entry error, a missing entry in the DS06 table, or a mismatch between the resource ID and EOC values in the two tables.
To resolve this issue, you should verify that each resource ID and EOC combination in the DS21 Rates table matches an entry in the DS06 Schedule Resources table for forecasted schedule types. If any mismatches are found, they should be corrected to ensure data integrity and quality.
¶ Why do we check this?
This test is being performed to ensure that the combination of resource ID and EOC (Estimate of Completion) is present in the forecast resources. The test is checking the 'DS21 Rates' table and comparing it with the 'DS06' table to ensure that the same resource ID and EOC combination exists in both, specifically where the schedule type is 'FC' (Forecast).
The importance of this check is to maintain data consistency and integrity across different tables. If the same resource ID and EOC combination is not found in both tables, it could lead to discrepancies in data analysis and forecasting, potentially causing errors in project management decisions.
The severity of this check is marked as 'MINOR', which means it's not a critical error that would prevent data review, but it's a potential issue that could cause minor problems or indicate that the data doesn't follow all best practices. Therefore, it's recommended to address this issue to ensure optimal data quality and integrity.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS21_Rates_IsWBSAndEOCComboMissingInDS06] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
SELECT
R.*
FROM
DS21_rates R LEFT OUTER JOIN DS06_schedule_resources Res ON R.resource_ID = Res.resource_ID AND R.EOC = Res.EOC
WHERE
R.upload_ID = @upload_ID
AND Res.upload_ID = @upload_ID
AND Res.schedule_type = 'FC'
AND Res.resource_ID IS NULL
)