SO 39105589 - Project monthly revenue over lease periods, Sql Server

Language: Layout:

create table Lease
    (Lease_id int, Apt_id int, Resident_id int, Start_Date datetime, End_Date datetime, Upfront_Amt int, Monthly_Fee int);
    
insert into Lease
    (Lease_id, Apt_id, Resident_id, Start_Date, End_Date, Upfront_Amt, Monthly_Fee)
values
    (101, 110, 1001, '2015-02-01', '2015-07-31',  250,  500),
    (102, 111, 1002, '2015-03-01', '2016-02-29', 1000, 2000),
    (103, 112, 1003, '2015-04-01', '2016-03-31',  750, 1500);

declare @num_Months int = 16;
declare @start_Date date = '20150101';

with months as (
    select @start_Date as Lease_Month, 1 as Month_Num
    union all
    select dateadd(month, Month_Num, @start_Date), Month_Num + 1
    from months
    where Month_Num < @num_Months
)
select distinct
    m.Lease_Month,
    format(m.Lease_Month, 'MMM yyyy') as Lease_Month,
    sum(Monthly_Fee) over (
        partition by
            case
                when m.Lease_Month between Start_Date and End_Date
                then m.Lease_Month
            end
    ) as Projection
from months m left outer join Lease l
    on m.Lease_Month between l.Start_Date and l.End_Date
order by
    m.Lease_Month;

View schema
Execution time: 0,03 sec, rows selected: 18, rows affected: 3, absolute service time: 0,2 sec