Skip to content
Advertisement

SQL query to get values by the calendar month

I have the following schema:

and i want to get a return object that has the industry id along with the number of items but based on the calendar month for the previous two months, not including the current. So i want the number of items in October and the number in November.

What i have, that is not working

What i am currently getting from the above :

What i am expecting:

So industry 1, it has two items with start_date in October and 1 in November.

Advertisement

Answer

date_trunc('month', ...) always returns the first of the month, so you don’t really need the >= .. <= in your CASE expression.

It’s enough to compare the month start of “start_date” with the month start of last month, which is date_trunc('month', current_date - interval '1 month')

The query can also be simplified a bit by using the filter operator.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement