sqlpostgresql

Count of available items with start and end dates grouped by month


I am working with PSQL/pgAdmin, looking to get a total # of inventory grouped by month for the year. I've tried a generated_series, but when I try to use a Count Filter it seems to ignore the date.

My attempt

select
g.day::date,
count(item) filter(
  where install_date <= g.day+'1month'::interval
  and (removed_date is null or removed_date < g.day+'1month'::interval))
FROM table
CROSS  JOIN generate_series('2024-01-01'::timestamp, '2025-01-01'::timestamp, '1 month'::interval) AS g(day)
group by 1,2

As an example:

Item Install_Date Removed_Date
Item1 Jan 2024 Mar 2024
Item3 Feb 2024 Apr 2024

So the results would look something like

Month Install_Count
Jan 1
Feb 2
Mar 1
Apr 0

Solution

  • All months are generated within a date range.

    Each month is then compared with install_date and removed_date should be greater than the month_start so that it is excluded from that month.

    Fiddle

    WITH month_series AS (
        SELECT generate_series('2024-01-01'::date, '2024-12-01'::date, '1 month'::interval) AS month_start
    )
    SELECT 
        to_char(month_series.month_start, 'Mon YYYY') AS month, 
        COUNT(i.item) AS install_count
    FROM 
        month_series
    LEFT JOIN 
        inventory i 
        ON i.install_date <= month_series.month_start 
        AND (i.removed_date IS NULL OR i.removed_date > month_series.month_start)
    GROUP BY 
        month_series.month_start
    ORDER BY 
        month_series.month_start;
    

    Output

    month install_count
    Jan 2024 1
    Feb 2024 2
    Mar 2024 1
    Apr 2024 0
    May 2024 0
    Jun 2024 0
    Jul 2024 0
    Aug 2024 0
    Sep 2024 0
    Oct 2024 0
    Nov 2024 0
    Dec 2024 0