I am using this query to determine which job is done in which shift.
SELECT srvb.kod AS code,
CASE
WHEN srvb.rectarih1 || ' ' || srvb.rectime1 >= srvb.rectarih1 || ' ' || '07:30'
AND srvb.rectarih1 || ' ' || srvb.rectime1 < srvb.rectarih1 || ' ' || '15:30' THEN
1
WHEN srvb.rectarih1 || ' ' || srvb.rectime1 >= srvb.rectarih1 || ' ' || '15:30'
AND srvb.rectarih1 || ' ' || srvb.rectime1 < srvb.rectarih1 || ' ' || '23:30' THEN
2
ELSE
3
END AS shift,
srvb.rectarih1 || ' ' || srvb.rectime1 AS full_time,
srvb.rectarih1 AS job_start_date,
srvb.rectime1
FROM srvb00O srvb
LEFT JOIN srvbp0 srvp
ON srvp.kod = srvb.operatorkodu
LEFT JOIN srvb00 srv0
ON srv0.kod = srvb.kod
LEFT JOIN srvbc0 srvc
ON srvc.kod = srv0.cihazkartno
WHERE srvb.rectarih1 <> ' '
AND srvb.rectime1 <> ' '
AND srvb.rectarih2 <> ' '
AND srvb.rectime2 <> ' '
-- AND sysdate-TO_DATE(srvb.rectarih1,'YYYY/MM/DD')=1
ORDER BY srvb.kod DESC
Here is the some part of the list.
| CODE | SHIFT | FULL_TIME | JOB_START_DATE | RECTIME1 |
|---|---|---|---|---|
| 0000006182 | 3 | 2022/09/18 01:00 | 2022/09/18 | 01:00 |
| 0000006182 | 3 | 2022/09/18 01:00 | 2022/09/18 | 01:00 |
| 0000006181 | 3 | 2022/09/18 02:00 | 2022/09/18 | 02:00 |
| 0000006181 | 3 | 2022/09/18 02:00 | 2022/09/18 | 02:00 |
| 0000006180 | 3 | 2022/09/18 06:40 | 2022/09/18 | 06:40 |
| 0000006180 | 3 | 2022/09/18 06:40 | 2022/09/18 | 06:40 |
| 0000006179 | 3 | 2022/09/18 02:00 | 2022/09/18 | 02:00 |
| 0000006179 | 3 | 2022/09/18 02:00 | 2022/09/18 | 02:00 |
| 0000006178 | 3 | 2022/09/18 06:00 | 2022/09/18 | 06:00 |
| 0000006178 | 3 | 2022/09/18 06:00 | 2022/09/18 | 06:00 |
| 0000006175 | 1 | 2022/09/17 08:00 | 2022/09/17 | 08:00 |
| 0000006175 | 1 | 2022/09/17 08:00 | 2022/09/17 | 08:00 |
| 0000006174 | 1 | 2022/09/17 08:00 | 2022/09/17 | 08:00 |
| 0000006173 | 3 | 2022/09/17 00:20 | 2022/09/17 | 00:20 |
| 0000006173 | 3 | 2022/09/17 00:20 | 2022/09/17 | 00:20 |
| 0000006170 | 3 | 2022/09/16 03:00 | 2022/09/16 | 03:00 |
| 0000006170 | 3 | 2022/09/16 03:00 | 2022/09/16 | 03:00 |
| 0000006169 | 3 | 2022/09/16 02:00 | 2022/09/16 | 02:00 |
| 0000006169 | 3 | 2022/09/16 02:00 | 2022/09/16 | 02:00 |
| 0000006168 | 3 | 2022/09/16 01:00 | 2022/09/16 | 01:00 |
| 0000006168 | 3 | 2022/09/16 01:00 | 2022/09/16 | 01:00 |
| 0000006167 | 2 | 2022/09/15 21:00 | 2022/09/15 | 21:00 |
| 0000006167 | 2 | 2022/09/15 21:00 | 2022/09/15 | 21:00 |
| 0000006166 | 2 | 2022/09/15 20:30 | 2022/09/15 | 20:30 |
| 0000006166 | 2 | 2022/09/15 20:30 | 2022/09/15 | 20:30 |
| 0000006165 | 2 | 2022/09/15 18:15 | 2022/09/15 | 18:15 |
| 0000006165 | 2 | 2022/09/15 18:15 | 2022/09/15 | 18:15 |
| 0000006164 | 2 | 2022/09/15 17:00 | 2022/09/15 | 17:00 |
| 0000006164 | 2 | 2022/09/15 17:00 | 2022/09/15 | 17:00 |
| 0000006163 | 2 | 2022/09/15 16:00 | 2022/09/15 | 16:00 |
| 0000006163 | 2 | 2022/09/15 16:00 | 2022/09/15 | 16:00 |
| 0000006162 | 1 | 2022/09/15 08:00 | 2022/09/15 | 08:00 |
| 0000006162 | 1 | 2022/09/15 08:00 | 2022/09/15 | 08:00 |
| 0000006161 | 1 | 2022/09/16 08:00 | 2022/09/16 | 08:00 |
| 0000006161 | 1 | 2022/09/16 08:00 | 2022/09/16 | 08:00 |
| 0000006160 | 1 | 2022/09/16 09:00 | 2022/09/16 | 09:00 |
| 0000006160 | 1 | 2022/09/16 09:00 | 2022/09/16 | 09:00 |
| 0000006158 | 3 | 2022/09/15 03:00 | 2022/09/15 | 03:00 |
| 0000006158 | 3 | 2022/09/15 03:00 | 2022/09/15 | 03:00 |
| 0000006157 | 3 | 2022/09/15 01:00 | 2022/09/15 | 01:00 |
| 0000006157 | 3 | 2022/09/15 01:00 | 2022/09/15 | 01:00 |
| 0000006156 | 3 | 2022/09/15 00:20 | 2022/09/15 | 00:20 |
| 0000006156 | 3 | 2022/09/15 00:20 | 2022/09/15 | 00:20 |
| 0000006155 | 1 | 2022/09/14 08:00 | 2022/09/14 | 08:00 |
| 0000006154 | 2 | 2022/09/14 16:00 | 2022/09/14 | 16:00 |
| 0000006154 | 2 | 2022/09/14 16:00 | 2022/09/14 | 16:00 |
| 0000006151 | 1 | 2022/09/14 09:30 | 2022/09/14 | 09:30 |
| 0000006150 | 1 | 2022/09/14 08:00 | 2022/09/14 | 08:00 |
| 0000006148 | 2 | 2022/09/13 22:00 | 2022/09/13 | 22:00 |
| 0000006148 | 2 | 2022/09/13 22:00 | 2022/09/13 | 22:00 |
| 0000006147 | 2 | 2022/09/13 20:00 | 2022/09/13 | 20:00 |
| 0000006147 | 2 | 2022/09/13 20:00 | 2022/09/13 | 20:00 |
| 0000006146 | 2 | 2022/09/13 18:00 | 2022/09/13 | 18:00 |
| 0000006146 | 2 | 2022/09/13 18:00 | 2022/09/13 | 18:00 |
| 0000006145 | 1 | 2022/09/13 14:30 | 2022/09/13 | 14:30 |
| 0000006143 | 1 | 2022/09/13 11:00 | 2022/09/13 | 11:00 |
| 0000006142 | 1 | 2022/09/13 08:00 | 2022/09/13 | 08:00 |
| 0000006141 | 1 | 2022/09/13 08:00 | 2022/09/13 | 08:00 |
| 0000006141 | 1 | 2022/09/13 08:00 | 2022/09/13 | 08:00 |
| 0000006140 | 2 | 2022/09/12 20:00 | 2022/09/12 | 20:00 |
| 0000006140 | 2 | 2022/09/12 20:00 | 2022/09/12 | 20:00 |
| 0000006139 | 2 | 2022/09/12 18:30 | 2022/09/12 | 18:30 |
| 0000006139 | 2 | 2022/09/12 18:30 | 2022/09/12 | 18:30 |
| 0000006138 | 2 | 2022/09/12 17:00 | 2022/09/12 | 17:00 |
| 0000006138 | 2 | 2022/09/12 17:00 | 2022/09/12 | 17:00 |
| 0000006137 | 1 | 2022/09/12 14:00 | 2022/09/12 | 14:00 |
| 0000006137 | 1 | 2022/09/12 14:00 | 2022/09/12 | 14:00 |
| 0000006136 | 1 | 2022/09/12 09:00 | 2022/09/12 | 09:00 |
| 0000006136 | 1 | 2022/09/12 09:00 | 2022/09/12 | 09:00 |
| 0000006134 | 1 | 2022/09/12 08:00 | 2022/09/12 | 08:00 |
| 0000006132 | 3 | 2022/09/11 02:00 | 2022/09/11 | 02:00 |
| 0000006132 | 3 | 2022/09/11 02:00 | 2022/09/11 | 02:00 |
| 0000006131 | 3 | 2022/09/11 00:45 | 2022/09/11 | 00:45 |
| 0000006131 | 3 | 2022/09/11 00:45 | 2022/09/11 | 00:45 |
| 0000006130 | 3 | 2022/09/10 00:00 | 2022/09/10 | 00:00 |
| 0000006130 | 3 | 2022/09/10 00:00 | 2022/09/10 | 00:00 |
| 0000006129 | 1 | 2022/09/10 08:00 | 2022/09/10 | 08:00 |
| 0000006127 | 1 | 2022/09/10 09:30 | 2022/09/10 | 09:30 |
| 0000006127 | 1 | 2022/09/10 09:30 | 2022/09/10 | 09:30 |
| 0000006126 | 1 | 2022/09/10 10:30 | 2022/09/10 | 10:30 |
| 0000006125 | 3 | 2022/09/10 06:00 | 2022/09/10 | 06:00 |
| 0000006124 | 3 | 2022/09/10 05:15 | 2022/09/10 | 05:15 |
| 0000006124 | 3 | 2022/09/10 05:15 | 2022/09/10 | 05:15 |
| 0000006123 | 3 | 2022/09/10 04:00 | 2022/09/10 | 04:00 |
| 0000006123 | 3 | 2022/09/10 04:00 | 2022/09/10 | 04:00 |
| 0000006122 | 3 | 2022/09/10 02:00 | 2022/09/10 | 02:00 |
| 0000006122 | 3 | 2022/09/10 02:00 | 2022/09/10 | 02:00 |
| 0000006121 | 3 | 2022/09/10 01:30 | 2022/09/10 | 01:30 |
| 0000006121 | 3 | 2022/09/10 01:30 | 2022/09/10 | 01:30 |
| 0000006120 | 3 | 2022/09/10 00:30 | 2022/09/10 | 00:30 |
| 0000006120 | 3 | 2022/09/10 00:30 | 2022/09/10 | 00:30 |
| 0000006119 | 3 | 2022/09/10 00:00 | 2022/09/10 | 00:00 |
| 0000006118 | 1 | 2022/09/09 07:40 | 2022/09/09 | 07:40 |
| 0000006118 | 1 | 2022/09/09 07:40 | 2022/09/09 | 07:40 |
| 0000006117 | 3 | 2022/09/09 06:00 | 2022/09/09 | 06:00 |
| 0000006117 | 3 | 2022/09/09 06:00 | 2022/09/09 | 06:00 |
| 0000006116 | 3 | 2022/09/09 05:30 | 2022/09/09 | 05:30 |
| 0000006116 | 3 | 2022/09/09 05:30 | 2022/09/09 | 05:30 |
| 0000006113 | 3 | 2022/09/09 04:00 | 2022/09/09 | 04:00 |
| 0000006113 | 3 | 2022/09/09 04:00 | 2022/09/09 | 04:00 |
| 0000006112 | 3 | 2022/09/09 02:00 | 2022/09/09 | 02:00 |
| 0000006112 | 3 | 2022/09/09 02:00 | 2022/09/09 | 02:00 |
| 0000006111 | 3 | 2022/09/09 01:00 | 2022/09/09 | 01:00 |
| 0000006111 | 3 | 2022/09/09 01:00 | 2022/09/09 | 01:00 |
| 0000006110 | 3 | 2022/09/09 00:00 | 2022/09/09 | 00:00 |
| 0000006110 | 3 | 2022/09/09 00:00 | 2022/09/09 | 00:00 |
| 0000006109 | 1 | 2022/09/08 15:00 | 2022/09/08 | 15:00 |
| 0000006108 | 1 | 2022/09/08 08:00 | 2022/09/08 | 08:00 |
| 0000006108 | 1 | 2022/09/08 08:00 | 2022/09/08 | 08:00 |
| 0000006107 | 1 | 2022/09/08 09:00 | 2022/09/08 | 09:00 |
| 0000006106 | 1 | 2022/09/08 11:00 | 2022/09/08 | 11:00 |
| 0000006105 | 3 | 2022/09/08 06:00 | 2022/09/08 | 06:00 |
| 0000006105 | 3 | 2022/09/08 06:00 | 2022/09/08 | 06:00 |
| 0000006104 | 3 | 2022/09/08 05:00 | 2022/09/08 | 05:00 |
| 0000006104 | 3 | 2022/09/08 05:00 | 2022/09/08 | 05:00 |
| 0000006103 | 3 | 2022/09/08 03:30 | 2022/09/08 | 03:30 |
| 0000006103 | 3 | 2022/09/08 03:30 | 2022/09/08 | 03:30 |
| 0000006102 | 3 | 2022/09/08 02:00 | 2022/09/08 | 02:00 |
| 0000006102 | 3 | 2022/09/08 02:00 | 2022/09/08 | 02:00 |
| 0000006101 | 3 | 2022/09/08 01:00 | 2022/09/08 | 01:00 |
| 0000006101 | 3 | 2022/09/08 01:00 | 2022/09/08 | 01:00 |
| 0000006099 | 2 | 2022/09/07 16:00 | 2022/09/07 | 16:00 |
| 0000006098 | 1 | 2022/09/07 10:00 | 2022/09/07 | 10:00 |
| 0000006097 | 1 | 2022/09/08 11:00 | 2022/09/08 | 11:00 |
| 0000006096 | 1 | 2022/09/07 09:30 | 2022/09/07 | 09:30 |
| 0000006095 | 1 | 2022/09/07 10:30 | 2022/09/07 | 10:30 |
| 0000006094 | 3 | 2022/09/07 05:00 | 2022/09/07 | 05:00 |
| 0000006094 | 3 | 2022/09/07 05:00 | 2022/09/07 | 05:00 |
| 0000006093 | 3 | 2022/09/07 04:15 | 2022/09/07 | 04:15 |
| 0000006093 | 3 | 2022/09/07 04:15 | 2022/09/07 | 04:15 |
| 0000006092 | 3 | 2022/09/07 03:00 | 2022/09/07 | 03:00 |
| 0000006092 | 3 | 2022/09/07 03:00 | 2022/09/07 | 03:00 |
| 0000006091 | 3 | 2022/09/07 02:00 | 2022/09/07 | 02:00 |
| 0000006091 | 3 | 2022/09/07 02:00 | 2022/09/07 | 02:00 |
| 0000006090 | 2 | 2022/09/06 21:30 | 2022/09/06 | 21:30 |
| 0000006090 | 2 | 2022/09/06 21:30 | 2022/09/06 | 21:30 |
| 0000006086 | 3 | 2022/09/06 06:10 | 2022/09/06 | 06:10 |
| 0000006086 | 3 | 2022/09/06 06:10 | 2022/09/06 | 06:10 |
| 0000006085 | 3 | 2022/09/06 05:00 | 2022/09/06 | 05:00 |
| 0000006085 | 3 | 2022/09/06 05:00 | 2022/09/06 | 05:00 |
| 0000006084 | 3 | 2022/09/06 01:45 | 2022/09/06 | 01:45 |
| 0000006084 | 3 | 2022/09/06 01:45 | 2022/09/06 | 01:45 |
| 0000006083 | 3 | 2022/09/06 23:30 | 2022/09/06 | 23:30 |
| 0000006083 | 3 | 2022/09/06 23:30 | 2022/09/06 | 23:30 |
| 0000006082 | 2 | 2022/09/05 20:30 | 2022/09/05 | 20:30 |
| 0000006082 | 2 | 2022/09/05 20:30 | 2022/09/05 | 20:30 |
| 0000006081 | 2 | 2022/09/05 21:00 | 2022/09/05 | 21:00 |
| 0000006081 | 2 | 2022/09/05 21:00 | 2022/09/05 | 21:00 |
| 0000006077 | 1 | 2022/09/05 12:30 | 2022/09/05 | 12:30 |
| 0000006076 | 1 | 2022/09/05 10:00 | 2022/09/05 | 10:00 |
| 0000006075 | 1 | 2022/09/05 14:00 | 2022/09/05 | 14:00 |
| 0000006074 | 1 | 2022/09/05 09:00 | 2022/09/05 | 09:00 |
| 0000006074 | 1 | 2022/09/05 09:00 | 2022/09/05 | 09:00 |
| 0000006072 | 2 | 2022/09/06 16:00 | 2022/09/06 | 16:00 |
| 0000006072 | 2 | 2022/09/06 16:00 | 2022/09/06 | 16:00 |
| 0000006071 | 1 | 2022/09/03 10:00 | 2022/09/03 | 10:00 |
| 0000006070 | 2 | 2022/09/02 15:30 | 2022/09/02 | 15:30 |
| 0000006070 | 2 | 2022/09/02 15:30 | 2022/09/02 | 15:30 |
| 0000006068 | 2 | 2022/09/02 18:00 | 2022/09/02 | 18:00 |
| 0000006068 | 2 | 2022/09/02 18:00 | 2022/09/02 | 18:00 |
| 0000006067 | 1 | 2022/09/02 14:15 | 2022/09/02 | 14:15 |
| 0000006066 | 1 | 2022/09/02 13:00 | 2022/09/02 | 13:00 |
| 0000006065 | 1 | 2022/09/02 10:00 | 2022/09/02 | 10:00 |
| 0000006064 | 1 | 2022/09/02 10:30 | 2022/09/02 | 10:30 |
| 0000006063 | 1 | 2022/09/02 10:00 | 2022/09/02 | 10:00 |
| 0000006062 | 1 | 2022/09/02 08:15 | 2022/09/02 | 08:15 |
| 0000006061 | 1 | 2022/09/02 09:30 | 2022/09/02 | 09:30 |
| 0000006060 | 1 | 2022/09/03 10:00 | 2022/09/03 | 10:00 |
| 0000006058 | 2 | 2022/09/01 20:00 | 2022/09/01 | 20:00 |
| 0000006058 | 2 | 2022/09/01 20:00 | 2022/09/01 | 20:00 |
| 0000006057 | 2 | 2022/09/01 16:00 | 2022/09/01 | 16:00 |
Problem I am facing here is in WHERE statement.
When I try to filter the yesterday's data by using
sysdate-TO_DATE(srvb.rectarih1,'YYYY/MM/DD')=1
I am getting this error -->
"ORA-01841:"(full) year must be between -4713 and +9999, and not be 0" Cause: Illegal year entered Action: Input year in the specified range"