¶ Basic Information
| Key | Value |
|---|---|
| Table | DS04 Schedule |
| Severity | MAJOR |
| Unique ID | 1040192 |
| Summary | Is this milestone earlier than the Approve Start Project Milestone? |
| Error message | Milestone ES_Date < ES_Date for the Approve Start Project Milestone (milestone_level = 100) (by subproject_ID). |
¶ 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 "Milestone Prior To Approve Start Project" is designed to ensure that no milestones in the DS04 Schedule table have an early start date (ES_Date) that is prior to the approved start project milestone.
The approved start project milestone is identified by a milestone level of 100. If a milestone has a level other than 100 and its early start date is earlier than that of the approved start project milestone, this DIQ check will flag it as an error.
The likely cause of this error is incorrect data entry in the ES_Date field for some milestones. The ES_Date for any milestone should not be earlier than the ES_Date of the approved start project milestone.
To resolve this issue, review the ES_Date values for all milestones and ensure they are not earlier than the ES_Date of the approved start project milestone.
¶ Why do we check this?
This test is being performed on the 'DS04 Schedule' table to check if there are any milestones that have an early start date (ES_Date) prior to the approved start project milestone. The test is titled 'Milestone Prior To Approve Start Project' and it is designed to identify any instances where a milestone is scheduled to begin before the project has officially been approved to start.
The importance of this check is to ensure that the project schedule is logically consistent and that no tasks are planned to commence before the project has been officially approved. This is crucial for maintaining the integrity of the project schedule and for ensuring that all tasks are carried out in the correct sequence.
The severity of this test is classified as a 'MAJOR', which means that if this issue is not addressed, it is likely to cause problems during the analysis of the project data. It may not prevent the data from being reviewed, but it could potentially lead to inaccurate conclusions or decisions being made based on the data. Therefore, it is important to rectify this issue to ensure the accuracy and reliability of the project data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS04_Sched_IsMSEarlierThanAppStartProj] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with MS100 as (
SELECT ES_Date, schedule_type, ISNULL(subproject_ID,'') SubP
FROM DS04_schedule
WHERE upload_ID = @upload_ID AND milestone_level = 100
)
SELECT
S.*
FROM
DS04_schedule S INNER JOIN MS100 M ON S.schedule_type = M.schedule_type
AND ISNULL(S.subproject_ID,'') = M.SubP
AND S.ES_date < M.ES_date
WHERE
upload_id = @upload_ID
AND S.milestone_level <> 100
AND S.milestone_level IS NOT NULL
)
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2024-04-30 | Logic adjusted to account for the addition of 'subproject_ID' field. |