您好,我对拆分/枢转日期有疑问
这是我的查询
select Name
, Start
, End
from Employees
where Start >= '1/27/2014'
and End <= '1/31/2014'
样本数据会是这样的
我想要做的是按日期范围拆分/旋转所有数据,如下所示
但我不知道我该如何做到这一点以及是否可能?
谢谢。
您需要生成所有日期。我在这里使用 cte 做到了这一点。然后我将这个日期范围与您的数据结合起来并收到所需的结果。
with DateRange AS
(
SELECT CAST('1/27/2014' as DATEtime) DateValue
UNION ALL
SELECT dateadd(dd,1,DateValue)
FROM DateRange
WHERE dateadd(dd,1,DateValue) <= CAST('3/31/2014' as datetime)
)
select name
, DateValue
from Employees
join DateRange
on start <= DateValue
and [end] >= datevalue
order by
name
, DateValue
outdated after your updated question
i would go about it with a simple union:
select Name
, Start
from Employees
where Start >= '1/27/2014'
and End <= '1/31/2014'
union all
select Name
, End
from Employees
where Start >= '1/27/2014'
and End <= '1/31/2014'
order by
Name
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)