¶ Basic Information
| Key | Value |
|---|---|
| Table | DS06 Resources |
| Severity | MINOR |
| Unique ID | 9060293 |
| Summary | Is this calendar name missing in the standard calendar list? |
| Error message | calendar_name not found 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 Missing In Standard Calendar List" is designed to ensure that all calendar names listed in the DS06 Resources table are also present in the DS19 Schedule Calendar Standard table.
If an error message "calendar_name not found in DS19.calendar_name list" is returned, it indicates that there is a discrepancy between the two tables. Specifically, one or more calendar names in the DS06 Resources table could not be found in the DS19 Schedule Calendar Standard table.
This discrepancy could be due to a variety of reasons such as data entry errors, updates or changes in the standard calendar list that have not been reflected in the resources table, or vice versa.
To resolve this issue, it is recommended to cross-verify the calendar names in both tables and ensure they are consistent. Any missing calendar names in the DS19 Schedule Calendar Standard table should be added if they are valid, or the corresponding entries in the DS06 Resources table should be corrected if they are erroneous.
Please note that this DIQ check is only performed when there are entries in the DS19 Schedule Calendar Standard table. If the table is empty, the check will not run.
¶ Why do we check this?
This test is being performed to ensure that all calendar names used in the 'DS06 Resources' table are also present in the standard calendar list (DS19.calendar_name list). The purpose of this check is to maintain consistency and accuracy in the data, as any discrepancies in calendar names could lead to confusion or errors in data analysis.
The severity of this test is marked as 'MINOR', which means it is not a critical issue that would prevent the data from being reviewed. However, it does indicate that there might be minor problems or that the data doesn't follow all best practices. It's important to address this alert to ensure that all calendar names are standardized across all tables, which will facilitate accurate and efficient data analysis.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS06_Res_IsCalendarMissingInDS19] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
with CALs as (
SELECT calendar_name CName, ISNULL(subproject_ID,'') SubP
FROM DS19_schedule_calendar_std
WHERE upload_ID = @upload_ID
)
SELECT R.*
FROM DS06_schedule_resources R LEFT OUTER JOIN CALs C ON R.calendar_name = C.CName AND ISNULL(R.subproject_ID,'') = C.SubP
WHERE upload_id = @upload_ID
AND C.CName IS NULL
AND EXISTS (SELECT 1 FROM CALs) --run only if there are any calendars in DS19
)
¶ 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. |