¶ Basic Information
| Key | Value |
|---|---|
| Table | DS20 Sched CAL Exception |
| Severity | MINOR |
| Unique ID | 9200596 |
| Summary | Is this calendar missing in the standard list of calendars? |
| Error message | calendar_name not in DS19.calendar_name list. |
¶ 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 "Calendar Name Missing In Standard Calendar List" is designed to verify the consistency of calendar names in the DS20 Sched CAL Exception table with the standard list of calendars in the DS19 Schedule Calendar Std table.
The DIQ check is triggered when a calendar name in the DS20 Sched CAL Exception table is not found in the DS19 Schedule Calendar Std table. This discrepancy could be due to a typo in the calendar name, a missing entry in the standard calendar list, or an unauthorized addition of a new calendar in the DS20 Sched CAL Exception table.
The field causing the issue is the 'calendar_name' field in the DS20 Sched CAL Exception table. The expected value for this field should be a name that exists in the 'calendar_name' field of the DS19 Schedule Calendar Std table.
If the DIQ check fails, it is recommended to review the calendar names in both tables for any discrepancies and make necessary corrections to ensure data integrity and quality.
¶ Why do we check this?
This test is being performed to check if a particular calendar name exists in the standard list of calendars. The test is important because it ensures that all calendar names used in the 'DS20 Sched CAL Exception' table are also present in the standard list of calendars (DS19.calendar_name list). This is crucial for maintaining consistency and accuracy in the data, as it prevents the use of non-standard or incorrect calendar names.
The severity of this test is marked as 'MINOR', which is less severe. This means that while the issue might 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. For instance, if a calendar name is missing from the standard list, it could lead to confusion or errors during data analysis, especially if the missing calendar name is used frequently in the data. Therefore, it's recommended to address this issue to ensure the integrity and quality of the data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS20_Sched_CAL_Excpt_IsCalNameMissingInDS19] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
SELECT Exc.*
FROM DS20_schedule_calendar_exception Exc LEFT OUTER JOIN DS19_schedule_calendar_std Std ON Exc.calendar_name = Std.calendar_name
AND ISNULL(Exc.subproject_ID,'') = ISNULL(Std.subproject_ID,'')
WHERE
Exc.upload_ID = @upload_ID
AND Std.upload_ID = @upload_ID
AND Std.calendar_name IS NULL
)
¶ Changelog
| Date | Description of Changes |
|---|---|
| 2024-04-30 | Logic adjusted to account for the addition of 'subproject_id' field, as well as for a minor code optimization |