¶ Basic Information
| Key | Value |
|---|---|
| Table | DS19 Sched CAL Std |
| Severity | MINOR |
| Unique ID | 1190594 |
| Summary | Are the hours of any of your shifts in excess of 12 hours? |
| Error message | Hours delta between std_##DDD_shift#start_time & std##DDD_shift#_stop_time > 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 Exceeds 12 Hours" is performed on the DS19 Sched CAL Std table. This check is designed to identify any shifts that are longer than 12 hours.
The check is performed by comparing the start and stop times of each shift. If the difference between these times is greater than 12 hours, the check will flag this as an issue.
The fields that are checked include the start and stop times for each shift on each day of the week. For example, the fields for Monday's shifts are std_01_Mon_shift_A_start_time, std_01_Mon_shift_A_stop_time, std_01_Mon_shift_B_start_time, std_01_Mon_shift_B_stop_time, std_01_Mon_shift_C_start_time, and std_01_Mon_shift_C_stop_time. Similar fields exist for each day of the week, from Monday (std_01) to Sunday (std_07), and for each shift (A, B, C).
If you receive a message indicating that the "Hours delta between std_##DDD_shift#start_time & std##DDD_shift#_stop_time > 12", this means that one or more shifts have been scheduled that are longer than 12 hours. This could be due to an error in data entry or scheduling.
To resolve this issue, review the shift times in the DS19 Sched CAL Std table and adjust any shifts that are longer than 12 hours. The expected values for the difference between the start and stop times of each shift should be 12 hours or less.
¶ Why do we check this?
This test is being performed to check if any shifts in the 'DS19 Sched CAL Std' table exceed 12 hours. The test checks the difference between the start and stop times of each shift and raises an alert if this difference is greater than 12 hours.
The importance of this check is to ensure that the data on shift lengths is accurate and reasonable. Shifts exceeding 12 hours could indicate a data entry error or an unrealistic work schedule. This could potentially lead to problems in project management, such as overestimating the amount of work that can be done in a day or failing to account for necessary rest periods.
Although the severity of this check is classified as an 'MINOR', which is less severe than a 'MAJOR' or 'CRITICAL', it is still important to address any issues identified. Ignoring these alerts could lead to minor problems in the data or indicate that the data does not follow best practices for shift scheduling.
¶ Code
CREATE FUNCTION [dbo].[fnDIQ_DS19_Sched_CAL_Std_IsShiftLongerThan12Hours] (
@upload_id int = 0
)
RETURNS TABLE
AS RETURN
(
SELECT
*
FROM DS19_schedule_calendar_std
WHERE
upload_ID = @upload_ID
AND (
ABS(DATEDIFF(hour, std_01_Mon_shift_A_start_time, std_01_Mon_shift_A_stop_time)) > 12
OR ABS(DATEDIFF(hour, std_01_Mon_shift_B_start_time, std_01_Mon_shift_B_stop_time)) > 12
OR ABS(DATEDIFF(hour, std_01_Mon_shift_C_start_time, std_01_Mon_shift_C_stop_time)) > 12
OR ABS(DATEDIFF(hour, std_02_Tue_shift_A_start_time, std_02_Tue_shift_A_stop_time)) > 12
OR ABS(DATEDIFF(hour, std_02_Tue_shift_B_start_time, std_02_Tue_shift_B_stop_time)) > 12
OR ABS(DATEDIFF(hour, std_02_Tue_shift_C_start_time, std_02_Tue_shift_C_stop_time)) > 12
OR ABS(DATEDIFF(hour, std_03_Wed_shift_A_start_time, std_03_Wed_shift_A_stop_time)) > 12
OR ABS(DATEDIFF(hour, std_03_Wed_shift_B_start_time, std_03_Wed_shift_B_stop_time)) > 12
OR ABS(DATEDIFF(hour, std_03_Wed_shift_C_start_time, std_03_Wed_shift_C_stop_time)) > 12
OR ABS(DATEDIFF(hour, std_04_Thu_shift_A_start_time, std_04_Thu_shift_A_stop_time)) > 12
OR ABS(DATEDIFF(hour, std_04_Thu_shift_B_start_time, std_04_Thu_shift_B_stop_time)) > 12
OR ABS(DATEDIFF(hour, std_04_Thu_shift_C_start_time, std_04_Thu_shift_C_stop_time)) > 12
OR ABS(DATEDIFF(hour, std_05_Fri_shift_A_start_time, std_05_Fri_shift_A_stop_time)) > 12
OR ABS(DATEDIFF(hour, std_05_Fri_shift_B_start_time, std_05_Fri_shift_B_stop_time)) > 12
OR ABS(DATEDIFF(hour, std_05_Fri_shift_C_start_time, std_05_Fri_shift_C_stop_time)) > 12
OR ABS(DATEDIFF(hour, std_06_Sat_shift_A_start_time, std_06_Sat_shift_A_stop_time)) > 12
OR ABS(DATEDIFF(hour, std_06_Sat_shift_B_start_time, std_06_Sat_shift_B_stop_time)) > 12
OR ABS(DATEDIFF(hour, std_06_Sat_shift_C_start_time, std_06_Sat_shift_C_stop_time)) > 12
OR ABS(DATEDIFF(hour, std_07_Sun_shift_A_start_time, std_07_Sun_shift_A_stop_time)) > 12
OR ABS(DATEDIFF(hour, std_07_Sun_shift_B_start_time, std_07_Sun_shift_B_stop_time)) > 12
OR ABS(DATEDIFF(hour, std_07_Sun_shift_C_start_time, std_07_Sun_shift_C_stop_time)) > 12
)
)