¶ Basic Information
| Key | Value |
|---|---|
| Table | DS04 Schedule |
| Severity | MINOR |
| Unique ID | 9040187 |
| Summary | Is this LOE task missing at least one FF and SS predecessor (both discrete)? |
| Error message | DS04.type = LOE or DS04.EVT = A without DS05.type = FF and SS relationships. |
¶ 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 "LOE Task With Improper Logic" is designed to identify any Level of Effort (LOE) tasks in the DS04 Schedule table that are missing at least one Finish-to-Finish (FF) and Start-to-Start (SS) predecessor. These predecessors should be discrete tasks, meaning they are not LOE tasks and do not have an Event Type (EVT) of 'A', 'K', or 'M'.
The error message "DS04.type = LOE or DS04.EVT = A without DS05.type = FF and SS relationships" indicates that the DIQ check has identified a LOE task or a task with an EVT of 'A' in the DS04 Schedule table that does not have the required FF and SS relationships in the DS05 Schedule Logic table.
This error is likely caused by incomplete or incorrect data entry. For example, a LOE task may have been entered into the DS04 Schedule table without the corresponding FF and SS relationships being entered into the DS05 Schedule Logic table. Alternatively, the FF and SS relationships may have been entered, but they may not be associated with discrete tasks.
To resolve this error, review the LOE tasks and their associated relationships in the DS04 Schedule and DS05 Schedule Logic tables. Ensure that each LOE task has at least one FF and SS predecessor, and that these predecessors are discrete tasks.
¶ Why do we check this?
This test is being performed to ensure that Level of Effort (LOE) tasks in the DS04 Schedule table have the correct logic applied. Specifically, it checks if each LOE task has at least one Finish-to-Finish (FF) and Start-to-Start (SS) predecessor, both of which should be discrete tasks.
The importance of this check is to maintain the integrity and accuracy of the project schedule. LOE tasks are ongoing tasks that span the duration of the project and their scheduling is dependent on other tasks. If they lack the proper FF and SS relationships, it could lead to inaccuracies in the project timeline and potentially cause delays or inefficiencies.
The severity of this check is marked as an MINOR. This means that while it may not immediately prevent data review or cause significant problems during analysis, it could potentially lead to minor issues or indicate that the data does not fully adhere to best practices. Therefore, it is recommended to address this issue to ensure optimal data quality and project management efficiency.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS04_Sched_IsLOELogicIncorrect] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with Tasks as (
SELECT schedule_type, task_ID, type, ISNULL(EVT,'') EVT, ISNULL(subproject_ID,'') SubP
FROM DS04_schedule
WHERE upload_ID = @upload_ID
), Logic as (
SELECT schedule_type, task_ID, predecessor_task_ID, type, ISNULL(subproject_ID,'') SubP, ISNULL(predecessor_subproject_ID,'') PSubP FROM DS05_schedule_logic WHERE upload_ID = @upload_id
), LOELogic as (
SELECT L.*
FROM Logic L INNER JOIN Tasks LOE ON L.schedule_type = LOE.schedule_type
AND L.task_ID = LOE.task_ID
AND L.SubP = LOE.SubP
WHERE LOE.[type] = 'LOE' OR LOE.EVT IN ('A', 'J', 'M')
), LOEsWithDisPreds as (
SELECT L.schedule_type, L.task_ID, L.SubP, STRING_AGG(L.type, ',') WITHIN GROUP (ORDER BY L.type) Type
FROM LOELogic L INNER JOIN Tasks Discrete ON L.schedule_type = Discrete.schedule_type
AND L.predecessor_task_ID = Discrete.task_ID
AND L.PSubP = Discrete.SubP
WHERE Discrete.Type IN ('TD', 'RD') AND Discrete.EVT NOT IN ('A','J','M')
GROUP BY L.schedule_type, L.task_ID, L.SubP
)
SELECT S.*
FROM DS04_schedule S LEFT OUTER JOIN LOEsWithDisPreds L ON S.schedule_type = L.schedule_type
AND S.task_ID = L.task_ID
AND ISNULL(S.subproject_ID,'') = L.SubP
WHERE upload_id = @upload_ID
AND (S.type = 'LOE' or S.EVT IN ('A', 'J', 'M'))
AND (
L.[Type] NOT LIKE '%SS%FF%' AND L.[Type] NOT LIKE '%FF%SS%' OR
L.task_ID IS NULL
)
UNION
SELECT S.*
FROM DS04_schedule S INNER JOIN LOELogic L ON S.schedule_type = L.schedule_type
AND S.task_ID = L.predecessor_task_ID
AND ISNULL(S.subproject_ID,'') = L.PSubP
WHERE S.upload_id = @upload_ID AND (S.[type] = 'LOE' OR S.EVT IN ('A', 'J', 'M'))
)
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2024-04-30 | Logic overhaul. DIQ now flags the following LOE & apportioned tasks (EVT = A, J, M or type = LOE): 1. Those without two predecessors (one SS, one FF); 2. Those with successors; 3. Those without immediate, discrete predecessors. Logic also adjusted to account for the addition of 'subproject_ID' field. |