By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table dataset(
ID int, Start_date Date,End_date Date);
insert into dataset values
(342,'2022-01-01','2022-02-12'),
(231,'2022-01-12','2022-02-26'),
(123,'2022-01-20','2022-04-10');
/*
Desired output:
Month Start Date
Jan 2
Feb 3
Mar 1
Apr 1
*/
select
min(month(Start_date)),
max(month(End_date))
from dataset;
min(month(Start_date)) |
max(month(End_date)) |
|---|---|
1 |
4 |
with recursive m as
(select min(month(Start_date)) mon from dataset
union all
select mon + 1 from m
where mon < (select max(month(End_date)) from dataset)
)
select
mon "month",
count(id) "Count"
from m
left join dataset
on month(Start_date)<= mon
and month(End_date) >= mon
group by mon
order by mon;
month |
Count |
|---|---|
1 |
3 |
2 |
3 |
3 |
1 |
4 |
1 |