¶ Basic Information
| Key | Value |
|---|---|
| Table | DS04 Schedule |
| Severity | MAJOR |
| Unique ID | 9040170 |
| Summary | Is the early finish date for this WBS misaligned with what is in cost (DS03)? |
| Error message | EF_date for this WBS_ID does not align with the last period where BCWSi > 0 in DS03 cost (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 "Finish Date Misaligned With Cost" is designed to identify any discrepancies between the early finish date (EF_date) for a given Work Breakdown Structure (WBS) in the DS04 Schedule table and the last period where the Budgeted Cost of Work Scheduled (BCWSi) is greater than zero in the DS03 Cost table.
The test groups the data by WBS_ID and checks if the difference between the maximum EF_date in the DS04 Schedule table and the maximum period_date in the DS03 Cost table is greater than 31 days. If such a discrepancy is found, it indicates that the early finish date for the WBS does not align with the last period where BCWSi is greater than zero in the DS03 Cost table.
The fields causing the issue are the EF_date in the DS04 Schedule table and the period_date in the DS03 Cost table. The expected values for these fields should be such that the difference between the maximum EF_date and the maximum period_date where BCWSi is greater than zero should not exceed 31 days.
Please note that this test only considers records in the DS04 Schedule table where the schedule_type is 'FC' and the subtype is not 'SVT' or 'ZBA', and the type is not 'WS', 'SM', or 'FM'. Any discrepancies found by this test could be due to incorrect data entry or issues with the scheduling or costing of the project.
¶ Why do we check this?
This test is being performed to ensure that the early finish date for a given Work Breakdown Structure (WBS) aligns with the last period where the Budgeted Cost of Work Scheduled (BCWS) is greater than zero in the DS03 cost data. This is important because misalignment between the schedule and cost data can lead to inaccurate project forecasting and budgeting.
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 or decisions being made based on the data. 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_IsEFMisalignedWithDS03] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CostFinish as (
SELECT WBS_ID_WP, MAX(period_date) CostEF
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
), SchedFinish as (
SELECT WBS_ID, MAX(EF_Date) SchedEF
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 SchedFinish S INNER JOIN CostFinish C ON C.WBS_ID_WP = S.WBS_ID
WHERE ABS(DATEDIFF(d,C.CostEF,S.SchedEF))>31
)
SELECT
*
FROM
DS04_schedule S
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)
)