在sql server中每月将日期范围拆分为一行 [英] Split date range into one row per month in sql server
本文介绍了在sql server中每月将日期范围拆分为一行的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个包含两列的表格,名为from_date"和to_date"
I have a table with two column called "from_date" and "to_date"
the table look like:-
我想要这样的结果:-
from_date to_date
----------- ------------
2013-11-25 2013-11-30
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 ?
推荐答案
这是闰年安全的,可以处理其他答案目前没有的日期范围.
This is leap year safe and handles date ranges the other answers currently don't.
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;
结果:
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
这篇关于在sql server中每月将日期范围拆分为一行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文