在SQL Server pt2中,将日期范围分成每月一行 [英] Split date range into one row per month in SQL Server pt2
问题描述
这个问题与此帖子有关:在SQL Server中将日期范围划分为每月一排
This question relates to this post: Split date range into one row per month in sql server
但是我对Stackoverflow并不陌生,并且不允许新用户对提供的解决方案发表评论。这就是为什么我现在将其添加为新问题。
But I'm new to Stackoverflow and new users are not allowed to place comments to provided solutions.That's why I am now adding this as a new question.
问题是这样的:
我有一个表,该表有两列,分别为 from_date和 to_date
I have a table with two column called "from_date" and "to_date"
该表看起来像这样:
我想要这样的结果:-
from_date || to_date
----------- ------------
from_date || to_date
----------- ------------
2013-11- 25 || 2013-11-30
2013-11-25 || 2013-11-30
2013-12-01 || 2013-12-05
2013-12-01 || 2013-12-05
该日期从2013-11-25到2013-11-30,另一个日期从2013-12-01到2013-12 -05 ...是否可以这样拆分?
That date is splits from 2013-11-25 to 2013-11-30 and another date split from 2013-12-01 to 2013-12-05... Is it possible to split like this ?
Aaron Bertrand的解决方案是:
Aaron Bertrand's solution is this:
DECLARE @d TABLE(from_date DATE, to_date DATE);
INSERT @d VALUES ('2013-11-25','2013-12-05');
;WITH n(n) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY [object_id])-1
FROM sys.all_columns
),
d(n,f,t,md,bp,ep) AS
(
SELECT n.n, d.from_date, d.to_date,
DATEDIFF(MONTH, d.from_date, d.to_date),
DATEADD(MONTH, n.n, DATEADD(DAY, 1-DAY(from_date), from_date)),
DATEADD(MONTH, n.n+1, DATEADD(DAY, 0-DAY(from_date), from_date))
FROM n
INNER JOIN @d AS d ON d.to_date >= DATEADD(MONTH, n.n-1, d.from_date)
)
SELECT original_from_date = f, original_to_date = t,
new_from_date = CASE n WHEN 0 THEN f ELSE bp END,
new_to_date = CASE n WHEN md THEN t ELSE ep END
FROM d
WHERE md >= n
ORDER BY original_from_date, new_from_date;
结果:
original_from_date original_to_date new_from_date new_to_date
------------------ ---------------- ------------- -----------
2013-11-25 2013-12-05 2013-11-25 2013-11-30
2013-11-25 2013-12-05 2013-12-01 2013-12-05
Aaron Bertrand's答案是一个超级强大的查询,但似乎只有在开始日期是31天的一个月之后的一个月内,它才能很好地工作。如果将开始日期更改为例如2013-5-25(从4月开始,只有30天),则new_to_date值不正确:所有整月突然最多不能超过30天,则:
Aaron Bertrand's answer is a super powerful query, but it seems it only works well if the startdate is in a month following a month with 31 days. If you change the startdate to, for example, 2013-5-25 (following April, with only 30 days), the new_to_date values are incorrect: all full months suddenly get a max of 30 days then:
original_from_date original_to_date new_from_date new_to_date
2013-05-25 2013-12-05 2013-05-25 2013-05-30
2013-05-25 2013-12-05 2013-06-01 2013-06-30
2013-05-25 2013-12-05 2013-07-01 2013-07-30
2013-05-25 2013-12-05 2013-08-01 2013-08-30
2013-05-25 2013-12-05 2013-09-01 2013-09-30
2013-05-25 2013-12-05 2013-10-01 2013-10-30
2013-05-25 2013-12-05 2013-11-01 2013-11-30
2013-05-25 2013-12-05 2013-12-01 2013-12-05
任何人都可以编辑查询以便结束日期是正确的(28、30、31天和2月29日为leap日),无论星期一开始日期在哪里?我不知道该怎么做。
Can anyone edit the query so that end dates are correct (28,30,31 days, and 29 feb leap) regardless of what month the start date is in? I can't figure out how to do it.
非常感谢Martijn Vermunt
Many thanks, Martijn Vermunt
推荐答案
已提供答案,查询已被编辑并变得完美。谢谢 @Aaron Bertrand
用于更新查询!
The answer has been provided, the query has been edited and made perfect. Thank you @Aaron Bertrand for updating the query!
DECLARE @d TABLE(from_date DATE, to_date DATE);
INSERT @d VALUES ('2013-11-25','2013-12-05');
;WITH n(n) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY [object_id])-1 FROM sys.all_columns
),
d(n,f,t,md,bp,ep) AS
(
SELECT n.n, d.from_date, d.to_date,
DATEDIFF(MONTH, d.from_date, d.to_date),
DATEADD(MONTH, n.n, DATEADD(DAY, 1-DAY(from_date), from_date)),
DATEADD(DAY, -1, DATEADD(MONTH, 1, DATEADD(MONTH, n.n,
DATEADD(DAY, 1-DAY(from_date), from_date))))
FROM n INNER JOIN @d AS d
ON d.to_date >= DATEADD(MONTH, n.n-1, d.from_date)
)
SELECT original_from_date = f, original_to_date = t,
new_from_date = CASE n WHEN 0 THEN f ELSE bp END,
new_to_date = CASE n WHEN md THEN t ELSE ep END
FROM d WHERE md >= n
ORDER BY original_from_date, new_from_date;
这篇关于在SQL Server pt2中,将日期范围分成每月一行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!