2 Replies Latest reply: Mar 8, 2013 3:54 AM by Henric Cronström RSS

    problems with one CASE into one select

      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:

       

      dateshiftshif_group
      2013-01-01morningA
      2013-01-01eveningB
      2013-01-01nightC
      2013-01-02nightC

       

       

      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!