By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table offers
(
ID int,
FromDate date,
ToDate date
);
insert into offers values
(1, '2022-01-02', '9999-12-31'),
(1, '2022-01-02', '2022-02-10'),
(2, '2022-01-05', '2022-02-15'),
(3, '2022-01-02', '9999-12-31'),
(3, '2022-01-15', '2022-02-15'),
(3, '2022-02-03', '2022-02-25'),
(4, '2022-01-16', '2022-02-05'),
(5, '2022-01-17', '2022-02-13'),
(5, '2022-02-05', '2022-02-13');
9 rows affected
with
dates as
(
-- get all date points
select ID, theDate = FromDate from offers
union -- union to exclude any duplicate
select ID, theDate = ToDate from offers
),
cte as
(
select ID = d.ID,
Date_Start = d.theDate,
Date_End = LEAD(d.theDate) OVER (PARTITION BY ID ORDER BY theDate),
TheCount = c.cnt
from dates d
cross apply
(
select cnt = count(*)
from offers x
where x.ID = d.ID
and x.FromDate <= d.theDate
and x.ToDate > d.theDate
) c
)
select ID, TheCount, days = sum(datediff(day, Date_Start, Date_End))
from cte
where Date_End is not null
group by ID, TheCount
order by ID, TheCount
| ID | TheCount | days |
|---|---|---|
| 1 | 1 | 2913863 |
| 1 | 2 | 39 |
| 2 | 1 | 41 |
| 3 | 1 | 2913861 |
| 3 | 2 | 29 |
| 3 | 3 | 12 |
| 4 | 1 | 20 |
| 5 | 1 | 19 |
| 5 | 2 | 8 |