How to Exclude Current or Partial Weeks

SQL Tutorial Home

If we have a table like:

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

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

select date_trunc('week', now())

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 < date_trunc('week', now()) -- 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 date_trunc('week', sold_at),sum(revenue) as weekly_revenue
from sales
where sold_at < date_trunc('week', now()) -- exclude sales from current week 
-- include data from the previous 4 whole weeks
and sold_at > date_trunc('week',now()) - interval '4 weeks'
group by 1
order by 1;

IN THIS PAGE