¶ Basic Information
| Key | Value |
|---|---|
| Table | DS03 Cost |
| Severity | MAJOR |
| Unique ID | 9030057 |
| Summary | Is this period date less than 20 or more than 36 days apart from either the previous or next period? |
| Error message | Period_date is not within 20-36 days from previous or next period. |
¶ 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 "Cost Periods Not One Month Apart" is designed to ensure that the period dates in the DS03 Cost table are approximately one month apart. This is important for maintaining consistency and accuracy in the project management data.
The DIQ check is looking for period dates that are less than 20 days or more than 36 days apart from either the previous or next period. If a period date falls outside of this range, it will trigger an error message stating "Period_date is not within 20-36 days from previous or next period."
The error is likely caused by incorrect data entry in the 'period_date' field of the DS03 Cost table. The expected values for this field should be dates that are approximately one month apart from each other. If the dates are too close together or too far apart, it could indicate a mistake in data entry or a problem with the data source.
To resolve this issue, review the 'period_date' entries in the DS03 Cost table and correct any dates that are not approximately one month apart from the previous or next period. This will ensure that the project management data maintains a consistent monthly timeline, which is crucial for accurate tracking and reporting.
¶ Why do we check this?
This test is being performed on the 'DS03 Cost' table to ensure that the cost periods are consistently one month apart. The test checks if the period date is less than 20 or more than 36 days apart from either the previous or next period. The importance of this check is to maintain consistency in the data and to ensure accurate tracking and reporting of costs over time. If the cost periods are not one month apart, it could lead to inaccuracies in cost analysis and forecasting, potentially causing problems during project management and decision-making processes. The severity of this issue is marked as a 'MAJOR', which means that while it may not prevent the data from being reviewed, it is likely to cause problems during analysis if not addressed.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS03_Cost_ArePeriodsLT20OrGT36DaysApart] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with PMBPeriods AS (
SELECT
period_date Period,
LAG(period_date,1) OVER (ORDER BY period_date) as PrevPeriod,
LEAD(period_date,1) OVER (ORDER BY period_date) as NextPeriod
FROM DS03_cost
WHERE upload_ID = @upload_ID AND period_date < (
SELECT MAX(ES_Date)
FROM DS04_schedule
WHERE upload_ID = @upload_ID AND schedule_type = 'BL' AND milestone_level = 175
)
GROUP BY period_date
)
SELECT
*
FROM
DS03_Cost
WHERE
upload_ID = @upload_ID
AND period_date IN (
SELECT Period
FROM PMBPeriods
WHERE
DATEDIFF(day,PrevPeriod,Period) NOT BETWEEN 20 AND 36 OR
DATEDIFF(day,Period,NextPeriod) NOT BETWEEN 20 AND 36
)
)