I got this from someone in the forum last month as I had the same question.
... INNER JOIN (Select calendar_date-day_of_week-6 as min_date, calendar_date-day_of_week as max_date from sys_calendar.calendar where calendar_date = current_date) date_range ON trans_date between date_range.min_date and date_range.max_date ...
I perform a similar query quite often when I want to get data from the first of the month until today's date--and the way I do it is by casting my date column as a date then taking day from current_date-current_date plus one as my first day of the month and then simply current_date for today.
The code example is below:
[code] cast(a.snapshot_date as date) BETWEEN (current_date - EXTRACT(DAY FROM current_date)+1) AND current_date; [/code]
So I'm sure this could easily be set to pull the previous week's data by just inserting the code you had for a week ago--i.e. ((current_date -7)).