¶ Basic Information
| Key | Value |
|---|---|
| Table | DS20 Sched CAL Exception |
| Severity | MINOR |
| Unique ID | 1200598 |
| Summary | Is this shift exception longer than 12 hours? |
| Error message | Delta between exception_shift_#start_time & exception_shift#_finish_time found > 12. |
¶ 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 "Shift Exception In Excess Of 12 Hours" is designed to identify any instances in the DS20 Sched CAL Exception table where the duration of a shift exception exceeds 12 hours.
This check is performed by comparing the start and finish times of each shift exception. If the difference between the start and finish times of any shift exception (A, B, or C) is greater than 12 hours, the DIQ check will flag this as an error.
The fields causing the issue are 'exception_shift_A_start_time' and 'exception_shift_A_stop_time', 'exception_shift_B_start_time' and 'exception_shift_B_stop_time', and 'exception_shift_C_start_time' and 'exception_shift_C_stop_time'.
The expected values for these fields should reflect shift exceptions that are 12 hours or less in duration. If the duration of a shift exception exceeds 12 hours, it may indicate an error in data entry or scheduling.
Please review the flagged entries in the DS20 Sched CAL Exception table and correct any discrepancies to ensure the accuracy and integrity of the project management data.
¶ Why do we check this?
This test is being performed on the 'DS20 Sched CAL Exception' table to check for any shift exceptions that are longer than 12 hours. The test is checking the difference between the start and finish times of exception shifts, and raises an alert if this difference is found to be greater than 12 hours.
The importance of this check is to ensure that the data regarding shift exceptions is accurate and reasonable. Shifts that are longer than 12 hours could indicate a data entry error or an unrealistic scheduling practice. While this issue is not severe enough to prevent the data from being reviewed, it is flagged as an MINOR because it could potentially cause minor problems in the analysis or indicate that the data does not fully adhere to best practices. It is important to address these alerts to maintain the overall integrity and quality of the project management data.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS20_Sched_CAL_Excpt_IsShiftInExcessOf12Hours] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
SELECT
*
FROM DS20_schedule_calendar_exception
WHERE
upload_ID = @upload_ID
AND (
ABS(DATEDIFF(hour, exception_shift_A_start_time, exception_shift_A_stop_time)) > 12 OR
ABS(DATEDIFF(hour, exception_shift_B_start_time, exception_shift_B_stop_time)) > 12 OR
ABS(DATEDIFF(hour, exception_shift_C_start_time, exception_shift_C_stop_time)) > 12
)
)