¶ Basic Information
| Key | Value |
|---|---|
| Table | DS09 CC Log |
| Severity | MINOR |
| Unique ID | 1090449 |
| Summary | Are risk IDs missing in the CC log? |
| Error message | Count of CC log entries > 5 & Count of risk_id = 0. |
¶ 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 Missing" is designed to identify if there are missing risk IDs in the DS09 CC Log. This check is important as each entry in the CC Log should be associated with a risk ID.
The DIQ check is triggered when there are more than five entries in the CC Log, but none of these entries have an associated risk ID. This could be due to a data entry error, where the risk ID was not entered, or a system error, where the risk ID was not properly linked to the CC Log entry.
The field causing the issue is the 'risk_ID' field in the DS09 CC Log. The expected value for this field is a valid risk ID. If the DIQ check identifies an issue, it means that the 'risk_ID' field is either blank or contains an invalid value.
To resolve this issue, review the entries in the DS09 CC Log and ensure that each entry has a valid risk ID associated with it.
¶ Why do we check this?
This test is being performed on the 'DS09 CC Log' table to check if there are any missing risk IDs in the CC log. The test is looking for instances where the count of CC log entries is greater than 5 and the count of risk_id is equal to 0. This means that there are CC log entries that do not have an associated risk ID, which could potentially cause issues in tracking and managing risks associated with the construction project.
The severity of this test is marked as 'MINOR', which is less severe but indicates that there might be minor problems or that the data doesn't follow all best practices. The importance of this check is to ensure that all CC log entries are properly linked with a risk ID. This is crucial for effective risk management in the project, as it allows for the identification, analysis, and prioritization of risks. Without a proper link to a risk ID, it would be difficult to track the source of a problem if one arises, which could lead to delays and cost overruns in the project.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS09_CCLog_IsRiskMissing] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CCLogEntries as (
SELECT COUNT(*) CCLogEntries
FROM DS09_CC_log
WHERE upload_ID = @upload_ID
), RiskRows as (
SELECT COUNT(*) RiskRows
FROM DS09_CC_log
WHERE upload_ID = @upload_ID AND TRIM(ISNULL(risk_ID,'')) <> ''
)
SELECT
*
FROM
DummyRow_Get(@upload_ID)
WHERE
(SELECT TOP 1 CCLogEntries FROM CCLogEntries) > 5 --more than 5 CC log entries?
AND (SELECT TOP 1 RiskRows FROM RiskRows) = 0 --no risks?
)