¶ Basic Information
| Key | Value |
|---|---|
| Table | DS04 Schedule |
| Severity | MAJOR |
| Unique ID | 9040175 |
| Summary | Is the early start date for this WBS misaligned with what is in cost (DS03)? |
| Error message | ES_date for this WBS_ID does not align with the first period where BCWSi > 0 (dollars/hours/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 "Start Date Misaligned With Cost" is designed to identify any discrepancies between the early start date (ES_date) for a Work Breakdown Structure (WBS) in the DS04 Schedule table and the first period where the Budgeted Cost of Work Scheduled (BCWSi) is greater than zero in the DS03 Cost table.
The error message "ES_date for this WBS_ID does not align with the first period where BCWSi > 0 (dollars/hours/FTEs)" indicates that the early start date for a particular WBS does not match the first period where there is a budgeted cost for work scheduled. This discrepancy could be due to data entry errors or inconsistencies in scheduling and budgeting practices.
The fields causing this issue are the ES_date in the DS04 Schedule table and the period_date and BCWSi fields in the DS03 Cost table. The expected values for these fields would be that the ES_date for a given WBS should align with the first period_date where BCWSi is greater than zero.
This DIQ check groups the results by WBS_ID, allowing you to identify which specific Work Breakdown Structures have misaligned start dates and costs. Please review these entries for accuracy and consistency in your project management data.
¶ Why do we check this?
This test is being performed to ensure that the start date for a given Work Breakdown Structure (WBS) aligns with the first period where the Budgeted Cost of Work Scheduled (BCWS) is greater than zero. This is important because it checks for consistency between the project's schedule and cost data. If the start date does not align with the first period where BCWS is greater than zero, it could indicate that the project's cost and schedule data are not in sync, which could lead to inaccurate project forecasting and reporting.
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 could potentially lead to incorrect conclusions being drawn about the project's status and performance. Therefore, it is important to address this issue to ensure the accuracy and reliability of the project's data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS04_Sched_IsESMisalignedWithDS03] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CostStart as (
SELECT WBS_ID_WP, MIN(period_date) CostES
FROM DS03_cost
WHERE upload_ID = @upload_ID AND (BCWSi_dollars > 0 OR BCWSi_FTEs > 0 OR BCWSi_hours > 0)
GROUP BY WBS_ID_WP
), SchedStart as (
SELECT WBS_ID, MIN(ES_Date) SchedES
FROM DS04_schedule
WHERE upload_ID = @upload_ID AND schedule_type = 'FC' AND ISNULL(subtype,'') NOT IN ('SVT', 'ZBA') and type NOT IN ('WS','SM','FM')
GROUP BY WBS_ID
), WBSFails as (
SELECT S.WBS_ID
FROM SchedStart S INNER JOIN CostStart C ON C.WBS_ID_WP = S.WBS_ID
WHERE ABS(DATEDIFF(d,C.CostES,S.SchedES))>31
)
SELECT
*
FROM
DS04_schedule
WHERE
upload_id = @upload_ID
AND schedule_type = 'FC'
AND ISNULL(subtype,'') NOT IN ('SVT', 'ZBA')
AND type NOT IN ('WS','SM','FM')
AND WBS_ID IN (SELECT WBS_ID FROM WBSFails)
)