¶ Basic Information
| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | MINOR |
| Unique ID | 1030066 |
| Summary | Does this WP or PP show estimates a year or more after the last recorded period of budget? |
| Error message | Last period_date where ETCi <> 0 is twelve or more months after last period_date of BCWSi <> 0 (on Dollars, Hours, or FTEs). |
¶ 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 "Estimates Found A Year or More After Last Recorded Budget" is designed to identify any Work Packages (WP) or Planning Packages (PP) in the DS03 Cost table that show estimates occurring a year or more after the last recorded budget period.
This check is important because it helps to ensure that the budgeting and estimation process is being carried out in a timely manner. If there are estimates occurring a year or more after the last budget period, it could indicate a delay in the budgeting process or a discrepancy in the data.
The check works by comparing the last period where the Estimate to Complete (ETCi) is not zero with the last period where the Budgeted Cost of Work Scheduled (BCWSi) is not zero. If the last ETCi period is twelve or more months after the last BCWSi period, an error is flagged. This comparison is made for dollars, hours, and Full-Time Equivalents (FTEs).
The fields that are likely causing the issue are the ETCi and BCWSi fields in the DS03 Cost table. The expected values for these fields should be such that the last ETCi period is not twelve or more months after the last BCWSi period. If this is not the case, it may indicate a delay in the budgeting process or a discrepancy in the data.
The results of this check are grouped by the Work Breakdown Structure (WBS) ID for the Control Account (CA) and the WBS ID for the Work Package (WP). This allows you to easily identify the specific WBS IDs that are causing the issue.
¶ Why do we check this?
This test is being performed on the 'DS03 Cost' table to check for any work packages (WP) or planning packages (PP) that show estimates a year or more after the last recorded period of budget. The test is designed to identify any instances where the last period where the estimate to complete (ETCi) is not zero is twelve or more months after the last period where the budgeted cost of work scheduled (BCWSi) is not zero. This could be in terms of dollars, hours, or full-time equivalents (FTEs).
The importance of this check is to ensure that there are no significant delays in the project that have not been accounted for in the budget. If there are estimates that are a year or more after the last recorded budget, it could indicate that the project is running behind schedule or that there are unanticipated costs that have not been included in the budget. This could potentially lead to financial and scheduling issues down the line.
The severity of this check is classified as an 'MINOR', which means it is less severe but still indicates that there might be minor problems or that the data doesn't follow all best practices. It is important to address these alerts to ensure the accuracy and integrity of the project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_DoesETCiExistAYearOrMoreAfterLastBCWSi] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with LastS as (
SELECT WBS_ID_WP, MAX(period_date) LastS
FROM DS03_cost
WHERE upload_ID = @upload_ID AND (BCWSi_dollars > 0 OR BCWSi_hours > 0 OR BCWSi_FTEs > 0)
GROUP BY WBS_ID_WP
), EPeriod as (
SELECT WBS_ID_WP, period_date EPeriod
FROM DS03_cost
WHERE upload_ID = @upload_ID AND (ETCi_dollars > 0 OR ETCi_hours > 0 OR ETCi_FTEs > 0)
GROUP BY WBS_ID_WP, period_date
), Flags As (
SELECT S.WBS_ID_WP WPID, EPeriod
FROM LastS S INNER JOIN EPeriod E ON S.WBS_ID_WP = E.WBS_ID_WP
WHERE DATEDIFF(m,LastS, EPeriod) >= 12
GROUP BY S.WBS_ID_WP, EPeriod
)
SELECT
C.*
FROM
DS03_Cost C INNER JOIN Flags F ON C.WBS_ID_WP = F.WPID
AND C.period_date = F.EPeriod
WHERE
upload_ID = @upload_ID
AND (ETCi_dollars > 0 OR ETCi_hours > 0 OR ETCi_FTEs > 0)
)