By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DECLARE @t TABLE (active_dt date, term_dt date, id int);
INSERT INTO @t VALUES
('2018-01-01', NULL, 101),
('2018-01-01', '2018-05-15', 102),
('2018-03-01', '2018-06-01', 103),
('2018-01-01', '2018-04-25', 104);
WITH cte1(date, val) AS (
SELECT active_dt, 1 FROM @t AS t
UNION ALL
SELECT COALESCE(term_dt, '2099-01-01'), -1 FROM @t AS t
-- if end date is null then assume the row is valid indefinitely
), cte2 AS (
SELECT date, SUM(val) OVER(ORDER BY date, val) AS rs
FROM cte1
)
SELECT YEAR(date) AS YY, MONTH(date) AS MM, MAX(rs) AS MaxActiveThisYearMonth
FROM cte2
GROUP BY YEAR(date), MONTH(date)
ORDER BY YEAR(date), MONTH(date)
| YY | MM | MaxActiveThisYearMonth |
|---|---|---|
| 2018 | 1 | 3 |
| 2018 | 3 | 4 |
| 2018 | 4 | 3 |
| 2018 | 5 | 2 |
| 2018 | 6 | 1 |
| 2099 | 1 | 0 |