¶ Basic Information
| Key | Value |
|---|---|
| Table | DS01 WBS |
| Severity | MINOR |
| Unique ID | 9010029 |
| Summary | Is the subproject ID missing in the schedule? |
| Error message | Subproject ID missing in DS04 Schedule |
¶ 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 "Subproject ID Missing in Schedule" is designed to identify any instances where a subproject ID is missing in the DS04 Schedule table, but is present in the DS01 WBS table.
This check is important because it ensures that all subprojects listed in the DS01 WBS table are also accounted for in the DS04 Schedule table. If a subproject ID is missing from the DS04 Schedule table, it could indicate a data entry error or a synchronization issue between the two tables.
The fields involved in this check are the 'type' and 'subproject_ID' fields in the DS01 WBS table, and the 'subproject_ID' field in the DS04 Schedule table. The 'type' field in the DS01 WBS table should contain one of the following values: 'WP', 'PP', or 'SLPP'. The 'subproject_ID' field in both tables should contain a valid subproject ID.
If the DIQ check identifies a subproject ID in the DS01 WBS table that is not present in the DS04 Schedule table, it will return a message indicating that the subproject ID is missing from the DS04 Schedule table. This is a signal to review the data entry process for the DS04 Schedule table to ensure that all subproject IDs from the DS01 WBS table are being correctly included.
¶ Why do we check this?
This test is being performed to check if the subproject ID is missing in the DS04 Schedule table. The subproject ID is a crucial piece of information that helps in identifying and tracking the progress of individual subprojects within the larger project.
The severity of this issue is marked as an MINOR. This means that while it's not a critical error that would prevent the data from being reviewed, it's still a potential issue that could cause minor problems. For instance, the absence of subproject IDs could make it more difficult to track the progress and status of individual subprojects, which could in turn affect project management and decision-making processes.
Therefore, this check is important because it helps ensure that all necessary information is included in the data, which in turn helps maintain the integrity and quality of the data. It also helps ensure that the data follows best practices, which can facilitate smoother and more efficient data analysis and project management.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS01_WBS_IsSubprojectIDMissingInDS04] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
WITH DS04SubPs AS (
SELECT DISTINCT TRIM(ISNULL(subproject_ID,'')) AS SubP
FROM DS04_Schedule
WHERE upload_ID = @upload_ID
)
SELECT W.*
FROM DS01_WBS W LEFT JOIN DS04SubPs S ON W.subproject_ID = S.SubP
WHERE W.upload_ID = @upload_ID
AND W.type in ('WP','PP','SLPP')
AND TRIM(ISNULL(W.subproject_ID,'')) <> ''
AND S.SubP IS NULL
)
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2024-04-30 | Minor code optimization. |