title: how to correctly use case in where clause
body: <p>So i have a procedure that i'm currently in the process of debugging and i've narrowed it down to this select statement.</p>
  
  <blockquote>
    <p>Note: where the to_date(''), 3300, 5220 is a representation of what would come from a parameter.         </p>
  </blockquote>
  
  <p>Now what this is suppose to do, is take the parameter which is a timestamp and subtract an offset value</p>
  
  <blockquote>
    <p>the offset is the number of minutes that have gone by since the beginning of the week where sunday at midnight = 0. (So if it was monday at midnight the offset would = 1440).</p>
  </blockquote>
  
  <p>when the offset is subtracted from the parameter, you then get the beginning of the week. You then get the offset value from the table which was already predetermined and add that value to the start of the week to obtain the timestamp.</p>
  
  <p>This is done in order to get the start date and end date of a shift.</p>
  
  <p>My original code works no problem with is below, however it is missing the boundary condition of saturday going into sunday.</p>
  
  <pre><code>SELECT SHIFT_ID_PK, SHIFT_NAME_FK,
           SHIFT_START_DAY, SHIFT_START_TIME,
           SHIFT_END_DAY, SHIFT_END_TIME, 
           SITE_ID_FK, SHIFT_DAY_ID,
           STARTOFFSET, ENDOFFSET,
           TO_TIMESTAMP_TZ(TO_CHAR((PSTARTTIMESTAMP - (VSTARTOFFSET  / 24 / 60)) + (STARTOFFSET / 24 / 60), 'YYYY-MM-DD HH:MI:SS AM'),  'YYYY-MM-DD HH:MI:SS AM TZH:TZM') as SHIFT_START_DATE,
           TO_TIMESTAMP_TZ(TO_CHAR((PENDTIMESTAMP -  (VENDOFFSET / 24 / 60)) + (ENDOFFSET   / 24 / 60), 'YYYY-MM-DD HH:MI:SS AM') ,'YYYY-MM-DD HH:MI:SS AM TZH:TZM') as SHIFT_END_DATE
    from   shift_tbl
    WHERE
           ENDOFFSET &gt;= VSTARTOFFSET
    and    STARTOFFSET &lt; VENDOFFSET 
    order by shift_start_date asc;
  </code></pre>
  
  <p>Now what i have come up with to handle this boundary condition is below which i have been testing in a script.</p>
  
  <pre><code>   declare
    VSTARTOFFSET integer;
    VENDOFFSET integer;
    SHIFTOFFSET integer;
    PSTARTTIMESTAMP timestamp;
    PENDTIMESTAMP timestamp;
    begin 
      VSTARTOFFSET := 10020;
      VENDOFFSET := 420; 
      PSTARTTIMESTAMP := TO_DATE('3/17/2012 23:00', 'mm/dd/yyyy hh24:mi');
      PENDTIMESTAMP :=   TO_DATE('3/18/2012 7:00', 'mm/dd/yyyy hh24:mi');
  
    SELECT SHIFT_ID_PK, SHIFT_NAME_FK,
           SHIFT_START_DAY, SHIFT_START_TIME,
           SHIFT_END_DAY, SHIFT_END_TIME, 
           SITE_ID_FK, SHIFT_DAY_ID,
           STARTOFFSET, ENDOFFSET,
           TO_TIMESTAMP_TZ(TO_CHAR((PSTARTTIMESTAMP - (VSTARTOFFSET / 24 / 60)) + (STARTOFFSET / 24 / 60), 'YYYY-MM-DD HH:MI:SS AM'),'YYYY-MM-DD HH:MI:SS AM TZH:TZM') as SHIFT_START_DATE,  
           TO_TIMESTAMP_TZ(TO_CHAR((PENDTIMESTAMP- (VENDOFFSET / 24 / 60)) + (ENDOFFSET   / 24 / 60), 'YYYY-MM-DD HH:MI:SS AM'),'YYYY-MM-DD HH:MI:SS AM TZH:TZM') AS SHIFT_END_DATE
    from   SHIFT_TBL
    where
      case 
        when SHIFT_START_DAY = 7 and  SHIFT_END_DAY = 1 then          
                   SHIFTOFFSET:= ENDOFFSET + 10080; 
            and    VENDOFFSET := VENDOFFSET + 10080;
        else
                   SHIFTOFFSET := ENDOFFSET;
      end
                   SHIFTOFFSET &gt;= VSTARTOFFSET
            and    STARTOFFSET &lt; VENDOFFSET 
      order by SHIFT_START_DATE asc; 
    end;
  </code></pre>
  
  <p>As you can see i am unsure as to how to handle the case statement inside of the where clause. Basically what i am trying to do is if the start day is Saturday and the end day is Sunday, then add 10080(one week) to the end offset/vend offset and if it does not meet that condition, then use the original values.</p>
  
  <p>Basically my question is fairly simple...i believe but i am having difficultly obtaining the solution. So what i would like to know is how to properly use a case statement inside of the where clause. And if i am not suppose to use a case statement in this form inside a where clause how exactly would i set up this select statement.</p>
  
  <p>Any help or suggestions are greatly appreciated.
  Thank you.</p>