I think I follow! You're on the right track with a date spine but you don't necessarily need a table for it. You can generate it from a generated series of numbers like so:
SELECT
'2024-01-01'::datetime + interval '1 month' * generate_series(0, ((DATE_PART('year', getdate ()) - DATE_PART('year', '2024-01-01'::date)) * 12 + (DATE_PART('month', getdate ()) - DATE_PART('month', '2024-01-01'::date)))::int) AS month;
This means that you don't have to maintain or store this table and running a new iteration with a different beginning and end date only takes a few ms.
Assuming you have a table shelter_stays that looks like:
(Species ID would probably be an attribute on the visitor record here but easier to illustrate like this)
with month_spine as (
SELECT
-- our starting date for the date spine
'2024-01-01'::date
-- the interval at which we want to increment the date for each row of this table
+ interval '1 month'
-- Multiplying the interval by an incrementing series of integers, starting at zero
* generate_series(0,
-- Calculate the upper end of the series, starting with the number of years between two dates multiplied by 12 to convert to months
((DATE_PART('year', current_date) - DATE_PART('year', '2024-01-01'::date)) * 12
-- plus the number of months between the two dates, irrespective of the year
+ (DATE_PART('month', current_date) - DATE_PART('month', '2024-01-01'::date)))::int) AS month;
)
select
-- selecting month and species here. Any additional columns selected and grouped by will mean that your count is specific to each row where all of the grouped attributes are the same
month,
species_id,
-- distinct to only count a unique stay once per month when the stay starts and end in the same month
count(distinct id)
from shelter_stays
inner join month_spine on
-- joining on either of these gives us two rows per matching condition
date_trunc('month', shelter_days.visit_start_date) = month_spine.month
or date_trunc('month', shelter_days.visit_end_date) = month_spine.month
--grouping to make our count function work
group by 1, 2
From there you can add additional joins and groupings as needed. If you have multiple shelters to query add the shelter ID in the select and group by to split the count out by the attribute as well.