¶ Basic Information
| Key | Value |
|---|---|
| Table | DS11 Variance |
| Severity | MINOR |
| Unique ID | 9110497 |
| Summary | Is this SVi or SVc VAR narrative missing a corrective action log entry with a schedule narrative? |
| Error message | narrative_RC_SVc or narrative_RC_SVi found without DS12.narrative_schedule (by CAL_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.
The Data Integrity and Quality (DIQ) check titled "SV Root Cause Narrative Missing CAL Schedule Narrative" is designed to ensure that each Schedule Variance (SV) root cause narrative in the DS11 Variance table is associated with a Corrective Action Log (CAL) entry that includes a schedule narrative in the DS12 Variance CAL table.
The error message "narrative_RC_SVc or narrative_RC_SVi found without DS12.narrative_schedule (by CAL_ID)" indicates that there is a Schedule Variance root cause narrative (either narrative_RC_SVc or narrative_RC_SVi) in the DS11 Variance table that does not have a corresponding schedule narrative in the DS12 Variance CAL table.
This discrepancy could be due to a missing row in the DS12 Variance CAL table or a blank narrative_schedule field for the corresponding CAL_ID. It could also be due to an error in the CAL_ID field in the DS11 Variance table, such as a missing or incorrect CAL_ID.
To resolve this issue, ensure that each SV root cause narrative in the DS11 Variance table has a corresponding CAL entry with a schedule narrative in the DS12 Variance CAL table. The CAL_ID field in the DS11 Variance table should match the CAL_ID field in the DS12 Variance CAL table.
¶ Why do we check this?
This test is being performed to ensure that every Schedule Variance (SV) root cause narrative in the DS11 Variance table has a corresponding corrective action log entry with a schedule narrative in the DS12 table. The test checks for instances where 'narrative_RC_SVc' or 'narrative_RC_SVi' are found without a corresponding 'DS12.narrative_schedule' entry, linked by the Corrective Action Log ID (CAL_ID).
The importance of this check is to maintain consistency and completeness in the data. It ensures that every identified schedule variance issue has a corresponding corrective action plan, which is crucial for effective project management. Without this, there may be schedule variance issues that are not being addressed, which could lead to project delays or cost overruns.
The severity of this check is classified as an MINOR. This means that while it's not a critical issue that would prevent the data from being reviewed, it's still a potential problem that could cause minor issues during data analysis. It also indicates that the data does not fully adhere to best practices for data integrity and quality. Therefore, it's recommended to address this issue to improve the overall quality of the project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS11_VAR_IsSVNarrMissingDS12SchedNarr] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with VARsByCAL as (
--WBS IDs with CAL IDs broken out into rows
--filtered for rows where there is a SV RC narrative
SELECT WBS_ID, CAL_ID
FROM DS11_variance CROSS APPLY string_split(CAL_ID, ';')
WHERE
upload_ID = @upload_ID
AND (TRIM(ISNULL(narrative_RC_SVi,'')) <> '' OR TRIM(ISNULL(narrative_RC_SVc,'')) <> '')
), Flags as (
--WBS IDs where no schedule narrative exists in DS12
--either because the row is missing or because the narrative_schedule is blank
SELECT V.WBS_ID
FROM VARsByCAL V LEFT OUTER JOIN DS12_variance_CAL C ON V.CAL_ID = C.CAL_ID
WHERE
C.upload_ID = @upload_ID
AND (TRIM(ISNULL(C.narrative_schedule,'')) = '' OR C.CAL_ID IS NULL)
)
SELECT
*
FROM
DS11_variance
WHERE
upload_ID = @upload_ID
and WBS_ID IN (SELECT WBS_ID FROM Flags)
)