Date range calculation for a project revenue in SQL?

Question

I have a table ClientProfile that contains a column called StartDate, the datatype of this column is date, the second column is called MonthlyRevenue which is the amount column in numeric(18,2) and the third column is called ContractMonths datatype int which specifies the number of months the project will be active for. A user is required to select a date range and the query should be able to fetch the full date range (month wise) specifying the amount for each month.

For example:
Project A is going to start from 2020-03-01 (1st March) and the contract will run for 6 months so when a user selects dates 02-2020 to 12-2020.

I should be able to get a result like this:

Month     Revenue
-----------------   
02-2020   0
03-2020   100
04-2020   100
05-2020   100
06-2020   100
07-2020   100
08-2020   100
09-2020   0
10-2020   0
11-2020   0
12-2020   0

I would really appreciate any help because I am stuck at this point and not able to figure this out.

Answer 1

One method is a recursive CTE to generate the months:

with months as (
      select @startmonth as mon
      union all
      select dateadd(month, 1, mon)
      from months
      where mon < @endmonth
     )
select months.mon, coalesce(cp.monthlyrevenue, 0) as revenue
from months left join
     clientprofile cp
     on cp.project = @project and
        cp.startdate <= months.mon and
        dateadd(month, cp.contractmonths, cp.startdate) >= months.mon;

If the period can exceed 100 months, you need to add option (maxrecursion 0).

Alternatively, you can build a monthly calendar table into your application and do pretty much he same thing using that table directly.