0

What's the best way to create month ranges without using specific dates?

For example, today is April the 4th and I want to calculate the sales rate of the past 12 months, that I can run every month without updating the date.

I was thinking of working with current_date, but is there a way to create a more efficient query?

select 
datetrunc('month',date)
,count(id) filter (where status='sold' and (date between (datetrunc('month','current_date - interval '1 months')) 
and datetrunc('month', current_date) - Interval '1 days')) / count(id) filter 
(where date between (datetrunc('month','current_date - interval '1 months')) and 
datetrunc('month', current_date) - Interval '1 days') as Mar2020_Sales_Rate
,..... as Feb2020_Sales_Rate
,.
,.
,..... as Mar2019_Sales_Rate
from sales
group by 1;
0

This is the way I chose to get 1st and end of the month, without using current date, but rather the creation date (of an order) in my data. Please suggest any other query that might seem more effective or if you feel there is a flaw in this query:

select distinct creation_date::date,
       (creation_date - Interval '1 months')::date as Minus_1M,
       (creation_date - Interval '2 months')::date as Minus_2M,
       (creation_date - Interval '3 months')::date as Minus_3M,
       extract('day' from creation_date) as Day_of_month,
       date_trunc('month', (creation_date::date - extract('day' from 
       creation_date)::int))::date as Beginning_of_month,
       creation_date::date - extract('day' from creation_date)::int as End_of_month
from order_data
order by 1 desc;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.