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 dateadd(wk, datediff(wk, 0, getdate()), 0) as ThisWeekStart

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 < dateadd(wk, datediff(wk, 0, getdate()), 0) -- exclude current week

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 dateadd(wk, datediff(wk, 0, sold_at), 0) ,sum(revenue) as weekly_revenue
from sales
where sold_at < dateadd(wk, datediff(wk, 0, getdate()), 0) -- exclude sales from current week 
group by dateadd(wk, datediff(wk, 0, sold_at), 0)
order by dateadd(wk, datediff(wk, 0, sold_at), 0);

IN THIS PAGE