DateDiff[
^] is what you're looking for.
To be able to calculate it for each year in date range, you have to use
CTE[
^]:
DECLARE @startdate DATE = '2007-10-01'
DECLARE @enddate DATE = '2012-08-24'
;WITH MIFY AS
(
SELECT @startdate AS StartDate, DATEFROMPARTS(YEAR(@startDate), 12,31) AS EndOfYear, DATEDIFF(MM, @startdate, DATEFROMPARTS(YEAR(@startDate), 12,31)) +1 AS Months
WHERE @enddate> DATEFROMPARTS(YEAR(@startdate), 12, 31)
UNION ALL
SELECT DATEADD(DD, 1,EndOfYear) AS StartDate, DATEFROMPARTS(YEAR(DATEADD(DD, 1,EndOfYear)), 12,31) AS EndOfYear, DATEDIFF(MM, DATEADD(DD, 1,EndOfYear), DATEFROMPARTS(YEAR(DATEADD(DD, 1,EndOfYear)), 12,31)) +1 AS Months
FROM MIFY
WHERE @enddate >DATEFROMPARTS(YEAR(DATEADD(DD, 1,EndOfYear)), 12,31)
UNION ALL
SELECT DATEADD(DD, 1,EndOfYear) AS StartDate, @enddate AS EndOfYear, DATEDIFF(MM, DATEADD(DD, 1,EndOfYear), @enddate) +1 AS Months
FROM MIFY
WHERE DATEADD(DD, 1,EndOfYear) = DATEFROMPARTS(YEAR(DATEADD(DD, 1,@enddate)), 1,1) AND
DATEFROMPARTS(YEAR(DATEADD(DD, 1,@enddate)), 12,31)=DATEFROMPARTS(YEAR(DATEADD(DD, 1,EndOfYear)), 12,31)
)
SELECT *
FROM MIFY
Result:
StartDate EndOfYear Months
2007-10-01 2007-12-31 3
2008-01-01 2008-12-31 12
2009-01-01 2009-12-31 12
2010-01-01 2010-12-31 12
2011-01-01 2011-12-31 12
2012-01-01 2012-08-24 8
For further information, please see:
Using Common Table Expressions[
^]
Recursive Queries Using Common Table Expressions[
^]