¶ Basic Information
| Key | Value |
|---|---|
| Table | DS11 Variance |
| Severity | MAJOR |
| Unique ID | 9110482 |
| Summary | Is the approved date for this variance later than the dates for the corrective action date? |
| Error message | approved_date > DS12.initial_date, DS12.original_due_date, DS12.forecast_due_date, or DS12.closed_date (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 "Approved Date After CAL Dates" is designed to ensure that the approved date for a variance in the DS11 Variance table is not later than the dates associated with the corrective action log (CAL) in the DS12 Variance CAL table.
The fields involved in this check are the 'approved_date' from the DS11 Variance table and the 'initial_date', 'original_due_date', 'forecast_due_date', and 'closed_date' from the DS12 Variance CAL table.
An error will be flagged if the 'approved_date' in the DS11 Variance table is later than any of the dates in the DS12 Variance CAL table. This could be due to a data entry error or a delay in the approval process.
The expected values for the 'approved_date' should be a date that is earlier than or equal to the 'initial_date', 'original_due_date', 'forecast_due_date', and 'closed_date' in the DS12 Variance CAL table.
Please ensure that the dates are entered correctly and that the approval process is completed in a timely manner to avoid any discrepancies in the data.
¶ Why do we check this?
This test is being performed on the 'DS11 Variance' table to ensure that the approved date for a variance is not later than the dates for the corrective action. The test checks if the 'approved_date' is greater than any of the dates in the 'DS12' table, namely 'initial_date', 'original_due_date', 'forecast_due_date', or 'closed_date'.
The importance of this check is to maintain the chronological order of events in the project management data. If the approved date for a variance is later than the corrective action dates, it could imply that the approval was given after the corrective action was initiated or completed, which is not a logical sequence of events. This could lead to confusion and misinterpretation of data during analysis.
The severity of this check is marked as 'MAJOR', which means that while it may not prevent the data from being reviewed, it is likely to cause problems during analysis. Therefore, it is recommended to address this issue to ensure the accuracy and reliability of the data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS11_VAR_IsApprovedDateGtCALDates] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with VARsByCAL as (
SELECT WBS_ID, approved_date, CAL_ID
FROM DS11_variance CROSS APPLY string_split(CAL_ID, ';')
WHERE upload_ID = @upload_ID
), Flags as (
SELECT WBS_ID
FROM VARsByCAL V INNER JOIN DS12_variance_CAL C ON V.CAL_ID = C.CAL_ID
AND (
V.approved_date > C.initial_date
OR V.approved_date > C.original_due_date
OR V.approved_date > C.forecast_due_date
OR V.approved_date > C.closed_date
)
WHERE C.upload_ID = @upload_ID
)
SELECT
V.*
FROM
DS11_variance V INNER JOIN Flags F ON V.WBS_ID = F.WBS_ID
WHERE
V.upload_ID = @upload_ID
)