¶ Basic Information
| Key | Value |
|---|---|
| Table | DS06 Resources |
| Severity | MINOR |
| Unique ID | 1060251 |
| Summary | Is this task lacking a resource ID planned to start soon (within 3 months)? |
| Error message | Resource item without an ID is planned to start within three months (resource_ID is blank & start_date is within 3 months of CPP SD). |
¶ 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 Task without Resource Assigned Starting Soon" is designed to identify any tasks in the DS06 Resources table that are scheduled to start within the next three months but do not have a resource assigned to them.
The error is likely to occur when a task has a role assigned (role_ID is not null) but lacks a corresponding resource ID (resource_ID is blank). This situation could arise due to an oversight during data entry or a miscommunication between teams.
The fields causing the issue are 'role_ID' and 'resource_ID'. The 'role_ID' field should not be null, indicating that a role has been assigned for the task. However, the 'resource_ID' field is blank, suggesting that no resource has been assigned to carry out the task.
The 'start_date' field is also involved in this check. The test is looking for tasks that are planned to start within the next three months from the CPP status date.
To resolve this issue, ensure that every task with a role assigned also has a corresponding resource assigned. This means that for every non-null 'role_ID', there should be a non-blank 'resource_ID'.
¶ Why do we check this?
This test is being performed to ensure that all tasks in the 'DS06 Resources' table that are planned to start soon (within 3 months) have a resource assigned to them. The test checks for tasks where the 'resource_ID' field is blank, indicating that no resource has been assigned to the task.
The importance of this check is to ensure that all upcoming tasks have the necessary resources allocated to them. If a task is scheduled to start soon but does not have a resource assigned, it could lead to delays in the project timeline or other operational issues.
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 a potential problem that should be addressed. It is a best practice to assign resources to tasks in advance to ensure smooth project execution.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS06_Res_IsResourceWithoutIDStartingSoon] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
SELECT
*
FROM
DS06_schedule_resources
WHERE
upload_id = @upload_ID
AND role_ID IS NOT NULL
AND ISNULL(resource_ID,'') = ''
AND DATEDIFF(d,CPP_status_date,start_date) < 90
)