Breaking down a date range in to number of months in each calendar year

Question

I have a large number of date ranges as below, example below. I need to calculate how many months are in each actual calendar year. So this would break down as:

---

Contract: 123

Start date: 01/11/2016

End date: 01/06/2018

• 01/11/2016 > 31/12/2016 - 2 months in year 2016
• 01/01/2017 > 31/12/2017 - 12 months in year 2017
• 01/01/2018 > 01/06/2018 - 6 months in year 2018

---

Contract: 456

Start date: 31/05/2017

End date: 01/06/2019

• 31/05/2017 > 31/12/2017 - 6 months in year 2017
• 01/01/2018 > 31/12/2018 - 12 months in year 2018
• 01/01/2019 > 01/06/2019 - 6 months in year 2019

---

Does anyone know of a solution to handle this? Each contract has a row, all in the same table and the start and end date listed in the same row.

I was originally going down the CTE route but this blew my mind.

Expected outcome:

contract_id    year    number of months
123            2016    2
123            2017    12
123            2018    6
456            2017    6
456            2018    12
456            2019    6

Or similar, I am more than happy to amend my original query to incorporate what the best outcome/method to achieve this is.

---

Table definition:

• contract_id: int
• start_date: datetime

• end_date: datetime

  contract_id start_date end_date 123 2016-01-11 00:00:00.000 2018-06-01 00:00:00.000 456 2017-05-31 00:00:00.000 2019-06-01 00:00:00.000

Answer 1

I would use a tally for this. I keep one on my system as a view which is lightning fast. Here is the view.

create View [dbo].[cteTally] as

WITH
    E1(N) AS (select 1 from (values (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))dt(n)),
    E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
    E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
    cteTally(N) AS 
    (
        SELECT  ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
    )
select N from cteTally
GO

Then we need some sample data. Something like this.

declare @Something table
(
    Contract char(1)
    , StartDate date
    , EndDate date
)

insert @Something values
('A', '20161101', '20180601')
, ('B', '20170531', '20190601')

Now we can query against the sample and utilize the tally table to make short work of this.

select s.Contract
    , ContractYear = datepart(year, DATEADD(month, t.N - 1, s.StartDate))
    , NumMonths = count(*)
from @Something s
join cteTally t on t.N <= datediff(month, s.StartDate, s.EndDate) + 1
group by s.Contract
    , datepart(year, DATEADD(month, t.N - 1, s.StartDate))
order by s.Contract
    , datepart(year, DATEADD(month, t.N - 1, s.StartDate))

Answer 2

Here's a possibility:

select t.contract_id,n.id as year,q2.[#months]
from yourtable t
cross apply
(
    select year([Start Date]) as first_year,
    select year([End Date]) as last_year
)q
inner join numbers_table n on n.id between q.first_year and q.last_year
cross apply
(
    select case
        when n.id=first_year then 12-month([Start Date])
        when n.id=last_year then month([End Date])
        else 12
        end as [#months]
)q2

If you don't have a numbers table, put this before the query:

;WITH numbers_table(id) AS
(
  SELECT ROW_NUMBER() OVER (ORDER BY s1.[object_id]) - 1
  FROM sys.all_columns AS s1
  CROSS JOIN sys.all_columns AS s2
)

I would go for the tested variation of Sean Lange, though

Answer 3

You can use master..spt_values with type = 'P' to get numbers from 0 to 2047. Filtering this number so it is between year of start date and year of end date and you get the years between the two dates as rows. The EndOfYear and BeginOfYear returns the first date, respectively last date, of each of this years. Months returns the months between the first and last date.

DECLARE @Table TABLE
(
    Contract VARCHAR(5),
    StartDate DATETIME,
    EndDate DATETIME
)

INSERT INTO @Table(Contract, StartDate, EndDate) 
SELECT 'A', '20161101', '20180601' UNION ALL
SELECT 'B', '20170531', '20190601'

SELECT Contract,
    Year = spt_values.number,
    Months = Months.Value
FROM @Table CROSS JOIN
    master..spt_values CROSS APPLY
    (
        SELECT CAST(CONCAT(spt_values.number, '1231') AS DATETIME) AS Value
    ) AS EndOfYear CROSS APPLY
    (
        SELECT DATEADD(YEAR, -1, EndOfYear.Value) + 1 AS Value
    ) AS BeginOfYear CROSS APPLY
    (
        SELECT DATEDIFF(MONTH, IIF(BeginOfYear.Value < StartDate, StartDate, BeginOfYear.Value), IIF(EndOfYear.Value > EndDate, EndDate, EndOfYear.Value)) + 1 AS Value
    ) Months
WHERE type = 'P' AND
    spt_values.number <= YEAR(EndDate) AND
    spt_values.number >= YEAR(StartDate)