¶ Basic Information
| Key | Value |
|---|---|
| Table | DS20 Sched CAL Exception |
| Severity | MINOR |
| Unique ID | 1200597 |
| Summary | Is this non-workday exception missing a shift? |
| Error message | exception_work_day = N & no exception_shift_#start_time or exception_shift#_finish_time found. |
¶ 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 for the DS20 Sched CAL Exception table, titled "Non-Workday Exception Missing Shift", is designed to identify any non-workday exceptions that are missing a shift.
This issue arises when the 'exception_work_day' field is marked as 'N', indicating a non-workday, but no corresponding start or finish times are found for any of the shifts (A, B, or C).
In a properly functioning system, a non-workday exception should always be associated with a shift, and that shift should have both a start and finish time. If this DIQ check flags any records, it means that there are non-workday exceptions in the DS20 Sched CAL Exception table that are missing this crucial information.
To resolve this issue, you should ensure that every non-workday exception has an associated shift, and that each shift has a defined start and finish time.
¶ Why do we check this?
This test is being performed on the 'DS20 Sched CAL Exception' table to check for any non-workday exceptions that are missing a shift. The test is looking for instances where the 'exception_work_day' is marked as 'N' (indicating a non-workday), but there is no corresponding 'exception_shift_#start_time' or 'exception_shift#_finish_time' found.
The importance of this check is to ensure that all non-workday exceptions are properly accounted for with a corresponding shift. If a non-workday exception is missing a shift, it could lead to inaccuracies in the project management data, such as incorrect calculations of work hours or misinterpretation of project timelines.
The severity of this check is marked as 'MINOR', which means it is less severe but still indicates that there might be minor problems or that the data doesn't follow all best practices. It's not a critical error, but it's something that should be addressed to maintain the integrity and quality of the data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS20_Sched_CAL_Excpt_IsNonWorkDayExceptionMissingShift] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
SELECT
*
FROM DS20_schedule_calendar_exception
WHERE
upload_ID = @upload_ID
AND exception_work_day = 'N'
AND exception_shift_A_start_time IS NULL
AND exception_shift_A_stop_time IS NULL
AND exception_shift_B_start_time IS NULL
AND exception_shift_B_stop_time IS NULL
AND exception_shift_C_start_time IS NULL
AND exception_shift_C_stop_time IS NULL
)