Hello community! I need help with the following situation:
I have the following table stores the errors in a production process:
| stops |
|---|
| id_stop |
| error_description |
| start_time |
| end_time |
| duration |
My problem is, I need to identify how many errors occur per shift, but shifts currently we not stored anywhere.
the information its should be, for example:
| date | shift | shif_group |
|---|
| 2013-01-01 | morning | A |
| 2013-01-01 | evening | B |
| 2013-01-01 | night | C |
| 2013-01-02 | night | C |
Morning shifts are from 6 am to 14 pm.
the evening from 14 PM to 22 PM.
the night from 22 PM to 6 PM.
I'm trying to do the following:
select id_stop, error_description, start_time, end_time, duration,
shift= CASE
when DatePart(hh, start_time) between 22 and 23 then 'Night'
when DatePart(hh, start_time) between 00 and 05 then 'Night'
when DatePart(hh, start_time) between 06 and 13 then 'Morning'
when DatePart(hh, start_time) between 14 and 21 then 'Evening'
end
from stops
where start_time >= '2013-01-01' ;
But I have an error, "the column shift does not exist".
Can anyone help me out in this problem? or perhaps with a better idea how solved this 🙂
from already thank you very much!
.png "Former Employee"){kind=icon}