SQL COUNT number of patients each month

Question

I have a table with:

PATIENT_ID	START_DATE	END_DATE	Ward
1	19/01/2022	19/02/2022	A
2	20/01/2022	19/03/2022	A

And I want to create a summarized table to show for each month, how many patients were active in that ward as well as the total number of patient days for that month. Is this possible in SQL? I'm thinking I might need an external DIM_DATE table that has all of the months up until now and starts from the first START_DATE out of all the PATIENT_ID's but doesn't sound very efficient? Note that I have shown only 1 ward but there are also different wards.

Expected result:

Month	Ward	COUNT_PATIENTS	TOTAL_NUMBER_DAYS
31/01/2022	A	2	33
28/02/2022	A	2	47
31/03/2022	A	1	19

Answer 1

with data(PATIENT_ID, START_DATE, END_DATE, Ward) as (
    select column1, to_date(column2, 'dd/mm/yyyy'), to_date(column3, 'dd/mm/yyyy'), column4
    from values
        (1, '19/01/2022','19/02/2022','A'),
        (2, '20/01/2022','19/03/2022','A')
), ranges as (
    select 
        date_trunc('month', min(start_date)) as min_start, 
        dateadd('day', -1, dateadd('month', 1, date_trunc('month', max(end_date)))) as max_end
    from data
), gen as (
    select 
        row_number() over(order by null)-1 as rn
    from table(generator(ROWCOUNT => 1000))
), all_months as (
    select 
        dateadd('month', g.rn, date_trunc(month, r.min_start)) as month_start,
        dateadd('day', -1, dateadd('month', 1, month_start)) as month_end
    from ranges as r
    cross join gen as g
    qualify month_start <= r.max_end
)
select 
    a.month_end as month,
    d.ward,
    a.month_end))+1 as days
    count(distinct patient_id) as count_patients,
    sum(datediff(days, greatest(d.start_date, a.month_start), least(d.END_DATE, a.month_end))+1) as total_numbers_days
from all_months as a
left join data as d
    on a.month_start between  date_trunc('month', d.START_DATE) and date_trunc('month', d.END_DATE)
group by 1,2
order by 1,2

gives:

MONTH	WARD	COUNT_PATIENTS	TOTAL_NUMBERS_DAYS
2022-01-31	A	2	25
2022-02-28	A	2	47
2022-03-31	A	1	19

I think your 33 is wrong, as the partials are:

MONTH	WARD	PATIENT_ID	_START	_END	DAYS
2022-01-31	A	1	2022-01-19	2022-01-31	13
2022-01-31	A	2	2022-01-20	2022-01-31	12
2022-02-28	A	1	2022-02-01	2022-02-19	19
2022-02-28	A	2	2022-02-01	2022-02-28	28
2022-03-31	A	2	2022-03-01	2022-03-19	19