How to Exclude Current or Partial Weeks

SQL Tutorial Home

If we have a table like:

WITH sales AS (
  SELECT DATETIME '2022-03-22 20:10:00' AS sold_at,20.34 as revenue UNION ALL
  SELECT DATETIME '2022-03-26 20:10:00', 10.00 UNION ALL
  SELECT DATETIME '2022-04-05 20:10:00', 16.00 
)
select revenue, sold_at from sales;
revenue sold_at
20.34 2022-03-22T20:10:00
10.0 2022-03-26T20:10:00
16.0 2022-04-05T20:10:00

The following date expression returns the starting point of the first week of the April month:

select date_trunc('2022-04-01',WEEK)

If we want to show all the sales except those occurred during the first week of the April month, we can do:

WITH sales AS (
  SELECT DATETIME '2022-03-22 20:10:00' AS sold_at,20.34 as revenue UNION ALL
  SELECT DATETIME '2022-03-26 20:10:00', 10.00 UNION ALL
  SELECT DATETIME '2022-04-05 20:10:00', 16.00 
)
select revenue, sold_at from sales 
where CAST(sold_at as DATE) < date_trunc('2022-04-01', WEEK);
revenue sold_at
20.34 2022-03-22T20:10:00
10.0 2022-03-26T20:10:00

IN THIS PAGE