将日期范围细分为每个日历年的月数

Question

我有大量的日期范围，如下所示，示例如下。我需要计算每个实际日历年有多少个月。因此，这将分解为：

合同：123

开始日期：2016年11月1日

结束日期：2018年6月1日

• 2016年11月1日

合同：456

开始日期：2017年5月31日

结束日期：2019年6月1日

• 2017年5月31日

有人知道解决这个问题的方法吗？每个合同都有一行，都在同一个表中，开始和结束日期列在同一行中。

我原本打算沿着CTE路线走，但这让我大吃一惊。

预期结果：

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

或者类似的，我非常乐意修改我最初的查询，以纳入实现这一目标的最佳结果/方法。

表定义：

>

contract_id：int

start_date： datetime

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

您可以使用具有type='P'的master...spt_values来获取从0到2047的数字。过滤此数字以使其介于开始日期的年份和结束日期的年份之间，您可以将两个日期之间的年份作为行获取。EndOfYork和Start inOfYork分别返回今年的第一个日期和最后一个日期。月份返回第一个日期和最后一个日期之间的月份。

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)

Answer 2

有一种可能性：

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

如果没有数字表，请将其放在查询之前：

;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
)

不过我还是会选肖恩·兰格的测试版

Answer 3

我会用一个计数来计算这个。我在我的系统上保留了一个视图，它的速度非常快。这是视图。

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

然后我们需要一些样本数据。像这样的。

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

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

现在，我们可以查询样本并利用tally表进行简短的工作。

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))