Number of Periods in a date range

Guys
Date brain killer - I have a startdate and enddate and need, for a given period length in months, to identify the number
of periods betwen the startdate and enddate
so for example

DECLARE @.Periods TABLE
(
StartDate datetime,
Enddate datetime,
NumberOfMonthsInPeriod INT ,
TotPeriods INT
)

-- Initial data
insert @.Periods
(
StartDate ,
Enddate ,
NumberOfMonthsInPeriod ,
TotPeriods -- for this illustration, initialised to 0 but need to be UPDATEd as per detail below
)

select
'30-Sep-2005',
'10-Apr-2009',
1 -- 1 month period
0
union all
select
'30-Sep-2005',
'10-Apr-2009',
3 -- 3 month period
0
union all
select
'30-Sep-2005',
'10-Apr-2009',
6 -- 6 month period
0

The following rules regarding periods apply
Each 1 (NumberOfMonthsInPeriod) month period is as would be expected ie
01-Jan - 31 Jan
01-Feb - 28-Feb (ie 1st March - 1 day which would deal with leap years - dateadd(...)
and so on to December

Each 3 (NumberOfMonthsInPeriod) month period is one of the following 'bands' per year
01-Jan - 31-Mar
01-Apr - 30-Jun
01-Jul - 30-Sep
01-Oct - 31-Dec

Each 6 (NumberOfMonthsInPeriod) month period is one of the following
01-Jan - 30-Jun
01-Jul - 31-Dec

I need to derive TotPeriods as follows:

For the row where NumberOfMonthsInPeriod = 1, the first period ie the one the start date falls within is 01-Sep-2005 to 30-Sep-2005, second is 01-Oct-2005 - 31-Oct-2005 and so on until
last period ie the one the end datye falls within is 01-Apr-2009 - 30-Apr-2009, a TotPeriods value of 44

For the row where NumberOfMonthsInPeriod = 3, first period is 01-Jul-2005 to 30-Sep-2005, second is 01-Oct-2005 - 31-Dec-2005 and so on until
last period is 01-Apr-2009 - 30-Jun-2009, a TotPeriods value of 16

For the row where NumberOfMonthsInPeriod = 6, first period is 01-Jul-2005 to 31-Dec-2005, second is 01-Jan-2006 - 30-Jun-2006 and so on until
last period is 01-Jan-2009 - 30-Jun-2009, a TotPeriods value of 8

Hope this is clear and thanks in advanceYou started off good, but I still don't get it. What' sthe expect results, and what defines a period again?|||Brett
Expected results is the TotPeriods value - I've come up with a solution (below) but wonder if there isn't a more elegant or less verbose way of doing it

DECLARE @.Periods TABLE
(
StartDate datetime,
Enddate datetime,
NumberOfMonthsInPeriod INT ,
TotPeriods INT,
periodstartdate datetime ,
periodenddate datetime
)

-- Initial data
insert @.Periods
(
StartDate ,
Enddate ,
NumberOfMonthsInPeriod ,
TotPeriods
)

select
'02-Jul-2004',
'10-Dec-2005',
1 ,-- 1 month period
0
union all
select
'02-Jul-2004',
'10-Dec-2005',
3 ,-- 3 month period
0
union all
select
'02-Jul-2004',
'10-Dec-2005',
6 ,-- 6 month period
0

update @.Periods
set periodstartdate =
case when NumberOfMonthsInPeriod = 1 then cast('01' + '-' +
cast (datepart(mm,startdate) as varchar) + '-' +
cast (datepart(yyyy,startdate) as varchar)
as datetime)

when NumberOfMonthsInPeriod = 3 then cast(
'01' + '-' +
case when datepart(mm,startdate) in (1,2,3) then 'Jan'
when datepart(mm,startdate) in (4,5,6) then 'Apr'
when datepart(mm,startdate) in (7,8,9) then 'Jul'
when datepart(mm,startdate) in (10,11,12) then 'Oct'
end
+ '-' +
cast (datepart(yyyy,startdate) as varchar)
as datetime)

when NumberOfMonthsInPeriod = 6 then cast(
'01' + '-' +
case when datepart(mm,startdate) in (1,2,3,4,5,6) then 'Jan'
when datepart(mm,startdate) in (7,8,9,10,11,12) then 'Jul'
end
+ '-' +
cast (datepart(yyyy,startdate) as varchar)
as datetime)
end ,
--convert enddate to first of whatever instead of 16th, add a month and knock off a day
periodenddate =
case when NumberOfMonthsInPeriod = 1 then
dateadd(dd , -1 ,
dateadd (mm , 1 ,
cast(
'01' + '-' + cast(datepart(mm,enddate) as varchar) + '-' +
cast(datepart (yyyy, enddate) as varchar)
as varchar)
)
)

when NumberOfMonthsInPeriod = 3 then
dateadd(dd , -1 ,
dateadd (mm , 1 ,
cast(

'01' + '-' +
case when datepart(mm,enddate) in (1,2,3) then 'Mar'
when datepart(mm,enddate) in (4,5,6) then 'Jun'
when datepart(mm,enddate) in (7,8,9) then 'Sep'
when datepart(mm,enddate) in (10,11,12) then 'Dec'
end
+ '-' +

cast(datepart (yyyy, enddate) as varchar)
as varchar)
)
)

when NumberOfMonthsInPeriod = 6 then
dateadd(dd , -1 ,
dateadd (mm , 1 ,
cast(

'01' + '-' +
case when datepart(mm,enddate) in (1,2,3,4,5,6) then 'Jun'
when datepart(mm,enddate) in (7,8,9,10,11,12) then 'Dec'
end
+ '-' +
cast(datepart (yyyy, enddate) as varchar)
as varchar)
)
)

end

update @.Periods
set TotPeriods =
datediff(mm , periodstartdate , dateadd(dd,1,periodenddate) ) / NumberOfMonthsInPeriod

select * from @.Periods|||PS Brett - sorry to answer your question, a period is the length of time based on the NumberOfMonthsInPeriod value so if the value is 1 it's a 1 month period, if 3 it's a 3 month period etc

Thx
SWsql