Sample Header Ad - 728x90

How to create month ranges without using a specific date in Postgresql

0 votes
1 answer
486 views
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;
Asked by ColRow (43 rep)
Apr 2, 2020, 12:56 PM
Last activity: Apr 29, 2025, 11:05 PM