¶ Basic Information
| Key | Value |
|---|---|
| Table | DS04 Schedule |
| Severity | MAJOR |
| Unique ID | 1040186 |
| Summary | Is LOE mingled with other EVT types for this WBS? |
| Error message | LOE (EVT = LOE) is mingled with other EVT types (EVT <> LOE) for this WBS_ID. |
¶ 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 Mingled With Other EVT Types" is designed to ensure that for each Work Breakdown Structure (WBS) ID in the DS04 Schedule table, the Level of Effort (LOE) is not mixed with other Earned Value Types (EVTs).
The LOE is identified by the EVT code 'A', while other EVTs are represented by different codes. The check groups the data by WBS ID and schedule type, and then compares the EVT codes within each group. If the LOE code 'A' is found within a group that also contains other EVT codes, the check will fail for that WBS ID and schedule type.
The error message "LOE (EVT = LOE) is mingled with other EVT types (EVT <> LOE) for this WBS_ID" indicates that the LOE is mixed with other EVTs for the specified WBS ID. This could be due to incorrect data entry or a problem with the data source.
To resolve this issue, you should review the EVT codes for the specified WBS ID and schedule type in the DS04 Schedule table. Ensure that the LOE is not mixed with other EVTs within the same group. If necessary, correct the EVT codes to ensure data integrity and quality.
¶ Why do we check this?
This test is being performed to check if Level of Effort (LOE) is mixed with other Earned Value Techniques (EVT) types for a particular Work Breakdown Structure (WBS) ID in the DS04 Schedule table. The LOE is a specific type of EVT that is used to measure the performance of non-discrete tasks that do not produce tangible outputs. Mixing LOE with other EVT types can lead to inaccurate performance measurements and can distort the project's overall progress.
The severity of this test is marked as a MAJOR, which means that while it may not prevent the data from being reviewed, it is likely to cause problems during analysis. This is because the mingling of different EVT types can lead to misleading results when assessing the project's performance and progress. Therefore, it is crucial to address this issue to ensure the accuracy and reliability of the project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS04_Sched_AreEVTsComingled] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with EVTGroups as (
-- WBS IDs, schedule type, and EVTs by group
SELECT
WBS_ID,
schedule_type,
CASE
WHEN EVT IN ('B', 'C', 'D', 'E', 'F', 'G', 'H', 'L', 'N', 'O', 'P') THEN 'Discrete'
WHEN EVT = 'A' THEN 'LOE'
WHEN EVT IN ('J', 'M') THEN 'Apportioned'
WHEN EVT = 'K' THEN 'PP'
END as EVT
FROM DS04_schedule
WHERE upload_ID = @upload_ID AND TRIM(ISNULL(EVT, '')) <> ''
), Flags AS (
--Compare. Group by WBS ID & schedule_type to return only distinct WBS IDs by schedule type
--that fail the test.
SELECT G1.WBS_ID, G1.schedule_type
FROM EVTGroups G1 INNER JOIN EVTGroups G2 ON G1.WBS_ID = G2.WBS_ID
AND G1.schedule_type = G2.schedule_type
AND G1.EVT <> G2.EVT
WHERE G1.EVT <> '' AND G2.EVT <> ''
GROUP BY G1.WBS_ID, G1.schedule_type
)
SELECT
S.*
FROM
DS04_schedule S INNER JOIN Flags F ON S.WBS_ID = F.WBS_ID AND S.schedule_type = F.schedule_type
WHERE
upload_id = @upload_ID
)