¶ Basic Information
| Key | Value |
|---|---|
| Table | DS08 WAD |
| Severity | MAJOR |
| Unique ID | 1080434 |
| Summary | Is the POP start of this WAD revision earlier than the POP start of the prior revision? |
| Error message | pop_start_date < pop_start_date of prior auth_PM_date. |
¶ 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 "POP Start Earlier Than Previous Revision" is designed to ensure that the Period of Performance (POP) start date for a given Work Authorization Directive (WAD) revision in the DS08 WAD table is not earlier than the POP start date of the prior revision.
This check is important because it helps maintain the chronological order of WAD revisions, which is crucial for accurate project management and tracking.
If an error is flagged by this DIQ check, it means that the POP start date of the current WAD revision is earlier than the POP start date of the previous revision. This could be due to a data entry error or a system error when the data was imported or updated.
The fields involved in this check are the WBS_ID, WBS_ID_WP, auth_PM_date, and POP_start_date. The WBS_ID and WBS_ID_WP fields identify the specific WAD, the auth_PM_date field indicates the date of the current revision, and the POP_start_date field is the start date of the period of performance.
The expected values for these fields would be such that for any given WAD (identified by WBS_ID and WBS_ID_WP), the POP_start_date should be later or equal to the POP_start_date of the previous revision (identified by the earlier auth_PM_date). If this is not the case, the DIQ check will flag an error.
¶ Why do we check this?
This test is being performed to ensure that the Period of Performance (POP) start date of the current Work Authorization Directive (WAD) revision is not earlier than the POP start date of the previous revision. This is important because it helps maintain the chronological order of project events and revisions, which is crucial for accurate project tracking and management.
The severity of this test is marked as a MAJOR. This means that while it may not immediately prevent the data from being reviewed, it is likely to cause problems during analysis. For instance, it could lead to confusion or inaccuracies when trying to understand the project timeline or when comparing different revisions of the WAD. Therefore, it is important to address this issue to ensure the integrity and quality of the project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS08_WAD_IsPOPStartEarlierThanLastRev] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
WITH LagValues AS (
SELECT
WBS_ID,
ISNULL(WBS_ID_WP,'') WPWBS,
auth_PM_date,
LAG(pop_start_date) OVER (PARTITION BY WBS_ID, ISNULL(WBS_ID_WP,'') ORDER BY auth_PM_date) AS prevPopStart
FROM DS08_WAD
WHERE upload_ID = @upload_ID
)
SELECT
W.*
FROM
DS08_WAD W INNER JOIN LagValues L ON W.WBS_ID = L.WBS_ID
AND ISNULL(W.WBS_ID_WP,'') = L.WPWBS
AND W.auth_PM_date = L.auth_PM_date
AND W.POP_start_date < L.prevPopStart
WHERE
W.upload_ID = @upload_ID
)