--Declare Variables 
variable  PrevWeekStateDate varchar2(30);
variable  PrevWeekEndDate  varchar2(30);

--Hardcodes date - I dont want this dynamically get the dates
exec  :PrevWeekStateDate :='11022015';
exec  :PrevWeekEndDate :='11082015';
 
/*  How do I assign the select to the var?
--Set Variables bases on current date to get previous Monday and previous Sunday
exec  :PrevWeekStateDate :=  (select next_day (sysdate-8,'MONDAY')  from dual);
exec  :PrevWeekEndDate :=   (select next_day (sysdate-7, 'SUNDAY')  from dual);

--2nd attempt to assign via into
--Set Variables bases on current date to get previous Monday and previous Sunday
select next_day (sysdate-8,'MONDAY') into PrevWeekStateDate from dual;
select next_day (sysdate-7, 'SUNDAY') into PrevWeekEndDate from dual;
*/

--Get results set
SELECT 
    COUNT(*)
 FROM worklight.TBL_ACTIVITY_LOG
WHERE ACTIVITY LIKE '%Refill Proc%'
AND activity_dttm between to_date(:PrevWeekStateDate, 'mmddyyyy')
AND to_date(:PrevWeekEndDate, 'mmddyyyy');

SELECT Count(*) 
FROM   worklight.tbl_activity_log 
WHERE  activity LIKE '%Refill Proc%' 
       AND activity_dttm BETWEEN Next_day (SYSDATE - 8, 'MONDAY') AND 
                                 Next_day (SYSDATE - 7, 'SUNDAY'); 

Next_day (SYSDATE - 8, 'MONDAY') AND Next_day (SYSDATE - 7, 'SUNDAY')

 Next_day (SYSDATE - 15, 'MONDAY') AND   Next_day (SYSDATE - 9, 'SUNDAY');