I have a request to identify records between two shifts. Shift 1 begin at 7am – 6:59pm same day and shift 2 begin on 7pm – 6:59am the next day. I can only write SQL cannot create function or procedure. Could you please advice how I can go about accomplish this through SQL code. sample data
shift
Thank you in advance for your help/suggestion
classify records create between 7am – 6:59pm as shift 1
classify records created between 7pm – 6:59am the next day as shift 2
shift continue
1
Subtract 7 hours from your time to get a normalised time and then calculate what fraction of the day has occurred in the time component of the normalised time. If it is less than half a day then it is the first shift, if it is half-or-greater then it is the second shift.
Something like this:
SELECT date_time,
TO_CHAR(date_time - INTERVAL '7' HOUR, 'YYYY-MM-DD') AS shift_date,
FLOOR(
(
(date_time - INTERVAL '7' HOUR)
- TRUNC(date_time - INTERVAL '7' HOUR)
) * 2
) + 1 AS shift_number
FROM table_name
Which, for the sample data:
CREATE TABLE table_name (date_time) AS
SELECT DATE '2024-01-06' + (LEVEL - 1) * INTERVAL '1' HOUR
FROM DUAL
CONNECT BY LEVEL <= 24;
Outputs:
| DATE_TIME | SHIFT_DATE | SHIFT_NUMBER |
|---|---|---|
| 2024-01-06 00:00:00 | 2024-01-05 | 2 |
| … | … | … |
| 2024-01-06 06:00:00 | 2024-01-05 | 2 |
| 2024-01-06 07:00:00 | 2024-01-06 | 1 |
| … | … | … |
| 2024-01-06 18:00:00 | 2024-01-06 | 1 |
| 2024-01-06 19:00:00 | 2024-01-06 | 2 |
| … | … | … |
| 2024-01-06 23:00:00 | 2024-01-06 | 2 |
fiddle
1