how to use the case correctly in the where clause

So i have a procedure that i'm currently in the process of debugging and i've narrowed it down to this select statement.

Note: where the to_date(''), 3300, 5220 is a representation of what would come from a parameter.

Now what this is suppose to do, is take the parameter which is a timestamp and subtract an offset value

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).

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.

This is done in order to get the start date and end date of a shift.

My original code works no problem with is below, however it is missing the boundary condition of saturday going into sunday.

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 >= VSTARTOFFSET
  and    STARTOFFSET < VENDOFFSET
  order by shift_start_date asc;

Now what i have come up with to handle this boundary condition is below which i have been testing in a script.

   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 >= VSTARTOFFSET
          and    STARTOFFSET < VENDOFFSET
    order by SHIFT_START_DATE asc;
  end;

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.

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.

Any help or suggestions are greatly appreciated. Thank you.

You do not need to set any variables in the WHERE clause, actually even you can't do it. What you want to do is to write correct logical predicate (that is an expression returning true or false) describing rows you want to get.

Here are 2 examples how I would try to define it (as far as I understand your requirements):

1. without CASE:

   WHERE
   ( SHIFT_START_DAY = 7 and  SHIFT_END_DAY = 1  AND ENDOFFSET + 10080 >= VSTARTOFFSET and STARTOFFSET < VENDOFFSET + 10080) OR
   ( NOT (SHIFT_START_DAY = 7 and  SHIFT_END_DAY = 1) AND SHIFTOFFSET >= VSTARTOFFSET and STARTOFFSET < VENDOFFSET )
   
   

2. with CASE:

   WHERE
   (CASE WHEN SHIFT_START_DAY = 7 and  SHIFT_END_DAY = 1 THEN ENDOFFSET + 10080 ELSE ENDOFFSET END) >= VSTARTOFFSET
   AND    STARTOFFSET < (CASE WHEN SHIFT_START_DAY = 7 and  SHIFT_END_DAY = 1 THEN VENDOFFSET + 10080 ELSE VENDOFFSET END)
   
   

I did not debug this expressions so do not expect them to work ;), but I hope that you have got the idea.