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
Last activity: Apr 29, 2025, 11:05 PM