¶ Basic Information
| Key | Value |
|---|---|
| Table | DS11 Variance |
| Severity | MINOR |
| Unique ID | 9110483 |
| Summary | Is the approved date before the last or after the current status date? |
| Error message | approved_date < DS00.CPP_Status_Date - 1 & approved_date > DS00.CPP_Status_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 "Approved Date Outside Current Period" is designed to ensure that the approved date for variances in the DS11 Variance table is within the current reporting period. This check is important for maintaining accurate and timely project management data.
The DIQ check compares the approved date for each variance with the current project status date from the DS00 table. If the approved date is either before the last period date or after the current status date, the DIQ check will flag this as an error.
The likely cause of this error is an incorrect entry in the 'approved_date' field in the DS11 Variance table. The approved date should fall within the current reporting period, which is defined by the 'CPP_Status_Date' in the DS00 table. If the approved date is before the last period date or after the current status date, it suggests that the variance approval was either recorded too early or too late.
To resolve this issue, review the 'approved_date' entries in the DS11 Variance table that have been flagged by this DIQ check. Ensure that these dates fall within the current reporting period as defined by the 'CPP_Status_Date' in the DS00 table.
¶ Why do we check this?
This test is being performed to ensure that the approved date for a variance in the DS11 Variance table is within the current period. The test checks if the approved date is before the last status date or after the current status date. If the approved date falls outside this range, it triggers an alert.
The importance of this check is to maintain the temporal accuracy and consistency of the data. It ensures that all approved variances are within the correct time frame relative to the project's status dates. This is crucial for accurate tracking and reporting of project variances, which can impact project management decisions and outcomes.
The severity of this check is classified as an MINOR. This means that while it may not prevent the data from being reviewed, it could potentially cause minor problems or indicate that the data does not adhere to all best practices. It's a signal to review the data and correct any discrepancies to ensure accurate and reliable project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS11_VAR_IsApprovedDateOutsideOfCurrentPeriod] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with LastPeriod as (
SELECT period_date
FROM (
SELECT period_date, ROW_NUMBER() OVER (ORDER BY period_date DESC) AS row_num
FROM DS03_cost
WHERE upload_ID = @upload_ID AND period_date < CPP_status_date
) subquery
WHERE row_num = 1
)
SELECT
*
FROM
DS11_variance
WHERE
upload_ID = @upload_ID
AND (
approved_date > CPP_status_date OR -- later than curent period
approved_date < (SELECT TOP 1 period_date from LastPeriod) -- earlier than last
)
)