¶ Basic Information
| Key | Value |
|---|---|
| Table | DS04 Schedule |
| Severity | MINOR |
| Unique ID | 9040203 |
| Summary | Is the delta between the Planned/Estimated Completion & End of PMB milestones not equal to UB days? |
| Error message | Delta between Planned/Estimated Completion ES_date (milestone_level = 170) & End of PMB EF_date (milestone_level = 175) <> UB bgt days (DS07.UB_bgt_days). |
¶ 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 "Planned/Estimated Completion & End of PMB Delta Is Less Than UB" is designed to ensure that the difference in days between the Planned/Estimated Completion (milestone level 170) and the End of PMB (milestone level 175) is less than or equal to the UB budget days. This check is performed on the DS04 Schedule table.
If an error is flagged by this DIQ check, it indicates that the time difference between the Planned/Estimated Completion and the End of PMB is exceeding the UB budget days. This could be due to incorrect or unrealistic scheduling, or an error in the UB budget days value.
The fields involved in this check are the ES_date and EF_date fields from the DS04 Schedule table, and the UB_bgt_days field from the DS07 IPMR header table. The ES_date and EF_date fields represent the start and end dates of the milestones, respectively, while the UB_bgt_days field represents the UB budget days.
The expected values for the ES_date and EF_date fields are dates that reflect a realistic and achievable schedule for the project. The UB_bgt_days field should contain a value that accurately represents the budgeted time for the project. The difference in days between the ES_date and EF_date should not exceed the value in the UB_bgt_days field.
¶ Why do we check this?
This test is being performed to ensure that the difference (delta) between the planned or estimated completion date and the end of the Performance Measurement Baseline (PMB) is not greater than the Unbudgeted (UB) days. This is important because it helps to maintain the schedule integrity of the project. If the delta is greater than the UB days, it could indicate that the project is not on track and may exceed the planned schedule, which could lead to cost overruns and other project management issues.
The severity of this check is marked as an MINOR, which means it's not a critical issue that would prevent the data from being reviewed, but it's still important to address. If not addressed, it could lead to minor problems in the project management process or indicate that the data doesn't follow all best practices. It's a good practice to keep the delta between the planned/estimated completion and the end of PMB within the UB days to ensure the project is on track and within the planned schedule.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS04_Sched_IsPlannedCompletionAndEndOfPMBDeltaLtEqToUB] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with BLSched as (
SELECT COALESCE(ES_date, EF_date) MSDate, milestone_level
FROM DS04_schedule
WHERE upload_id = @upload_ID AND schedule_type = 'BL' AND milestone_level IN (170, 175)
), Delta as (
SELECT DATEDIFF(d, MS170.MSDate, MS175.MSDate) Delta
FROM BLSched MS170 INNER JOIN BLSched MS175 ON MS170.milestone_level <> MS175.milestone_level
WHERE MS170.milestone_level = 170 AND MS175.milestone_level = 175
), UBBgtDays as (
SELECT UB_bgt_days UB
FROM DS07_IPMR_header
WHERE upload_ID = @upload_ID
)
SELECT
*
FROM
DS04_schedule
WHERE
upload_id = @upload_ID
AND schedule_type = 'BL'
AND milestone_level IN (170, 175)
AND (SELECT TOP 1 Delta FROM Delta) <> (SELECT TOP 1 UB FROM UBBgtDays)
)
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2024-04-30 | Logic adjusted to compare Delta and UB as not equal, rather than Delta being less than or equal to UB. |