| Key | Value |
|---|---|
| Table | DS06 Resources |
| Severity | CRITICAL |
| Unique ID | 9060301 |
| Summary | Is the task this resource is assigned to missing in the schedule? |
| Error message | Task_ID missing in DS04.task_ID list (by schedule_type & subproject_ID). |
The Data Integrity and Quality (DIQ) check titled "Resource Task Missing in Schedule" is designed to ensure that all tasks assigned to resources in the DS06 Resources table are also present in the DS04 Schedule table.
The error message "Task_ID missing in DS04.task_ID list (by schedule_type & subproject_ID)" indicates that there is a discrepancy between the tasks listed in the DS06 Resources table and the DS04 Schedule table. This discrepancy is likely due to a task being assigned to a resource in the DS06 Resources table, but the same task not being listed in the DS04 Schedule table.
The fields causing this issue are the 'task_ID', 'schedule_type', and 'subproject_id' fields in both the DS06 Resources and DS04 Schedule tables. Both sets of fields should match between the two tables.
To resolve this issue, ensure that all tasks assigned to resources in the DS06 Resources table are also listed in the DS04 Schedule table.
This test is being performed to ensure that all tasks assigned to resources in the 'DS06 Resources' table are also present in the schedule, specifically in the 'DS04.task_ID' list. The importance of this check is to maintain data integrity and consistency across different tables and datasets. If a task assigned to a resource is missing in the schedule, it could lead to mismanagement of resources, scheduling conflicts, and inaccurate project tracking.
The severity of this test is marked as 'CRITICAL', which is the highest level of severity. This means that if a task is missing in the schedule, it is a critical issue that must be fixed immediately. If not addressed, the data cannot be reviewed or used for further analysis, which could significantly impact the project's management and completion.
CREATE FUNCTION [dbo].[fnDIQ_DS06_Res_IsTaskMissingInDS04] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with Tasks as (
SELECT schedule_type, task_ID, ISNULL(subproject_ID,'') SubP
FROM DS04_schedule
WHERE upload_ID = @upload_ID
)
SELECT R.*
FROM DS06_schedule_resources R LEFT OUTER JOIN Tasks T ON R.schedule_type = T.schedule_type
AND R.task_ID = T.task_ID
AND ISNULL(R.subproject_ID,'') = T.SubP
WHERE R.upload_id = @upload_ID
AND TRIM(ISNULL(R.task_ID,'')) <> ''
AND T.task_ID IS NULL
)
| Date | Description of Changes |
|---|---|
| 2024-04-30 | Logic adjusted to account for the addition of 'subproject_id' field. |