How to Exclude Current or Partial Weeks

SQL Tutorial Home

If we have a table like:

create table sales ( sold_at 	date,
			   revenue	number(6,2)
		   	);

The following date expression returns the starting point of the current week:

select TRUNC(sysdate, 'iw') from dual;

Then, if we want to show all the sales except those occurred during the current week, we can do:

select sold_at, revenue as weekly_revenue from sales
where sold_at < TRUNC(sysdate, 'iw') -- exclude current week records

Finally if we want data from whole weeks, (avoiding data from partial weeks), for example if we want to show the weekly revenue of the last 4 whole weeks, excluding the current week, we can do:

select TRUNC(sold_at, 'iw') week_first_day,
       sum(revenue) as weekly_revenue
from sales
where sold_at < TRUNC(sysdate, 'iw') -- exclude sales from current week 
  -- include data from the previous 4 whole weeks (28 days)
  and sold_at > TRUNC(sysdate, 'iw') - 28 
group by TRUNC(sold_at, 'iw')
order by 1;

IN THIS PAGE