¶ Basic Information
| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | MAJOR |
| Unique ID | 1030075 |
| Summary | Is the budget for this 50-50 work spread improperly? (Must be across two consecutive periods and with the same value.) |
| Error message | 50-50 work (EVT = E) where BCWSi (Dollar, Hours, or FTEs) was found in either one period only or more than two, non-consecutive periods more than 45 days apart, or spread unevenly. |
¶ 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 "50-50 Budget Spread Improperly" is designed to ensure that the budget for 50-50 work is spread correctly across two consecutive periods and with the same value. This check is performed on the DS03 Cost table.
The error message "50-50 work (EVT = E) where BCWSi (Dollar, Hours, or FTEs) was found in either one period only or more than two, non-consecutive periods more than 45 days apart, or spread unevenly" indicates that the budget for 50-50 work is not spread correctly.
This could be due to several reasons:
-
The budget for 50-50 work (where EVT = E) is found in only one period. The budget should be spread across two consecutive periods.
-
The budget for 50-50 work is found in more than two periods, or the periods are not consecutive and are more than 45 days apart. The budget should be spread across two consecutive periods only.
-
The budget for 50-50 work is spread unevenly. The budget should be spread with the same value across the two periods.
The fields causing this issue are WBS_ID_WP, period_date, BCWSi_dollars, BCWSi_hours, BCWSi_FTEs, and EOC. The expected values for these fields should ensure that the budget for 50-50 work is spread evenly across two consecutive periods.
¶ Why do we check this?
This test is being performed to ensure that the budget for 50-50 work is spread correctly across two consecutive periods and with the same value. The 50-50 rule is a common practice in project management where the work is considered 50% complete when it begins and the remaining 50% when it ends. This rule is applied to tasks that are short in duration and have a well-defined scope.
The importance of this check is to ensure that the budget allocation is accurate and consistent with the project's timeline. If the budget is spread improperly, it could lead to financial discrepancies, misallocation of resources, and potential delays in the project timeline.
The severity of this test is marked as a MAJOR. This means that while it may not immediately prevent the data from being reviewed, it is likely to cause problems during the analysis. It is crucial to address this issue to ensure accurate budgeting and efficient project management.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_Is5050BCWSImproperlySpread] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with SSpread as (
SELECT
WBS_ID_WP,
LAG(period_date,1) OVER (PARTITION BY WBS_ID_WP, EOC, ISNULL(is_indirect,'') ORDER BY period_date) AS PrevPeriod,
period_date [Period],
LEAD(period_date,1) OVER (PARTITION BY WBS_ID_WP, EOC, ISNULL(is_indirect,'') ORDER BY period_date) AS NextPeriod,
BCWSi_dollars s_dollars,
BCWSi_hours s_hours,
BCWSi_FTEs s_ftes,
ISNULL(LEAD(BCWSi_dollars,1) OVER (PARTITION BY WBS_ID_WP, EOC, ISNULL(is_indirect,'') ORDER by period_date),0) AS NextS_dollars,
ISNULL(LAG(BCWPi_dollars,1) OVER (PARTITION BY WBS_ID_WP, EOC, ISNULL(is_indirect,'') ORDER by period_date),0) AS PrevS_dollars,
ISNULL(LEAD(BCWSi_hours,1) OVER (PARTITION BY WBS_ID_WP, EOC, ISNULL(is_indirect,'') ORDER by period_date),0) AS NextS_hours,
ISNULL(LAG(BCWPi_hours,1) OVER (PARTITION BY WBS_ID_WP, EOC, ISNULL(is_indirect,'') ORDER by period_date),0) AS PrevS_hours,
ISNULL(LEAD(BCWSi_ftes,1) OVER (PARTITION BY WBS_ID_WP, EOC, ISNULL(is_indirect,'') ORDER by period_date),0) AS NextS_ftes,
ISNULL(LAG(BCWPi_ftes,1) OVER (PARTITION BY WBS_ID_WP, EOC, ISNULL(is_indirect,'') ORDER by period_date),0) AS PrevS_ftes,
EOC,
ISNULL(is_indirect,'') IsInd
FROM DS03_cost
WHERE upload_ID = @upload_ID AND EVT = 'E'
),
Flags as (
SELECT WBS_ID_WP, [period], EOC, IsInd
FROM SSpread
WHERE
(ABS(s_dollars) > 100 OR ABS(s_hours) > 1 OR s_ftes > 0) --Get only rows where |BCWSi| > 100 (threshold of $100/1hr)
AND ((
(ABS(PrevS_dollars) > 100 AND ABS(NextS_dollars) > 100) OR -- Are the prev & next $ amounts both > $100? (Both provided)
(ABS(PrevS_dollars) < 100 AND ABS(NextS_dollars) < 100) -- Are the prev & next $ amounts both < $100? (Both missing)
) OR (
(ABS(PrevS_hours) > 1 AND ABS(s_hours) > 1) OR -- Are the prev & next hours both > 1? (Both provided)
(ABS(PrevS_hours) < 1 AND ABS(s_hours) < 1) -- Are the prev & next hours both < 1? (Both missing)
)
OR (
(PrevS_ftes = 0 AND NextS_ftes = 0) OR --Are prev/next FTEs both = 0?
(PrevS_ftes > 0 AND NextS_ftes > 0) --Are prev/next FTEs both > 0?
)
OR (
DATEDIFF(day, PrevPeriod, [period]) >= 45 OR DATEDIFF(day, [period], NextPeriod) >= 45 --Are the prev/next periods > 45 days from the current?
) OR (
ABS(s_dollars - PrevS_dollars) > 100 OR ABS(s_dollars - NextS_dollars) > 100 OR --Is there more than a $100 delta between current period and prev/next?
ABS(s_hours - PrevS_hours) > 1 OR ABS(s_hours - NextS_hours) > 1 --Is there more than a 1 hour delta between current period and prev/next?
)
)
)
SELECT
C.*
FROM
DS03_Cost C INNER JOIN Flags F ON C.WBS_ID_WP = F.WBS_ID_WP
AND C.period_date = F.period
AND C.EOC = F.EOC
AND ISNULL(C.is_indirect,'') = F.IsInd
WHERE
upload_ID = @upload_ID
AND EVT = 'E'
AND TRIM(ISNULL(C.WBS_ID_WP,'')) <> ''
)
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2024-04-30 | Logic adjusted to account for the addition of the is_indirect field. All cases where is_indirect = 'Y' are treated as indirect data. |
Logic also adjusted to include a filter that excludes records with empty WBS_ID_WP fields. |