¶ Basic Information
| Key | Value |
|---|---|
| Table | DS09 CC Log |
| Severity | MINOR |
| Unique ID | 9090445 |
| Summary | Is the implementation date missing or is it considerably after the approved date? |
| Error message | implementation_date is missing or > the next DS03.period_date after the current DS09.approved_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 "Implementation Date Missing or Unreasonable" is designed to ensure that the implementation date in the DS09 CC Log is neither missing nor significantly later than the approved date.
The error message "implementation_date is missing or > the next DS03.period_date after the current DS09.approved_date" indicates that the implementation date is either not provided or it is later than the next period date in the DS03 Cost table following the approved date in the DS09 CC Log.
The likely cause of this error is incorrect or missing data entry in the 'implementation_date' field of the DS09 CC Log. The expected value for the 'implementation_date' should be a valid date that is not later than the next period date in the DS03 Cost table following the approved date in the DS09 CC Log.
Please ensure that the 'implementation_date' is correctly entered in the DS09 CC Log to maintain data integrity and quality.
¶ Why do we check this?
This test is being performed on the 'DS09 CC Log' table to check the integrity and quality of the 'implementation_date' data. The test is checking whether the implementation date is missing or if it is significantly later than the approved date. This is important because the implementation date should ideally be after the approved date, but not too far into the future. If the implementation date is missing or unreasonable, it could lead to incorrect analysis or misinterpretation of the project timeline.
The severity of this test is marked as 'MINOR', which is less severe than 'CRITICAL' or 'MAJOR'. This means that while the issue might not prevent the data from being reviewed, it could potentially cause minor problems during analysis or indicate that the data does not adhere to all best practices. Therefore, it is recommended to address this issue to ensure the accuracy and reliability of the project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS09_CCLog_IsImpDateMissingOrUnreasonable] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
SELECT
*
FROM
DS09_CC_log
WHERE
upload_ID = @upload_ID
AND (
implementation_date IS NULL OR
implementation_date > (
SELECT period_date
FROM (
SELECT period_date, ROW_NUMBER() OVER (ORDER BY period_date ASC) AS row_num
FROM DS03_cost
WHERE upload_ID = @upload_ID AND period_date >= approved_date
) subQ
WHERE row_num = 2
)
)
/*
Example:
-- Create a temporary table to hold the sample data
CREATE TABLE #my_data (
period_date DATE,
cost DECIMAL(18, 2)
);
-- Insert some sample data into the table
INSERT INTO #my_data (period_date, cost)
VALUES
('2021-01-01', 100.00),
('2021-02-01', 110.00),
('2021-03-01', 120.00),
('2021-04-01', 130.00),
('2021-05-01', 140.00),
('2021-06-01', 150.00),
('2021-07-01', 160.00),
('2021-08-01', 170.00),
('2021-09-01', 180.00),
('2021-10-01', 190.00),
('2021-11-01', 200.00),
('2021-12-01', 210.00),
('2022-01-01', 220.00),
('2022-02-01', 230.00),
('2022-03-01', 240.00),
('2022-04-01', 250.00),
('2022-05-01', 260.00),
('2022-06-01', 270.00),
('2022-07-01', 280.00),
('2022-08-01', 290.00),
('2022-09-01', 300.00);
DECLARE @approved_date DATE = '2022-01-01';
SELECT period_date
FROM (
SELECT period_date, ROW_NUMBER() OVER (ORDER BY period_date ASC) AS row_num
FROM #my_data
WHERE period_date >= @approved_date
) subquery
WHERE row_num = 2
ORD