¶ Basic Information
| Key | Value |
|---|---|
| Table | DS06 Resources |
| Severity | MINOR |
| Unique ID | 1060246 |
| Summary | Is this labor resource scheduled to work on multiple tasks at the same time? |
| Error message | Labor resource start/finish dates overlap across multiple task_IDs. |
¶ 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 "Labor Overlap" is designed to identify instances where a labor resource is scheduled to work on multiple tasks simultaneously. This check is performed on the data in the DS06 Resources table.
The error message "Labor resource start/finish dates overlap across multiple task_IDs" indicates that there are labor resources with overlapping start and finish dates across different tasks. This could be due to an error in scheduling or data entry.
The fields causing this issue are the 'start_date' and 'finish_date' fields for each labor resource in the DS06 Resources table. The 'task_ID', 'resource_ID', and 'schedule_type' fields are also involved in this check as they help identify the specific tasks and resources where the overlap occurs.
The expected values for the 'start_date' and 'finish_date' fields should be such that no labor resource is scheduled to work on more than one task at the same time. In other words, for any given labor resource, the 'start_date' for a new task should not be earlier than the 'finish_date' of any ongoing task.
If this DIQ check identifies any instances of labor overlap, it may be necessary to review and adjust the scheduling of tasks and allocation of labor resources in the DS06 Resources table.
¶ Why do we check this?
This test is being performed to check for any instances where a labor resource is scheduled to work on multiple tasks at the same time. The test is checking for overlapping start and finish dates across multiple task IDs in the 'DS06 Resources' table.
The importance of this check is to ensure that resources are not over-allocated, which could lead to unrealistic project timelines and potential delays. It is crucial to have accurate scheduling in project management to ensure that tasks are completed on time and within budget.
The severity of this test is marked as an MINOR. This means that while it may not prevent the data from being reviewed, it could potentially cause minor problems or indicate that the data does not follow all best practices. It's a signal to review the resource allocation and scheduling to ensure it is accurate and feasible.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS06_Res_DoLaborDatesOverlap] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with Flags as (
SELECT R1.task_ID, R1.resource_ID, R1.schedule_type, ISNULL(R1.subproject_ID,'') SubP
FROM DS06_schedule_resources R1 INNER JOIN DS06_schedule_resources R2 ON R1.schedule_type = R2.schedule_type
AND R1.resource_ID = R2.resource_ID
AND ISNULL(R1.subproject_ID, '') = ISNULL(R2.subproject_ID, '')
AND R1.task_ID <> R2.task_ID
AND R1.start_date <= R2.finish_date
AND R2.start_date <= R1.finish_date
WHERE R1.upload_ID = @upload_ID
AND R2.upload_ID = @upload_ID
AND (R1.EOC = 'Labor' OR R1.[type] = 'Labor')
AND (R2.EOC = 'Labor' OR R2.[type] = 'Labor')
GROUP BY R1.task_ID, R1.resource_ID, R1.schedule_type, ISNULL(R1.subproject_ID,'')
)
SELECT R.*
FROM DS06_schedule_resources R INNER JOIN Flags F ON R.schedule_type = F.schedule_type
AND R.task_ID = F.task_ID
AND R.resource_ID = F.resource_ID
AND ISNULL(R.subproject_ID, '') = F.SubP
WHERE upload_ID = @upload_ID AND (EOC = 'Labor' OR [type] = 'Labor')
)
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2024-04-30 | Logic adjusted to account for the addition of subproject_ID field. Minor organizational changes otherwise. |