CODEWITHSUNDEEP
sql
Henry Lim
Henry Lim

Reputation: 1

SQL: Find number of active "events" each month

Background

I have an SQL table that contains all events, with each event containing a unique identifier.

As you can see for some IDs the "event" stretches across multiple months. What I'm trying to find is the number of "active events" per month.

For example event ID:342, is active in both the month of Jan and Feb. So it should count towards both Jan and Feb's final count.

Example dataset

ID Start Date End Date
342 01 Jan 2022 12 Feb 2022
231 12 Feb 2022 26 Feb 2022
123 20 Jan 2022 10 Apr 2022

Desired output:

Month Start Date
Jan 2
Feb 3
Mar 1
Apr 1

btw: I'm using Alibaba's ODPS SQL and not MySQL or Postgres. So i appreciate if the solution provided could be SQL system agnostic. Thanks!

Upvotes: 0

Views: 346

Answers (1)

user18098820
user18098820

Reputation:

Here is an example is MySQL 8, using a recursive CTE to construct the list of months. It would be more efficient to use a Calendar Table.
If you are not using MySQL you will need to modify the syntax of the query.

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

db<>fiddle here

Upvotes: 1

Related Questions