SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
drop table if exists #input, #service, #calendar, #result

create table #input (id int, cdate date)
insert into #input
values (1,'31 mar 2022'), (2, '26 feb 2023'), (3, '01 apr 2023')

create table #service (id int, start_date date, end_date date, countable bit)
insert into #service values
(1,'15 Nov 1978','30 Jul 2008',1),
(1,'31 Jul 2008','31 Jul 2008',0),
(1,'01 Aug 2008','19 Aug 2008',1),
(1,'20 Aug 2008','20 Aug 2008',0),
(1,'21 Aug 2008','29 Jun 2011',1),
(1,'30 Jun 2011','30 Jun 2011',0),
(1,'01 Jul 2011','29 Aug 2012',1),
(1,'30 Aug 2012','30 Nov 2012',0),
(1,'01 Dec 2012','19 Mar 2013',1),
(1,'20 Mar 2013','20 Mar 2013',0),
(1,'21 Mar 2013','12 May 2013',1),
(1,'13 May 2013','14 May 2013',0),
(1,'15 May 2013','09 Jul 2014',1),
(1,'10 Jul 2014','10 Jul 2014',0),
(1,'11 Jul 2014','31 Mar 2015',1),
(1,'01 Apr 2015','16 Jul 2017',1),
(1,'17 Jul 2017','30 Jul 2017',0),
(1,'31 Jul 2017','15 Jul 2018',1),
(1,'16 Jul 2018','29 Jul 2018',0),
(1,'30 Jul 2018','28 Jul 2019',1),
(1,'29 Jul 2019','11 Aug 2019',0),
(1,'12 Aug 2019','02 Aug 2020',1),
(1,'03 Aug 2020','16 Aug 2020',0),
(1,'17 Aug 2020','31 Oct 2020',1),
(1,'01 Nov 2020','04 Jul 2021',1),
(1,'05 Jul 2021','18 Jul 2021',0),
(1,'19 Jul 2021','10 Jul 2022',1),
(1,'11 Jul 2022','24 Jul 2022',0),
61 rows affected

create table #calendar(calendardate date primary key)

declare @dt as date = '01 jan 1970'

while @dt <= '31 dec 2030'
begin
    insert into #calendar values (@dt)
    set @dt = dateadd(d,1,@dt)
end

select count(*) from #calendar


(No column name)


22280


-- An alternative way to generate the calandar using a CTE.
create table #calendar2(calendardate date primary key)

declare @cal_from_dt as date = '01 jan 1970'
declare @cal_thru_dt as date = '31 dec 2030'

;with cte as (
    select @cal_from_dt as calendardate
    union all
    select dateadd(day, 1, calendardate) as calendardate
    from cte
    where calendardate < @cal_thru_dt
)
insert #calendar2
select calendardate
from cte
option(maxrecursion 0)

select count(*) from #calendar2


(No column name)


22280


-- original code
;with cte as (
    select
        row_number() over (partition by i.id, i.cdate order by calendardate desc) rn,
        i.id, i.cdate, c.calendardate
    from #input i 
    inner join #calendar c
        on c.calendardate <= i.cdate --only count where calendardate <= calculation date 
    inner join #service s 
        on s.id = i.id
        and c.calendardate between s.start_date and s.end_date
        and s.countable = 1 --calendardate within countable period of service
)
select *
into #result 
from cte
where rn <= 1200

select id, count(*) cnt FROM #result group by id order by id



id
cnt


1
1200

2
1200

3
411


-- Restructured joins
;with cte as (
    select
        row_number() over (partition by i.id, i.cdate order by calendardate desc) rn,
        i.id, i.cdate, c.calendardate
    from #input i 
    inner join #service s 
        on s.id = i.id
        and s.start_date <= i.cdate
        and s.countable = 1 -- countable period of service
    cross apply (
        select
            s.start_date as from_date,
            case when s.end_date <= i.cdate then s.end_date else i.cdate end as thru_date
    ) dr
    inner join #calendar c
        on c.calendardate between dr.from_date and dr.thru_date -- include dated within range
)
select *
into #result2 
from cte
where rn <= 1200

select id, count(*) cnt FROM #result2 group by id order by id



id
cnt


1
1200

2
1200

3
411


-- SQL Server 2022 or later
-- Restructured joins using LEAST() and GENERATE_SERIES()
;with cte as (
    select
        row_number() over (partition by i.id, i.cdate order by calendardate desc) rn,
        i.id, i.cdate, c.calendardate
    from #input i 
    inner join #service s 
        on s.id = i.id
        and s.start_date <= i.cdate
        and s.countable = 1 --calendardate within countable period of service
    cross apply (
        select
            s.start_date as from_date,
            least(s.end_date, i.cdate) as thru_date
    ) dr
    cross apply (
        select dateadd(day, s.value, dr.from_date) as calendardate
        from generate_series(0, datediff(day, dr.from_date, dr.thru_date)) s
    ) c
)
select *
into #result3 
from cte
where rn <= 1200

select id, count(*) cnt FROM #result3 group by id order by id



id
cnt


1
1200

2
1200

3
411