Select Multiple Rows from Timespan

Question

Problem

In my sql-server-2014 I store projects in a table with the columns:

Startdate .. | Enddate ....| Projectname .................| Volume
2017-02-13 | 2017-04-12 | GenerateRevenue .........| 20.02
2017-04-02 | 2018-01-01 | BuildRevenueGenerator | 300.044
2017-05-23 | 2018-03-19 | HarvestRevenue ............| 434.009

I need a SELECT to give me one row per month of the project for each project. the days of the month don't have to be considered.

Date .......... | Projectname..................| Volume
2017-02-01 | GenerateRevenue .........| 20.02
2017-03-01 | GenerateRevenue .........| 20.02
2017-04-01 | GenerateRevenue .........| 20.02
2017-04-01 | BuildRevenueGenerator | 300.044
2017-05-01 | BuildRevenueGenerator | 300.044
2017-06-01 | BuildRevenueGenerator | 300.044
...

Extra

Ideally the logic of the SELECT allows me both to calculate the monthly volume and also the difference between each month and the previous.

Date .......... | Projectname..................| VolumeMonthly
2017-02-01 | GenerateRevenue .........| 6.6733
2017-03-01 | GenerateRevenue .........| 6.6733
2017-04-01 | GenerateRevenue .........| 6.6733
2017-04-01 | BuildRevenueGenerator | 30.0044
2017-05-01 | BuildRevenueGenerator | 30.0044
2017-06-01 | BuildRevenueGenerator | 30.0044
...

Also...

I know I can map it on a temporary calendar table, but that tends to get bloated and complex very fast. Im really looking for a better way to solve this problem.

Solution

Gordons solution worked very nicely and it doesn't require a second table or mapping on a calendar of some sort. Although I had to change a few things, like making sure both sides of the union have the same SELECT.

Here my adapted version:

with cte as (
  select startdate as mondate, enddate, projectName, volume 
  from projects
  union all
  select dateadd(month, 1, mondate), enddate, projectName, volume
  from cte
  where eomonth(dateadd(month, 1, mondate)) <= eomonth(enddate)
)
select * from cte;

Volume monthly can be achieved by replacing volume with:

CAST(Cast(volume AS DECIMAL) / Cast(Datediff(month, 
startdate,enddate)+ 1 AS DECIMAL) AS DECIMAL(15, 2)) 
END AS [volumeMonthly]

Answer 1

You can use a recursive subquery to expand the rows for each project, based on the table:

with cte as (
      select stardate as mondate, p.*
      from projects
      union all
      select dateadd(month, 1, mondate), . . .  -- whatever columns you want here
      from cte
      where eomonth(dateadd(month, 1, mondate)) <= eomonth(enddate)
    )
select *
from cte;

I'm not sure if this actually answers your question. When I first read the question, I figured the table had one row per project.