有谁知道解决这个简单的方法吗？
我有它由开始时间的事件以及相关的持续时间的表。 我需要能够对事件的持续时间划分为三十分钟一班。 因此，例如，如果事件开始于10点45分00秒和持续时间是零时17分00秒，然后返回set应该分配15分钟，10点30分零零秒间隔和00:02:00分钟至11:00 ：00间隔。
我敢肯定，我可以找出一个笨拙的方法，但是想简单的东西一点点。 这必须拿出相当经常我想像但谷歌今天是无益的。
谢谢，
史蒂夫
Answer 1:
你可以创建只是时间（超过24小时）的查找表，并加入到该表。 您将需要重订的日期，在查询中使用。 然后对上下间隔DATEDIFF解决他们的持续时间。 每个中间间隔将是30分钟。
create table #interval_lookup (
from_date datetime,
to_date datetime
)
declare @time datetime
set @time = '00:00:00'
while @time < '2 Jan 1900'
begin
insert into #interval_lookup values (@time, dateadd(minute, 30, @time))
set @time = dateadd(minute, 30, @time)
end
declare @search_from datetime
declare @search_to datetime
set @search_from = '10:45:00'
set @search_to = dateadd(minute, 17, @search_from)
select
from_date as interval,
case
when from_date <= @search_from and
@search_from < to_date and
from_date <= @search_to and
@search_to < to_date
then datediff(minute, @search_from, @search_to)
when from_date <= @search_from and
@search_from < to_date
then datediff(minute, @search_from, to_date)
when from_date <= @search_to and
@search_to < to_date then
datediff(minute, from_date, @search_to)
else 30
end as duration
from
#interval_lookup
where
to_date > @search_from
and from_date <= @search_to
Answer 2:
创建TVF，其分离单个事件：
ALTER FUNCTION dbo.TVF_TimeRange_Split_To_Grid
(
@eventStartTime datetime
, @eventDurationMins float
, @intervalMins int
)
RETURNS @retTable table
(
intervalStartTime datetime
,intervalEndTime datetime
,eventDurationInIntervalMins float
)
AS
BEGIN
declare @eventMinuteOfDay int
set @eventMinuteOfDay = datepart(hour,@eventStartTime)*60+datepart(minute,@eventStartTime)
declare @intervalStartMinute int
set @intervalStartMinute = @eventMinuteOfDay - @eventMinuteOfDay % @intervalMins
declare @intervalStartTime datetime
set @intervalStartTime = dateadd(minute,@intervalStartMinute,cast(floor(cast(@eventStartTime as float)) as datetime))
declare @intervalEndTime datetime
set @intervalEndTime = dateadd(minute,@intervalMins,@intervalStartTime)
declare @eventDurationInIntervalMins float
while (@eventDurationMins>0)
begin
set @eventDurationInIntervalMins = cast(@intervalEndTime-@eventStartTime as float)*24*60
if @eventDurationMins<@eventDurationInIntervalMins
set @eventDurationInIntervalMins = @eventDurationMins
insert into @retTable
select @intervalStartTime,@intervalEndTime,@eventDurationInIntervalMins
set @eventDurationMins = @eventDurationMins - @eventDurationInIntervalMins
set @eventStartTime = @intervalEndTime
set @intervalStartTime = @intervalEndTime
set @intervalEndTime = dateadd(minute,@intervalMins,@intervalEndTime)
end
RETURN
END
GO
测试：
select getdate()
select * from dbo.TVF_TimeRange_Split_To_Grid(getdate(),23,30)
测试结果：
2008-10-31 11:28:12.377
intervalStartTime intervalEndTime eventDurationInIntervalMins
----------------------- ----------------------- ---------------------------
2008-10-31 11:00:00.000 2008-10-31 11:30:00.000 1,79372222222222
2008-10-31 11:30:00.000 2008-10-31 12:00:00.000 21,2062777777778
用法示例：
select input.eventName, result.* from
(
select
'first' as eventName
,cast('2008-10-03 10:45' as datetime) as startTime
,17 as durationMins
union all
select
'second' as eventName
,cast('2008-10-05 11:00' as datetime) as startTime
,17 as durationMins
union all
select
'third' as eventName
,cast('2008-10-05 12:00' as datetime) as startTime
,100 as durationMins
) input
cross apply dbo.TVF_TimeRange_Split_To_Grid(input.startTime,input.durationMins,30) result
样品使用效果：
eventName intervalStartTime intervalEndTime eventDurationInIntervalMins
--------- ----------------------- ----------------------- ---------------------------
first 2008-10-03 10:30:00.000 2008-10-03 11:00:00.000 15
first 2008-10-03 11:00:00.000 2008-10-03 11:30:00.000 2
second 2008-10-05 11:00:00.000 2008-10-05 11:30:00.000 17
third 2008-10-05 12:00:00.000 2008-10-05 12:30:00.000 30
third 2008-10-05 12:30:00.000 2008-10-05 13:00:00.000 30
third 2008-10-05 13:00:00.000 2008-10-05 13:30:00.000 30
third 2008-10-05 13:30:00.000 2008-10-05 14:00:00.000 10
(7 row(s) affected)
文章来源: Splitting time + duration into intervals in t-sql
