¶ Basic Information
| Key | Value |
|---|---|
| Table | DS20 Sched CAL Exception |
| Severity | CRITICAL |
| Unique ID | 1200595 |
| Summary | Is this calendar exception duplicated by calendar name, subproject, & exception date? |
| Error message | Count of calendar_name, subproject_ID, & exception_date combo > 1. |
¶ 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 "Duplicate Calendar Exception" is designed to identify any instances where the same calendar exception appears more than once in the DS20 Sched CAL Exception table. This is determined by checking for duplicates based on the combination of the 'calendar_name' and 'exception_date' fields.
If the DIQ check identifies any duplicates, it means that there are multiple entries for the same calendar exception, which could lead to confusion or errors in the project management data. The expected values for the 'calendar_name' and 'exception_date' fields should be unique combinations for each calendar exception.
To resolve this issue, you should review the data in the DS20 Sched CAL Exception table and remove any duplicate calendar exceptions. This will ensure the integrity and quality of the project management data.
¶ Why do we check this?
This test, titled "Duplicate Calendar Exception," is being performed on the 'DS20 Sched CAL Exception' table to identify any instances where the combination of 'calendar_name' and 'exception_date' is duplicated. The purpose of this test is to ensure that there are no redundant entries in the calendar exception data, which could lead to confusion or inaccuracies in the project management timeline.
The severity of this test is marked as 'CRITICAL', which is the highest level of severity. This means that if any duplicates are found, they must be resolved before the data can be further reviewed or used for analysis. This is crucial because duplicate entries can distort the data analysis, leading to incorrect conclusions and potentially flawed decision-making. Therefore, maintaining data integrity and quality through this check is of utmost importance for the accuracy and reliability of the EVMS construction project management data at the US Department of Energy.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS20_Sched_CAL_Excpt_PK] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with Dupes as (
SELECT calendar_name, ISNULL(subproject_ID,'') SubP, exception_date
FROM DS20_schedule_calendar_exception
WHERE upload_ID = @upload_ID
GROUP BY calendar_name, ISNULL(subproject_ID,''), exception_date
HAVING COUNT(*) > 1
)
SELECT
S.*
FROM
DS20_schedule_calendar_exception S INNER JOIN Dupes D ON S.calendar_name = D.calendar_name
AND ISNULL(S.subproject_ID,'') = D.SubP
AND S.exception_date = D.exception_date
WHERE
upload_ID = @upload_ID
)
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2024-04-30 | Logic adjusted to account for the addition of subproject_ID field. |