I have the following query that pulls all records from a Redshift table from January 1st of the current year through the final date of the most recent, full quarter.
SELECT *
FROM table
WHERE date_value BETWEEN DATE_TRUNC('year',getdate()) AND DATE_TRUNC('quarter',dateadd(day,-1,getdate()));
I now want to create a period-over-period comparison query that returns all records for the previous n months. Ex. if the first query returns all records for Jan - Jun 2022, this query will return all records for Jul - Dec 2021.
Here is what I have so far, however it currently returns Jan - Jun 2021 instead of the desired date range. I've tried playing around with DATEDIFF() instead of DATEADD() but haven't had any luck with that either. Any help is much appreciated.
SELECT *
FROM TABLE
WHERE date_value BETWEEN DATE_TRUNC('year',dateadd(year,-1,getdate())) AND DATE_TRUNC('quarter',dateadd(year,-1,getdate()));