tsql to split a time period in different months

here it is:

with t as (
select 1 id, getdate()-75 StarDate, getdate()-75+30 EndDate
union select 2, getdate()-100, getdate()-100+31
union select 3, getdate()-9, null
)
select id, StarDate, EndDate, datediff(dd, StarDate, isnull(EndDate, getdate())) Duration
from t

id	StarDate	EndDate	Duration
1	2014-10-04 12:06:20.867	2014-11-03 12:06:20.867	30
2	2014-09-09 12:06:20.867	2014-10-10 12:06:20.867	31
3	2014-12-09 12:06:20.867	NULL	9

Select allOpen in new window

oops, i guess you need more than that!

Here you go:

drop table #timeperiod
drop table #mydates
go

Create table #timeperiod
(ID int identity(1,1)not null,
StartDate date not null,
EndDate date null)

Insert into #timeperiod
(StartDate,EndDate)
Values ('9/1/2014','12/3/2014')

Insert into #timeperiod
(StartDate,EndDate)
Values ('11/9/2014','11/30/2014')

Insert into #timeperiod
(StartDate,EndDate)
Values ('12/9/2014',null)

create table #mydates
(StartDate date not null,
EndDate date not null)

insert into #mydates
select top(datediff(month,(select min(StartDate) from #timeperiod),SYSDATETIME())+1)
dateadd(month,-1+row_number() over(order by t1.id),'9/1/2014') as StartDate,
dateadd(month,row_number() over(order by t1.id),'9/1/2014') as FinishDate
from #timeperiod t1
cross join #timeperiod t2

Select * from #mydates;

with myTable as (select T.ID, D.StartDate as MonthStart, D.EndDate as MonthEnd, T.StartDate, T.EndDate
from #mydates D
cross join #timeperiod T
where D.EndDate >= T.StartDate
and (D.startDate <= T.EndDate or T.EndDate is null)),
myTable2 as (Select ID,
case when MonthStart < StartDate then
StartDate else MonthStart end as FirstDate,
case when EndDate is null then getdate()
when EndDate < MonthEnd then EndDate else MonthEnd end as SecondDate
from myTable)
select ID, FirstDate as StartDate, SecondDate as EndDate, Datediff(day,FirstDate,SecondDate) as Duration
from myTable2

Select allOpen in new window

Thank you ScottPletcher. The solution is working for me.
Can you please explain what is going on in the crossApply (portion of code copied below)

SELECT CASE WHEN t.tally = 0 THEN tp.StartDate 
         ELSE DATEADD(MONTH, DATEDIFF(MONTH, 0, tp.StartDate) + t.tally, 0) END AS StartDate,
    CASE WHEN t.tally = DATEDIFF(MONTH, tp.StartDate, ISNULL(tp.EndDate, GETDATE())) THEN ISNULL(tp.EndDate, GETDATE()) 
         ELSE DATEADD(DAY, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, tp.StartDate) + t.tally + 1, 0)) END AS EndDate

Select allOpen in new window

Sure.

First, as background, the CROSS APPLY as used above basically just assigns aliases to calculated values.  Otherwise, the entire expressions for StartDate and EndDate would have to be repeated when computing the Duration.  The CROSS APPLY in effect creates another column we can then use anywhere in our main query.  This example is somewhat more confusing than normal since the column names are exactly the same as the input column names, but it's actually a completely different value.

A stand-alone example may be clearer:
SELECT value, value1, value2
    --note that I can select all the different CROSS APPLY values
FROM ( SELECT 17 AS value ) AS test_data
CROSS APPLY (
    --multiply the original column value by 2 and give it a new alias name that can then be used anywhere in the query
    SELECT value * 2 AS value1
) AS cross_apply_1
CROSS APPLY (
    --note how we're using an earlier CROSS APPLY alias in this CA, which is an extremely useful capability!
    SELECT value1 * 2 AS value2
) AS cross_apply_2
ORDER BY value1 --note that I can ORDER BY any CA value(s), not just the last one(s)

Back to the original query.  The key ID is one like #1, which spans multiple months:
ID    StartDate          EndDate
 1       9/1/2014          12/3/2014
The output needs to be:
 ID     StartDate       EndDate       Duration
 1      9/1/2014         10/1/2014    30
 1      10/1/2014        11/1/2014   31
 1      11/1/2014        12/1/2014   30
 1      12/1/2014       12/3/2014    2

Here's how the query does this as it executes:
1) The INNER JOIN on tally will be for values 0, 1, 2 and 3, since the "DATEDIFF(MONTH" from StartDate to EndDate is 3 months.  Thus, a total of 4 rows will be output from the query, one for each month -- exactly what we want.
2) Since the output StartDate of the first row is the exact starting date, but for later months it is always the first of the month, when computing the new StartDate value in the CROSS APPLY we need to use "WHEN t.tally = 0" to test for the first row.
3) Similarly, since the output EndDate for the last row is the exact ending date, but for earlier months it is always the last day of the month, when computing the new EndDate value in the CROSS APPLY we need to use "WHEN t.tally = DATEDIFF(MONTH, tp.StartDate, ISNULL(tp.EndDate, GETDATE())) " to test for the last row.