两个日期之间的年和月的 T-SQL 列表

Question

我有一个表格，其中包含具有生效日期和终止日期的个人列表。 示例人员 1、20171201、20180601 对于每条记录，我需要输出它们在两个日期之间"活跃"的年份和月份的列表。 所以输出看起来像

数据输出

这是在SQL Server 2016中 任何帮助将不胜感激！

Answer 1

只是因为我没有看到它提供。 这是使用临时日历表的另一种方法

请注意 2000-01-01 和 10,000 天的基准日期 ...根据需要扩展或收缩

例

Declare @YourTable table (Person int,startdate date,  enddate date)
Insert Into @YourTable values 
(1,'20171201','20180601')
Select Distinct 
A.Person
,ActiveYear = year(D)
,ActiveMonth = month(D)
From  @YourTable A
Join  (
Select Top 10000 D=DateAdd(DAY,-1+Row_Number() Over (Order By (Select Null)),'2000-01-01') 
From  master..spt_values n1,master..spt_values n2
) B on D between startdate and enddate

返回

Person  ActiveYear  ActiveMonth
1       2017        12
1       2018        1
1       2018        2
1       2018        3
1       2018        4
1       2018        5
1       2018        6

Answer 2

根据您提供的信息，我做了一些猜测。也许递归 CTE 会有所帮助：

DECLARE @tab TABLE (Person INT, EffectiveDate DATE, TerminationDate DATE)
INSERT @tab VALUES
(1, '2017-12-01', '2018-05-31'),
(2, '2017-10-01', '2018-01-01'),
(3, '2018-02-01', '2018-12-01')
;WITH t AS (
SELECT Person, EffectiveDate AS Dt
FROM @tab
UNION ALL
SELECT Person, DATEADD(mm,1,Dt)
FROM t
WHERE t.Dt < (SELECT DATEADD(mm,-1,TerminationDate) FROM @tab tt WHERE tt.Person = t.Person)
)
SELECT *
FROM t
ORDER BY Dt

然后拿DATEPART()

SELECT t.Person
, t.Dt
, DATEPART(yyyy, t.Dt) ActiveYear
, DATEPART(mm, t.Dt) ActiveMonth
FROM t
ORDER BY Dt

Answer 3

欢迎来到堆栈溢出！为了获得更好的帮助，最好包含 DDL 易于使用的示例数据，我们可以使用这些数据快速重新创建您正在执行的操作并提供解决方案。请注意此示例数据：

DECLARE @yourtable TABLE(person VARCHAR(20), date1 DATE, date2 DATE)
INSERT @yourtable (person, date1, date2) 
VALUES ('Person1','20171201', '20180601'), ('Person2','20171001', '20180101'),
('Person3','20180101', '20180301');
SELECT t.* FROM @yourtable AS t;

返回：

person               date1      date2
-------------------- ---------- ----------
Person1              2017-12-01 2018-06-01
Person2              2017-10-01 2018-01-01
Person3              2018-01-01 2018-03-01

(我添加了几行(。这是我的解决方案：

DECLARE @yourtable TABLE(person VARCHAR(20), date1 DATE, date2 DATE)
INSERT @yourtable (person, date1, date2) 
VALUES ('Person1','20171201', '20180601'), ('Person2','20171001', '20180101'),
('Person3','20180101', '20180301');
SELECT 
Person      = t.person,
ActiveYear  = YEAR(st.DT),
ActiveMonth = MONTH(st.Dt)
FROM   @yourtable AS t
CROSS APPLY
(
SELECT TOP (DATEDIFF(MONTH,t.date1,t.date2)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM       (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS a(x)
CROSS JOIN (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS b(x)
) AS iTally(N)
CROSS APPLY (VALUES(DATEADD(MONTH, iTally.N-1, t.date1))) AS st(Dt);

其中返回：

Person               ActiveYear  ActiveMonth
-------------------- ----------- -----------
Person1              2017        12
Person1              2018        1
Person1              2018        2
Person1              2018        3
Person1              2018        4
Person1              2018        5
Person2              2017        10
Person2              2017        11
Person2              2017        12
Person3              2018        1
Person3              2018        2

如果您有任何疑问，请告诉我。