¶ Basic Information
| Key | Value |
|---|---|
| Table | DS15 Risk Register |
| Severity | MINOR |
| Unique ID | 9150551 |
| Summary | Is the closed date before all the risk's tasks have finished? |
| Error message | closed_date < FC DS04.EF_date (by DS15.risk_ID & DS16.risk_ID, and DS16.task_ID & DS04.task_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 "Risk Closed Before Risk Tasks Have Completed" is designed to ensure that the closed date of a risk is not set before all associated tasks have been completed. This check is performed on the DS15 Risk Register.
The error message "closed_date < FC DS04.EF_date (by DS15.risk_ID & DS16.risk_ID, and DS16.task_ID & DS04.task_ID)" indicates that the closed date of a risk is earlier than the earliest finish (EF) date of its associated tasks. This discrepancy is identified by matching the risk IDs in DS15 and DS16, and the task IDs in DS16 and DS04.
The likely cause of this error is an incorrect entry in the closed date field of the DS15 Risk Register. The closed date of a risk should be set after all its associated tasks have been completed, i.e., it should be later than the earliest finish (EF) date of its tasks. If the closed date is set earlier, it suggests that the risk was marked as closed before all its tasks were finished, which is not a valid scenario.
To resolve this issue, review the closed dates of risks in the DS15 Risk Register and the earliest finish (EF) dates of their associated tasks in the DS04 Schedule. Ensure that the closed date of each risk is set after the EF date of all its tasks. If necessary, correct the closed date entries in the DS15 Risk Register to reflect the actual completion of the tasks and the closure of the risks.
¶ Why do we check this?
This test is being performed to ensure that no risk is marked as closed before all associated tasks have been completed. The test checks if the 'closed date' of a risk is earlier than the 'finish date' of any of its tasks. This is important because prematurely closing a risk could lead to overlooking unresolved tasks associated with that risk, which could potentially cause problems in the project later on.
The severity of this test is marked as 'MINOR', which means it is not a critical issue that would prevent the data from being reviewed, but it is still important to address. If this issue is not corrected, it could lead to minor problems in data analysis or indicate that the data does not follow all best practices. It is crucial to maintain data integrity and quality to ensure accurate and reliable project management.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS15_Risk_Register_IsClosedDateLtRiskTaskEFDate] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with RiskEF as (
SELECT risk_ID, MIN(EF_date) EF
FROM DS16_risk_register_tasks R INNER JOIN DS04_schedule S ON R.task_ID = S.task_ID
WHERE R.upload_ID = @upload_ID AND S.upload_ID = @upload_ID AND S.schedule_type = 'FC'
GROUP BY risk_ID
)
SELECT
RR.*
FROM
DS15_risk_register RR INNER JOIN RiskEF R ON RR.risk_ID = R.risk_ID
WHERE
upload_ID = @upload_ID
AND RR.closed_date < R.EF
)