How do i calculate a projection sum using sql?

Question

I have the following Lease table

Lease_id Apt_id Resident_id Start_Date End_Date   Upfront_Amt Monthly_Fee
101      110    1001        02/01/2015 07/31/2015 250          500
102      111    1002        03/01/2015 02/29/2016 1000         2000
103      112    1003        04/01/2015 03/31/2016 750          1500

What I wanted to calculate was a projection of earnings from the Monthly fee. For example:

01/2015 0 (No lease active)
02/2015 500 (From Lease 101)
03/2015 500 + 2000 (From Lease 101 and 102)
04/2015 500 + 2000 + 1500 (From Lease 101, 102 and 103)
:
:
08/2015 2000 + 1500 (From lease 102 and 103)
etc..

Is there a way to do this efficiently with a single query?

Answer 1

select
    format(m.Lease_Month, 'MMM yyyy') as Lease_Month,
    sum(sum(Monthly_Fee)) over (partition by m.Lease_Month) as Projection
from
    <list of months> m left outer join
    Lease l
        on m.Lease_Month between l.Start_Date and l.End_Date
group by
    m.Lease_Month
order by
    m.Lease_Month;

There are lots of ways to generate a list of months. Here's one:

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
) ...

Put it all together and see it run here: http://rextester.com/YUAF69376